Not so long ago I talked to my colleague and he was definitely against using bit masks because it is hard to understand all the values that are stored in the database. In my opinion it is not always a bad idea to use them, for example to determine the roles of the current user. Otherwise you need to store it in a separate table, which will cause one more JOIN.
Can you please tell me if I am wrong? Any other side-effects, advantages/disadvantages of using bit masks?
Database – Advantages and Disadvantages of Using Bit Masks
advantagesbitdatabasepatterns-and-practices
Best Answer
I work with an application that uses bitmasks to store user role assignments. It's a pain in the butt. If this makes me biased, guilty as charged.
If you're already using a relational database, it is an anti-pattern that violates most relational theory and all the normalization rules. When you build your own data storage, it may not be such a bad idea.
There is such a thing as too many tables being joined, but relational databases are built to handle this. Many have additional features if performance becomes an issue: indexes, indexed views, etc. Even if the values you're looking up don't change very often, which is an advantage for Bitmask, the over-head of having to manage indexing is pretty easy on the database.
Although database do a good job of aggregating data, they can get sluggish when you start introducing things like complex formulas or Scalar Functions into datasets. You can do the bitwise in your app, but if all you're doing is getting related data (looking up a user's role(s)), you're not taking advantage of what your data storage does best.
My last argument against it would be simplicity for other developers. You have users, roles and assignments. It's a many-to-many relation set (because there's more than one relationship) that is so common, it should be easy to manage. It's just CRUD stuff.