11

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)?

Giulio Muscarello
  • 1,312
  • 2
  • 12
  • 33
  • 5
    For querying purposes, definitely separate columns. Unless storage space is an actual real-world issue, I'd go with that – Pekka May 03 '13 at 16:43
  • Things could get really ugly if you think you're going to have more privileges than will fit in your mask column. It's likely also to be easier to read if you have one column per privilege. I'd definitely go with seperate columns – nurdglaw May 03 '13 at 16:44

4 Answers4

13

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

Imre L
  • 6,159
  • 24
  • 32
  • 4
    The exception to this rule is if you have hundreds or thousands of bits that aren't queried directly. – tadman May 03 '13 at 16:46
  • 1
    Thank you for your comment. There is always exceptions. How many columns would you need for n = hundreds or thousands? n/64? – Imre L May 03 '13 at 17:12
  • 1
    If it's one column per "bit", then presumably as many columns as you have bits. At some point your schema ceases to make any sense. – tadman May 03 '13 at 17:24
  • 1
    That would make sense if a very low percentage of these bit fields are likely to be engaged, if their usage is sparse. – tadman May 03 '13 at 18:43
8

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

CodeCaster
  • 147,647
  • 23
  • 218
  • 272
  • I don't see the advantage. – Giulio Muscarello May 03 '13 at 16:56
  • 3
    @Giulio it allows for adding, modifying and removing permissions without changing your data model. It enables easyer querying, where you don't have to change column names but can change the parameters passed to the WHERE clause. And tons of other benefits of normalization. – CodeCaster May 03 '13 at 17:00
  • 1
    @GiulioMuscarello See: http://stackoverflow.com/questions/9774715/mysql-multiple-tables-or-one-table-with-many-columns There are pro's and cons to each method. With permissions/roles it is usually safe to apply normalization practices. When you begin to exceed 40,000 normalized records, during join operations you will generally experience an increase in query times. – Will B. Jan 30 '15 at 17:53
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.

ttdijkstra
  • 618
  • 4
  • 14
0

Using a bit mask you can not use the first or last bit 0 and (31 in 32 bet / 63 in 64 bit???) but easily searchable with pow(2, bit) & field = pow(2, bit)

If you need more than 1 field can cope with then you have to start using multiple fields and then you get into the pain of working out which field your bit is set in.

This can easily be overcome by a simple routine to return true or false given the bit you are looking for and the fields or row in question.

But for permissions it would be best as @CodeCaster said using a permissions table and a linking table.

Chris
  • 435
  • 4
  • 11