Flag storage in the database

Question

In my application, I would like the user to select their workdays. then save them to the database. Of course, my application will process user data, such as: Is it a business day for a specific user, who are the users who should work today, etc.

My question is: what is the best practice for this? should use:

• Bitmask field in user table
• From many to many link tables, creating a table of days, users, and days. Thank you in advance.

Answer 1

I would say that bitmask fields are a relational anti-pattern.

The field must have one meaningful value, otherwise you will get problems with the request - parsing the field every time you need to request its use.

Such a field also requires additional documentation, since the values ​​it stores are not described independently.

Answer 2

The bitmasking field is a bit more mysterious in nature, and you need to create something else to interpret what you store in the bitmask.

The second approach is much more transparent and easy to understand, and it is a bit more flexible if you need to add more values. With the bitmask, you again need to redo the bitmap decoder every time you add a value, which could be a maintenance nightmare compared to the relational approach.

Answer 3

I made a mistake by going with option 1, and given the opportunity to return in time, I would have done it differently.

Your database will almost certainly not use the index to create bitwise queries in your bitmask. Therefore, if you want to find, say, everyone working on Tuesdays, each time you will do an index scan. As your tables grow large, it can ruin your performance. You can try to optimize around this by copying SELECT DISTINCT(bitmaskfield) advance, executing the bit mask logic in your own application and applying it to the corresponding WHERE bitmaskfield IN (...) , but this quickly becomes unreachable as you need to update your distinct -bitmask in every place where you change the values ​​in the database.

Additional tables and joins may seem painful, but the bitmask will be worse. Believe me. Use your database as a database.