26. April 2006 06:26
Well, well, well, where do I start? I'm recreating this stored procedure for a client and I'm listing all columns I need from a table. What do I see? Something I thought I would never see in my life. The table contained a list of about 14 flags that specified something to be on or off. To be more specific, the columns looked like this: flag1_on, flag1_off, flag2_on, flag2_off and on and on it goes with a total of 7 flags * 2 (1 for off and other for on). I look at the data and I see that when flag1_on is 1 then flag2_off is always 0.
What is the correct design for this table? Well if you have a defined numbers of flags it is ok to have them layed out as columns of the table, but the columns should be flag1, flag2, flag3, etc of type bit. Why bit? Because the bit type holds a value of either 0 or 1; on or off. This gives you 3 possible choices: flag not set (null value), on, or off. If you need only 2 values then you don't allow nulls in the column and specify a default value [optional]. You might ask yourself why not use a char(1), varchar(1), nchar(1), nvarchar(1), or integer for that matter. The answer is very simple: the bit type is the smallest of them all and allows only a 0 or 1, while a char or varchar might allow other characters such as ‘A’, ‘X’, ‘4’, ‘$’, etc and adding inconsistency to the data.
If the number of flags is undefined because they are dynamic then a different approach is taken. You have the parent table with a many-to-many relationship to this newly created table that holds the name of the flag and whether the flag is on or off. In this case the flags are displayed as rows rather than columns.