Table having multiple flags

Question

I have a sample table below:

+-------------------------------------------------------------------+
| id | name | flag1 | flag2 | flag3 | flag4 | flag5 | flag6 | flag7 |
+-------------------------------------------------------------------+
| 1  | fred | 1     | 0     | 1     | 1     | 1     | 1     | 1     |
+-------------------------------------------------------------------+

What I would like to know, is this the most efficient and easiest way to retrieve these data?

I'm currently working on Enum which has some Flag Attribute Logic.

In that case, if:

flag1 = 1
flag2 = 2
flag3 = 4
and so on..

I'll just create a table with this design, then add another table for look up of those values:

+------------------+
| id | name | flag |
+------------------+
| 1  | fred | 125  |
+------------------+

Any suggestions?

score 1 · Answer 1 · answered Feb 22 '13 at 09:37

You can store all your flags in one Integer-field by using binary functions to get/set the different bits. See: MySql Bit Functions

There are a few comments on that page about this use-case. The latest is a good example showing a typical SQL to read the flags:

SELECT 
SUM(IF(rubrik & 1, 1, 0)) actus,
SUM(IF(rubrik & 2, 1, 0)) shopping,
SUM(IF(rubrik & 4, 1, 0)) utils,
SUM(IF(rubrik & 8, 1, 0)) communication,
SUM(IF(rubrik & 16, 1, 0)) services,
COUNT(user_id) AS total,
FROM preferences p

score 1 · Accepted Answer · answered Feb 22 '13 at 14:08

What I would like to know, is this the most efficient and easiest way to retrieve these data?

Depends^tm ;)

Do you always have exactly 7 flags?

If yes, storing them in the same row is appropriate.
If no, move flags to a separate table that is in N:1 relationship to the "main" table. This will enable you to easily vary the number of flags without adding more columns to the table.

For the (1) above, do you need to find individual flags?

If yes, keep them in separate fields, so you can index them separately. But beware that indexing is useful only if the index is selective enough.
If no, you could try to save some storage space (and more importantly, increase the cache effectiveness) by encoding the flags as bits within a single integer field. Unless you are on a very old version of MySQL, consider using BIT(M). A quote from documentation:

"This data type was added in MySQL 5.0.3 for MyISAM, and extended in 5.0.5 to MEMORY, InnoDB, BDB, and NDBCLUSTER. Before 5.0.3, BIT is a synonym for TINYINT(1)"

Table having multiple flags

2 Answers2