When is it better to store flags as a bitmask rather than use an associative table?

Question

Im working on an application in which users have different permissions to use different functions (for example, Read, create, download, print, approve, etc.). The list of permissions should not change frequently. I have several options for storing these permissions in a database.

When 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

Save a bitmask for each user.

  User
 ----
 UserId (PK)
 Name
 Department
 Permissions 

[Flags] enum Permissions { Read = 1, Create = 2, Download = 4, Print = 8, Approve = 16 } 

Answer 1

Great question!

First, let us make some assumptions about the "best."

I assume that you do not really care about disk space - the bitmask is spatially efficient, but I'm not sure if this is important if you are using a SQL server.

I guess you really need speed. A bitmask can be very fast when using calculations - but you cannot use an index when querying a bitmask. This should not really matter, but if you want to know which users have created access, your request will look like

 select * from user where permsission & CREATE = TRUE 

(Today we do not have access to SQL Server on the road). This query will not be able to use the index due to the mathematical operation, so if you have a huge number of users, it will be quite painful.

I assume that you care about maintainability. From the point of view of maintainability, the bitmask is not as expressive as the main problem domain, as the preservation of explicit permissions. You will almost certainly have to synchronize the value of the bitmask flags for several components, including the database. Not impossible, but pain in the back.

So, if there is no other way to evaluate “better”, I would say that the bitmask route is not as good as storing permissions in a normalized database structure. I don’t agree that it will be “slower because you need to make a connection” - if you don’t have a fully dysfunctional database, you cannot measure it (whereas a query without using an active index can become noticeable slower even with several thousand records).

Answer 2

Personally, I would use an associative table.

The bitmask field is very difficult to request and join.

You can always match this with your C # flag enumeration and if performance becomes and database reorganization.

Readability before premature optimization;)

Answer 3

Keep permissions normalized (i.e. not in a bitmask). Although this is clearly not a requirement for your scenario (especially if permissions will not change frequently), this will make the request much easier and more understandable.

Answer 4

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

Use parameter 1 if

• You expect permissions to evolve for many.
• If you may need to perform a rights check in the database stored procedures themselves
• You do not expect millions of users to keep records from growing in the spreadsheet.

Use option 2 if

• Permissions are limited to small
• You expect millions of users.

Answer 5

The only time I can use the bitmask field to store permissions is when you are really limited in how much physical memory you have ... for example, maybe on an old mobile device. In truth, the amount of memory you save is not worth it. Even for millions of users, the hard drive is cheap, and you can expand access rights, etc. It is much simpler using a non-brittle approach (this is a message about who has what permissions, etc.).

One of the biggest headaches I've encountered is assigning user rights directly to the database. I know that you should try and use the application to administer yourself and not really with the data of the application as a whole, but sometimes it is simply necessary. If the bitmask is not really a character field, and you can easily see what permissions someone has instead of an integer, try to explain analytics, etc., How to give write access, etc. To someone, updating the field ..... and pray your arithmetic is correct.

Answer 6

This will be useful when they do not change in their structure and will always be used together. Thus, you have few trips to the server. They are also good in performance because you can influence all rights in a single variable binding.

I personally don’t like them ... In some intensive use applications they are still in use. I remember how I implemented chess AI using them, because you could evaluate the board in one comparison. This is a pain to work with.

Answer 7

I would always keep it normalized, unless the database just holds the record for you, and you never do anything with it other than retrieving and saving. The scenario for this is that when entering the system, a line of user rights is selected, and it is processed and cached in the server code. In this case, it really does not really matter that it is denormalized.

If you store it in a row and try to work on it at the DB level, you will need to do some gymnastics to get permissions on page X, which can be painful.

Answer 8

I recommend using a bitmask for the following reasons:

• Index cannot be used effectively.
• Inquiry more difficult
• Reading / service has been hit hard.
• The average developer there does not know what a bitmask is.
• Flexibility is reduced (upper limit to nr bits in number)

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

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

Adding a column is a modification of the scheme, but I assume that adding the “Cleanup” privilege will require some code from the code, so the privileges may not be as dynamic as you think.

If you have a sick data distribution, for example, 90% of the user database does not have a single permission, the following model also works fine (but falls apart with large scans (one five-way connection versus a single scan of a full table).

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

Answer 9

Your queries will work faster using a flag enumeration (bitmasks) because you do not need to include a join in a linked table in order to understand the meaning.