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?
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.
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?
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