CODEWITHSUNDEEP
mysqldatabasedatabase-design
Freddie Fabregas
Freddie Fabregas

Reputation: 1203

Table having multiple flags

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?

Upvotes: 0

Views: 605

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52117

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

Dependstm ;)

Do you always have exactly 7 flags?

  1. If yes, storing them in the same row is appropriate.
  2. 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)"

Upvotes: 1

heiko
heiko

Reputation: 444

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

Upvotes: 1

Related Questions