Related boolean columns -- separate columns or bitpacked into an integer column?

Question

Hello I have a database for a certain record where it needs to store a 1 or a 0 for each day of the week. So which one would be better? Bitshifting each bit into an integer and just having an integer in the database named days or should we make all of them separate boolean values so to have sunday, monday, tuesday... columns?

Note that these columns are only used for a computation in our software(not on the DB itself) so the only thing that will be done with these values is selecting, updating, and inserting.

Answer 1

I'd go for separate columns, for the following reasons:

• That would seem like the better-designed database model (because clearer, more intuitive, easier-to-understand), and everyone will probably not need any further explanations;

• "Which bit was for Sunday again...? Did I assign the most-significant bit to it, or the least-significant one?" -- You won't run into such problems with separate, named columns... therefore less potential for bugs.

• If you later want to enhance your database model so that you could store NULL for single days, you will almost definitely want a separate column per day. Otherwise, you'd need at least two bits per day (since you now have 3 possible states, and 1 bit no longer suffices for that) and an appropriate, home-baked encoding scheme.

• I'm quite sure today's RDBMSs are smart enough to pack several boolean columns together;

Answer 2

Separate columns.

The DB engine will pack them anyway and work with the bits transparently for you. I suspect it's better at this than you or me rolling our own...

Answer 3

It depends on your needs. Sql Server suffers slower performance when querying with bitshifting. If you'll be doing a lot of filtering for just one day per query, then I'd recommend separate bit fields for each day.