How to design effectively a check table (bit fields)


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.

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

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.

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.

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?

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

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

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

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!

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

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

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