Storing boolean values: bundle or individually?

Question

Hello,

I have about 50 discrete boolean values that I need to store in the database. These are logged every few seconds so I will be storing a lot of them over time.

The way this data would be used is: 1) Access a bulk of time to see flag status history 2) Find times at which flag changed status

Once stored, the records will not be updated.

Would you recommend storing each value in its own column, or bundling them in an integer values and storing in a few columns?

I am mostly curious about which approach would be better for storing/accessing data as it gets bigger? Eventually I will be getting data from multiple units 24/7, so there will be a lot of data, so I guess my question is: is there a performance/stability tradeoff between packed integers and individual columns.

I am using MySQL with VB.NET and PHP interfaces to it, but the question is more of a generic database design rather than mysql-specific.

Thank you,

Answer 1

These kind of facts rarely remain boolean as the application evolves.

Today they're True/False.

Tomorrow they're True/False/NA/Don't Know

The next day they become an integer.

Eventually, they become proper "conditions" based on other pieces of data.

Don't "pack" them 32 to the integer. That's short-sighted. Leave them as independent columns -- perhaps "bytes" or something smallish.

Answer 2

Even if you know that they will always be a bool, it is still better to have them in a separate column. This will make queries much easier and faster in the future. If you have to do bit unpacking for a query, you'll regret it. I also echo what S.Lott said. Prepare for the future (for example, what if you can't get the status for some reason? Will you say false or unknown?)