I'm in the process of building an application based on a form that was used in an older system.
I have a collection of attributes to a given record as many as six of them may be related (although not dependent) but there's a total of 17 different attributes. Given that they are all Boolean, does a table of attributes make more sense or does it make more sense to have 17 boolean fields in each row?
I was originally going with 17 different booleans in the main table but I changed direction and started down the attribute route. As I have started working on that, I began to wonder in the fact that some of them have no real relationship to each other makes it a bad idea to do the attributes this way.

I was hoping some of you who have been doing this a lot longer than I had could offer some suggestions on direction.

I would not split these out into another table.
How about using a bitmask instead? That should handle 31 boolean values.

I'm decidedly against using bitmaps.

The reason that database engines were created, and one of the specific purposes driving the creation of SQL was to divorce data storage from program logic. A bitmap only has meaning in the context of programs that know how to interpret that mask. Individual columns with names and attributes are directly usable by any user with the permissions to access them.

-PatP

Blindman,
I'm building the initial prototypes in Access but I think I can bit map pretty easily just using integers and a function.
Pat,
Are you suggesting I keep the fields in the main table?

Yes, and I don't think Pat would disagree with me there.

Pat, I understand your concerns about divorcing data storage from program logic, and that is why on the rare instances where I have used bitmaps I insisted that there be a table translating the bitmap values. Would never want that hard-coded some place outside the db.

Yes, for your example I'd go with separate columns in the main table.

As blindman clarified, having another table that showed the bitmap values would make this tolerable. I assume that he's thinking of primative values (meaning seven rows for seven bits). I would insist on permuted values (128 rows for seven bits). Most of the database engines I work with would condense the seven columns into a single "unit of storage" that might be smaller than the equivalent bitmap.

I guess my main point is to let the database engine do the work for you. Don't code what the engine will do for you... Even if the database implementation is inefficent at first, it will eventually get better at no cost to you: no code to re-write, no conversion needed.

-PatP

Storing permuted bits might get a bit (no pun intended) unwieldy when you start getting up to 10, 15, or 20 or more bits....

That would be a major reason why I don't do it... I'm an idiot, so I like simple things. Nice, neat, named, typed columns just tickle my fancy.

-PatP

Thanks to both of you for your comments. I appreciate it. In the end, I'm going to do the 16 booleans. The geek in me loves the idea of storing it as bits and unraveling the yarn but I'm also concerned about the next person to come along. I don't work with people that are particularly savvy and who knows who will be the next person to look at this thing.
Thanks again.

In the I.T. world, we have a name for this. It is called "Job Security".

Grouping the bits yourself into bytes sounds nasty. Storage space should hardly matter whether the record gets stored in 17 bits or 17 bytes - either case is tiny - save yourself a lot of effort and use separate fields.

You could even avoid using bit values all together and just store each value as a char - it doesn't matter whether you store values as (1 or 0) or (Y or N ) but at least you'll be able to expand things in the future when the business decides that this boolean can now have 4 different values.

Its just a bitwise operation...
...plus, some searches will definitely be faster against a bitmap than against bit columns which aren't good candidates for indexes.

Ewwww....gross.

Lovely article on bitwise operations: http://www.mssqltips.com/tip.asp?tip=1218

Bitwise operations aren't difficult to understand but they're a pain to implement as you have to do all this garbage each time you want to reference a field. For the scenario in the article you should store this data in a table and certainly not encode it as part of some weird id.

The article showed how "simple" it was to store information for different types of phone. It may be understandable to the person who coded the system but it will be incomprehensible to those after him. Imagine you inherited this system and had to add office mobiles, personal mobiles, skype and pagers - you'd tear your hair out. What if you needed to know whether these numbers are cheap to call from abroad?

Better would be to just have a type field alongside each phone number with values like "home phone number" etc. You could either just search the string for "fax" or "free", or better still have a series of supporting fields in the type table to indicate these values. That article was not good advice in my opinion.

Any bitwise operation is a pain - and keep in mind you'll have to do this operation each and every time you access the field. You mentioned job security for this reason.

One day the business will ask for your binary field X to now have 3 or 4 possible values. How will you deal with this? if you were determined to continue with the binary fields then you could grab another bit from somewhere then combine the two bits to allow 4 values but then you'll have to alter the code everywhere and no-one will ever be able to understand it. In the article George gave how would you add office mobiles, personal mobiles, skype and pagers?

I don't think using a char field is any more gross than using bit fields but it does have the advantage of being easy to read and code and is expandable in the future.

Mike

...if the company hires noobs.
I design my database around business requirements and performance, not around the possibility the company may hire someone unqualified to administer them.

Not until they hit more than 31 phone types. Until then, a bigint bitmask will handle it easily.
You would prefer altering the schema by adding a new column each time they want to track a new variation?
Ewwww....

You were not paying attention when you read the article. It said nothing about storing the actual phone numbers. Just demographic information.

No they won't, because I gather requirements and business rules before coding, not after.