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

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.