4

In my application, I'd like the user to select his working days. then store them in the database. Of course my application will process the users' data like: Is today a working day for a specific user, who are the users that should work today , ... etc.

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

  1. Bitmasking field in the users table
  2. Many to many relationships tables by creating table for days, users and days_users. Thank you in advance.
skaffman
  • 398,947
  • 96
  • 818
  • 769
Gohary
  • 215
  • 2
  • 7
  • This is very subjective. Do you and your house tend towards clarity or optimisation? Also, whether either of those are even a factor here depend highly on your environment. – Grant Thomas Jan 16 '11 at 16:05

3 Answers3

8

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

A field should have a single meaningful value, otherwise you end up with querying issues - parsing the field every time you need to query using it.

Such a field also requires extra documentation, as the values it stores are not self describing.

Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • Though this works nicely with Ling2Sql and a `Flags` enum mapping, for instance with a `User` and `Role` table, where the user may have multiple roles, omitting the need for a third table. Admittedly, on the surface this implies a data/logic reliance, but not given the transferable functionality of languages/platforms this day and age. – Grant Thomas Jan 16 '11 at 16:10
  • @Mr. Disappointment - I like the explicitness of the many-to-many table approach. The bit field requires extra documentation (unless used in the exact way you described). – Oded Jan 16 '11 at 16:12
  • I tend to agree, just throwing it out there. – Grant Thomas Jan 16 '11 at 16:14
2

Bitmasking field is a bit more cryptic in nature and you need to create something else to interpret what you store in the bitmask.

The second approach is a lot more transparent and easily understandable and it's a bit more flexible if you need to add more values. With the bitmask, you again need to redo your bitmap decoder each time you add a value which can be a maintainance nightmare compared to the relational approach.

Jimmy Chandra
  • 6,472
  • 4
  • 26
  • 38
2

I have made the mistake of going with option 1, and given the opportunity to go back in time I would absolutely do it the other way.

Your database almost certainly will not use an index to make bitwise queries on your bitmask. So if you want to find, say, everyone working Tuesdays, you're going to do an index scan every time. As your tables get large, this can destroy your performance. You can try to optimize around this by caching a SELECT DISTINCT(bitmaskfield) ahead of time, doing the bitmask logic in your own application, and turing it into an appropriate WHERE bitmaskfield IN (...) clause, but that quickly becomes unmaintainable as you then have to update your distinct-bitmask cache in every place that you change values in the database.

The extra tables and joins may seem like a pain, but the bitmask will turn out worse. Trust me on this. Use your database as a database.

dkarp
  • 14,483
  • 6
  • 58
  • 65