Best way to store lots of boolean columns in table.

Question

I'm not sure if this kind of question has been asked before but I searched and could find anything.

I am working on a database at the moment that has records that have lots of boolean based values stored with them, so the table structure looks something like this:

===Table===
ID <- int
Name <- string
Bool1 <- bool
Bool2 <- bool
Bool3 <- bool
Bool4 <- bool
Bool5 <- bool
Bool6 <- bool
Bool7 <- bool
Bool8 <- bool
Bool9 <- bool

not all the boolean values are set a once, so each record can have one,many or none selected.

I have thought about doing something like this:

==Main Table==     ===Second Table====
ID <- int PK          ValueID <- PK links to Main Table ValueID
Name <- string        ID <- int
ValueID <- FK         Value <- Contains name of assigned value eg Bool1, Bool2

So there is a one to many relationship between the Main Table and Second Table joined on ValueID. So the the second table only has the data for the selected items and rather then a heap of empty boolean values in the main table.

The reason I thought about doing it this way was it would allow me to add different values to be stored against the record in the future rather then changing the table structure.

Would this be a good way to store the lots of boolean values?

I would really like to be able to bind this to a form with check boxes , would going this way make it hard.

So if this is a bit hard to understand what I need, I don't really know how to explain it in text.

Thanks.

Answer 1

Just an idea, you can use int to store bits, bitwise operation are efficient, however I am not sure how well SQL support them. In C# there are Bit Enums, so you can save its value directly.

Answer 2

If you expect the number of booleans for a main record to change, I recommend that you go for the separation into two tables.

Do not put the FK (ValueID) into the main table, but put the MainID into the second table instead.

Some things that are not clear from your question: If a value is not set, is this equivalent to a false value or do you have ternary logic with null values? If the latter is the case, you will have to put a boolean field into you second table. Otherwise the absence of a record in the second table is enough to give the logical false.

If the number of boolean attributes does not change, you could put them into a single integer field and mask them as Mike Chaliy suggests.

Answer 3

Leave it as it is!

The database will pack multiple bit fields very efficiently. I have seen people doing things like having a 32-bit int field, thus allowing them to store their 17 boolean values using bitmasking and 'leaving some room for additional fields'. This is just dumb, it is a maintenance nightmare and your queries become littered with bitmasks making them difficult to maintain.

I will reiterate, keep it simple, just have the boolean columns. If you need a new column, then add it. You do not need to create a separate table.