78

I’m working on an application where users have different permissions to use different features (e.g. Read, Create, Download, Print, Approve, etc.). The list of permissions isn’t expected to change often. I have a couple of options of how to store these permissions in the database.

In what cases would Option 2 be better?

Option 1

Use an associative table.

User
----
UserId (PK)
Name
Department

Permission
----
PermissionId (PK)
Name
User_Permission
----
UserId (FK)
PermissionId (FK)

Option 2

Store a bitmask for each user.

User
----
UserId (PK)
Name
Department
Permissions
[Flags]
enum Permissions {
    Read = 1,
    Create = 2,
    Download = 4,
    Print = 8,
    Approve = 16
}
Ryan Kohn
  • 13,079
  • 14
  • 56
  • 81

9 Answers9

66

Splendid question!

Firstly, let's make some assumptions about "better".

I'm assuming you don't much care about disk space - a bitmask is efficient from a space point of view, but I'm not sure that matters much if you're using SQL server.

I'm assuming you do care about speed. A bitmask can be very fast when using calculations - but you won't be able to use an index when querying the bitmask. This shouldn't matter all that much, but if you want to know which users have create access, your query would be something like

select * from user where permsission & CREATE = TRUE

(haven't got access to SQL Server today, on the road). That query would not be able to use an index because of the mathematical operation - so if you have a huge number of users, this would be quite painful.

I'm assuming you care about maintainability. From a maintainability point of view, the bitmask is not as expressive as the underlying problem domain as storing explicit permissions. You'd almost certainly have to synchronize the value of the bitmask flags across multiple components - including the database. Not impossible, but pain in the backside.

So, unless there's another way of assessing "better", I'd say the bitmask route is not as good as storing the permissions in a normalized database structure. I don't agree that it would be "slower because you have to do a join" - unless you have a totally dysfunctional database, you won't be able to measure this (whereas querying without the benefit of an active index can become noticably slower with even a few thousand records).

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • 5
    As the [cardinality](http://en.wikipedia.org/wiki/Cardinality_%28SQL_statements%29) of a boolean (or bit in SQL Server case) column is extremely low, an index on those columns are totally useless. So the normalized solution also wouldn't have that optimization available. – Clodoaldo Neto Jan 31 '13 at 16:33
  • Doesn't SQL Server pack adjacent bit fields into bytes, basically storing it as a bitmask. – crush Oct 25 '15 at 15:42
12

Personally, I would use an associative table.

A bitmask field is very difficult to query and join on.

You can always map this to your C# flags enum and if performance becomes and issue refactor the database.

Readability over premature optimization ;)

Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • 6
    Management and maintenance. How much harder will it be to maintain and manage the data stored in the database when critical information is obfuscated in a bitmask column? And any performance gain is almost certainly not going to be great enough to make a real difference. – Philip Kelley Apr 18 '11 at 20:13
6

There is no definitive answer, so do what works for you. But here is my catch:

Use option 1 if

  • You expect permissions to grow to many
  • If you might need to do a permission check in the database stored procedures itself
  • You do not expect millions of users so that records in the table do not grow massively

Use option 2 if

  • Permissions are going to be limited to handful
  • You expect millions of users
Aliostad
  • 80,612
  • 21
  • 160
  • 208
  • Millions of rows is a trivial number in modern (and even decent legacy) RDBMS's – Adam Robinson Apr 18 '11 at 20:09
  • Yes but considering indexes you might need and possibility of index bookmarking during search which will slow down the process, I prefer second option. – Aliostad Apr 18 '11 at 20:11
5

Store the permissions normalized (i.e. not in a bitmask). While it's obviously not a requirement for your scenario (especially if the permissions won't often change), it will make querying much easier and more obvious.

Adam Robinson
  • 182,639
  • 35
  • 285
  • 343
2

I advice against using a bitmask for the following reasons:

  • Index cannot be used efficiently
  • Querying is harder
  • Readability / Maintenance is severely impacted
  • The average developer out there doesn't know what a bitmask is
  • Flexibility is reduced (upper limit to nr of bits in a number)

Depending on your query patterns, planned feature set and data distribution I would go with your option 1, or even something simple as:

user_permissions(
   user_id
  ,read     
  ,create   
  ,download 
  ,print    
  ,approve  
  ,primary key(user_id)
);

Adding a column is a schema modification, but my guess is that adding a privilege "Purge", will require some code to go along with it, so the privileges may not have to be as dynamic as you think.

If you have some sick distribution of data, such as 90% of the user base doesn't have a single permission, the following model also works fine (but falls apart when doing larger scans (one 5-way join vs a single full table scan).

user_permission_read(
   user_id
  ,primary key(user_id)
  ,foreign key(user_id) references user(user_id)
)

user_permission_write(
   user_id
  ,primary key(user_id)
  ,foreign key(user_id) references user(user_id)
)

user_permission_etcetera(
   user_id
  ,primary key(user_id)
  ,foreign key(user_id) references user(user_id)
)
Ronnis
  • 12,593
  • 2
  • 32
  • 52
1

The only time I can think of when I would use a bitmask field to store permissions, is when you are really really constrained in how much physical memory you have....like maybe on an old mobile device. In truth, the amount of memory you save isn't worth it. Even at millions of users hard drive space is cheap, and you can expand permissions etc. a lot easier by using the non-bitmask approach (this about reporting off of who has what permissions etc.)

One of this biggest headaches I've run into with it is assigning users permissions directly in the database. I know you should try and use the application to administer itself and not-much with application data in general, but sometimes, it's just necessary. Unless the bitmask is actually a character field, and you can easily see what permissions someone has instead of an integer, try explaining to an analyst etc. how to give write access etc. to someone by updating the field.....and pray your arithmetic is correct.

kemiller2002
  • 113,795
  • 27
  • 197
  • 251
1

It'll be useful when they won't change in their structure and will always be used together. That way, you have little round trips to the server. They are also good performance-wise because you can affect all the rights in a single assignation of a variable.

I personally don't like them... In some performance intense application, they're still used. I remember implementing a chess-AI using these because you could evaluate a board in a single comparison.. It's a pain to work with.

Simon Dufour
  • 184
  • 7
1

I would always store it normalized unless the database is merely holding the record for you, and you will never do anything with this besides retrieving and saving. A scenario for this is if upon login, your user's permission string is fetched, and in server code it is processed and cached. In that case it really doesn't matter too much that it's denormalized.

If you're storing it in a string and trying to do work on it at the DB level, you'd have to do some gymnastics to get the permissions for page X out, which can be painful.

Mike M.
  • 12,343
  • 1
  • 24
  • 28
-2

Your queries will run faster using a flags enumeration (bitmask), because you won't need to include a join to the associated table in order to make sense of the value.

smartcaveman
  • 41,281
  • 29
  • 127
  • 212
  • 4
    -1 This incorrectly implies that it will *not* run fast using a join. You also don't account for what the query *is*. If it's checking for the presence of a particular permission, a join on a properly indexed column will blow the doors off of a bitmask field, whose bitwise operations would require a table scan. – Adam Robinson Apr 18 '11 at 20:07
  • @Adam Robinson, (1) No, it really doesn't imply that at all. It implies the query will run **faster**, which is correct. (2) You are comparing the most highly optimized query on an associative table to the most poorly optimized query on an integer field. That really isn't very practical. – smartcaveman Apr 18 '11 at 20:12
  • 1
    While it's certainly possible that the code you write to interpret the bitmask would be more efficient than a join to the `USER_PERMISSION` table, it seems unlikely that the performance difference would be meaningful-- this is unlikely to be the bottleneck operation-- and there is a substantial loss of clarity in the code. – Justin Cave Apr 18 '11 at 20:13
  • Your original version said "fast", not "faster", as it does now, hence my first comment. Yes, I am comparing "the most highly optimized query" for the associative version, but it's also the version that is most likely to be in place. I'm comparing that to "the most poorly optimized" query on the bitmask field because, again, that's what will likely be in place. There is no way to create a bitwise index on a field, and if you plan to check permissions as part of the query, a bitwise operation is unavoidable. Do you have a better option for doing that? – Adam Robinson Apr 18 '11 at 20:21