Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Join Date
    Feb 2007
    Posts
    348

    Table Design - A bunch of Bools or and attributes table

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I would not split these out into another table.
    How about using a bitmask instead? That should handle 31 boolean values.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  4. #4
    Join Date
    Feb 2007
    Posts
    348
    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?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Storing permuted bits might get a bit (no pun intended) unwieldy when you start getting up to 10, 15, or 20 or more bits....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by blindman
    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

  9. #9
    Join Date
    Feb 2007
    Posts
    348
    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.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by starkmann
    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.
    In the I.T. world, we have a name for this. It is called "Job Security".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by mike_bike_kite
    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.
    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.

    Quote Originally Posted by mike_bike_kite
    You could even avoid using bit values all together and just store each value as a char
    Ewwww....gross.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Lovely article on bitwise operations: http://www.mssqltips.com/tip.asp?tip=1218
    George
    Home | Blog

  14. #14
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by georgev
    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.

    Quote Originally Posted by blindman
    Its just a bitwise operation
    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.

    Quote Originally Posted by blindman
    Ewwww....gross.
    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

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by mike_bike_kite
    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.
    ...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.

    Quote Originally Posted by mike_bike_kite
    Imagine you inherited this system and had to add office mobiles, personal mobiles, skype and pagers - you'd tear your hair out.
    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....

    Quote Originally Posted by mike_bike_kite
    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.
    You were not paying attention when you read the article. It said nothing about storing the actual phone numbers. Just demographic information.

    Quote Originally Posted by mike_bike_kite
    One day the business will ask for your binary field X to now have 3 or 4 possible values.
    No they won't, because I gather requirements and business rules before coding, not after.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •