Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2008
    Posts
    1

    Question Unanswered: Question about bit masking.

    We are in the process of doing a database redesign. Our system architect is planning to a bit masked integer field vs. lookup tables. I personally have never used bit masking at the db level. I was wondering if this is a good design choice.
    I should also note that our application will be db agnostic so it will need to work on SQL, Oracle, MySQL, etc.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm not sure what you mean be "bit masked integer field vs. lookup tables". The general rule would be not to use bitmasks and design your database properly but there are exceptions for some people. I never use bitmasks in anything where there would be more than trivial volumes of data (you can't use indexes for starters). The only thing I've seen them commonly used for is application permission tables, but I still prefer a standard TNF design myself.

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I use them, but never for a column that you might put by itself in a where clause, or join on.

    The tables I use them in are fairly big, up to a billion rows or more, so to use a normalized design would really bloat things.

    In my case, I use them for recording the possible reasons (of which there may be many) the result of a calculation may be trusted.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jezemine
    In my case, I use them for recording the possible reasons (of which there may be many) the result of a calculation may be trusted.
    Then the assumption is your data can't be trusted?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by pootle flump
    The general rule would be not to use bitmasks and design your database properly but there are exceptions for some people.
    Uhm...the system tables themselves use bitmasks.
    Bitmasks are most useful when you need to represent multiple states in a single column. You can think of them as a replacement for a simple subtable implementing a one-to-many design. It is my understanding that bitmask operations are very fast, and so bitmasks may be preferable to a subtable for some performance applications.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by pootle flump
    Then the assumption is your data can't be trusted?
    in my case it's more that for a particular calculation, maybe the statistics weren't good enough (too few points in the sample, or too large of a stdev) to call an answer good. I use the bitmask to enumerate the reasons why a particular calc was deemed good.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I was thinking more on searchable columns really. I don't use them much for simple attributes - I suppose you guys are right and if it is a column that will not be joined or filtered then fine. I still think an atomic value should be the default.
    Quote Originally Posted by blindman
    It is my understanding that bitmask operations are very fast, and so bitmasks may be preferable to a subtable for some performance applications.
    The operation itself is fast but searching a table based on bit logic requires an index scan. If you can't avoid the scan with any other design then perhaps it is viable.

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245
    My .02. Bitmasks are good, but only if the member population is a well-established, near constant domain. If the member population is growing or changing frequently, it makes for a really bad solution.

    But maybe that's too obvious.

    One other thing; if you're going to use a bitmask, PLEASE document the values extensively and socialize (ie, communicate) the solution to everyone (developers, admins, operators and the poor sobs who have to maintain the system). Nothing is more frustrating than staring at a column of meaningless numeric values and trying to deduce the logic behind the numbers.

    Not that I have ever had to do that.

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

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