Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004

    Multiple bit columns vs. Question and questionLookup table.


    I have a vendor that wants to hand over a DB to me. One of his tables currently has 10 bit fields in it. My thought is to ask for the table to be normalized further. My thought is that I should add a Questions and questionsLookup tables and get rid of all the bit fields. The logic being that instead of queries being

    "select fooID from foo where bitField1 = 1 or bitField4 = 1 orbitField7 = 1... group by id"

    they would be

    "select fooID from foo inner join questionLookup on foo.fooID = questionLookup.fooID where Questions.questionID in(1,4,7).

    My thinking is not only would this be easier to read and write queries for, it also would not require adding new fields whenever a new question is introduced, the way the current method does. I furthermore would expect it to be faster (provided proper indexing), but currently have no way to prove it.

    Has anyone done performance testing on this same sort of scenario that could share results? If not, is the general thought that my proposed modification would help or hinder performance?


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    your suggestion is very sensible for many reasons

    bit fields are ~so~ last century | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Quote Originally Posted by r937 View Post
    your suggestion is very sensible for many reasons

    bit fields are ~so~ last century
    Thanks -- however, I dont really have the grounds to make it a requirement. I can make it a recommendation, but unless it's going to hinder performance, its tough for me to enforce. I spent some time googling today, and havent yet seen anything regarding this.

Posting Permissions

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