SQLTeam.com | Weblogs | Forums

How to design effectively a check table (bit fields)


#1

Hello,

I need to design a table that keeps true/false values for checks running on an application form.
I have 10 different categories with 3-4 different checks so I'm looking for an advice on how to design the table.

I thought of the following:

Solution 1: Create one bit field for every running check like this {Appno, Check1, Check2, Check3,.......,Check40}
My concern in this solution is that I will have approximately 40 fields with each record using only 3-4 fields each time, which is not the best solution.

Solution 2:Create the table like this {Appno, CategoryID, Check1, Check2, Check3. Check4}. My concern in this solution is that check fields will have a different meaning based on the category. Is that a problem?

Is there a better way to go with?

Thank you in advance.


#2

Solution 2 is not the best. Solution 1 is OK (not knowing much about your app!) since SQL packs bit columns. Your 40 columns is thus only 5 bytes


#3

I'd use a couple of integer columns and set/test bits within each column. That makes it much easier to add bits as no table structure changes are required.


#4

NOt sure it would help, but integer columns with set/test bits, grouped together, would also enable you to do a Bitwise AND / OR to easily discover if any bits, for a given Appno, were set / all-unset etc. whereas with individual column bit datatype columns you would have to do

Check1 = 1 OR Check2 = 1 OR ...

which is perhaps a bit more tedious and possibly performs worse.


#5

Thank you all for you answers!

ScottPletcher / Kristen,
when you say integer columns with set/test bits you mean having an integer like 1010001 and every digit of the integer will represent a check bit?


#6

I was thinking of a binary integer (which might be what your 1010001 represents?)

So Check1 = 1
Check2 = 2
Check3 = 4
Check4 = 8
etc

Add together, or bitwise-OR them, to forma value.

To check if .Check4 is set then:

SELECT Appno, CategoryID
FROM MyTable
WHERE (BitChecks & 8) = 8

To check if BOTH Check3 and Check4 (i.e. 4 + 8 = 12) are set then:

WHERE (BitChecks & 12) = 12

To check if EITHER Check3 and Check4 are set then:

WHERE (BitChecks & 12) <> 0

#7

If I understood correctly, for example if check1, check5 and check10 are set, the value of BitCheck is 529? Is this number unique?

Thanks again for the explanation!


#8

Yes, the values would be unique. I strongly prefer to test by <> 0 rather than = specific value. It's too easy to copy/paste and leave an old value in, resulting in a bogus test condition that can never be met.

WHERE (BitChecks & 8) <> 0

To set that bit on, you just OR it into the value:

SET BitChecks = BitChecks | 8 --turn on the "8" bit


#9

I agree - provided that you are not testing for multiple bits ALL set.


#10

Thank you all for the mini tutorial, your answer were very helpful!