Bitmasks vs. separate columns

Question

I have a database of users and their permissions. For example, a row looks like this:

    Name    | sendMessages | receiveMessages | post | readPosts
------------+--------------+-----------------+------+----------
Jeff Atwood |      1       |        1        |  0   |     1

What's better for this situation, separate columns (as in the example) or a single column, containing a bitmask (in this case, 1101 translates to 0xD)?

Answer 1

tinyint(1) as boolean is usally the best way to go.

Doing queries with bitmask is not efficient as it cannot use index if it has to calculate it or can get very nasty if you try to make use of index

Lets look at simple query

select * from tbl where sendMessages = 1 and readPosts = 1

With single column that would be:

select * from tbl where val&9 = 9

This is not really efficient as it has to do full table scan and calculation.

Lets try to rewrite the query so that it can make use of indexes. This can be done by listing all possible values with IN:

select * from tbl where val in (9, 11, 13, 15)

Now imagine how would this query look if you want to do simple where readPosts = 1

However, if you list too much values mysql optimiser will still do full table scan

Answer 2

What about not using columns for permissions, but create a permissions table and a user-permissions link table?

Answer 3

It is best to store permissions as separate columns with the BIT data type. Most modern database engines optimize bit column storage:

• for up to 8 bit columns in a table, the columns are stored in 1 single byte.
• for 9 and up to 16 bit columns, the columns are stored in 2 bytes,
• and so on

So it makes the most of the two options you list. Less storage required through bitmasking, while keeping the clarity with multiple columns. The engine takes care of it for you.