Database Design - Boolean Fields

I am designing a table where the object(s) that the table represents could have hundreds of boolean attributes.

Which method of design would you chose for this scenario:

Keep the booleans in the original object's table, potential for hundreds of nulls in a rowCreate a 2 more tables, one that has the boolean value names & ID. Another that relates an object (in original table) to a boolean value name/ID. No nulls, lots of joining

So second method would probably normalize it, but I would suffer a performance cost, whereas the 1st method would be the easiest/quickest for joins but tons of null records.

Thanks

Ben

Can you post some sample data so I can "see" what you mean..

|||

For example, say I have a table of homes. The homes table will have general attributes that all home objects share (address, sq ft, price) but then there are optional boolean fields that are attributes of homes (Is TwoStory, HasGasStove, HasAlarmSystem).

So would you store these boolean fields in the homes table, or create a table of attributes and a relationship table?

The first choice would have the potential for many null values in the table for each record. Most likely this table would not be normalized.

The second option would normalize the table, but suffer from the performance penalty of the joining 3 tables (tbl_homes, tbl_homeattributes, tbl_relationship_homes_attributes) to get the information. There would not be any null values though.

This situation could apply to any object that has boolean attributes: clothes, cars, computers, buildings.

Thanks

Ben

|||

I would create an Attributes lookup table with an AttributeId mapped to an attribute

AttribId AttribName
---------
1 IsTwoStory
2 HasGasStove
3 HasAlarmSystem

Then there would be a cross-reference table with a Home mapped to all its atributes.

HomeId AttribId
------
1 1
1 2
2 1
2 2
2 3
3 1

this way, if any new attributes get added in future you just add them to the attributes table and if there are homes that match that attribute you add them to the cross ref table. Similarly if existing attributes get removed or if a home loses some attributes you just update the cross ref table appropriately. This approach will give you a properly normalized structure with best scalability.