Naming Boolean (bit) Values in a SQL db

Question

The AdventureWorks Data Dictionary specifies that the [EmailPromotion] column in the [Contact] table is an int and that:

0 = Contact does not wish to receive e-mail promotions.
1 = Contact does wish to receive e-mail promotions.

and [Employee].[CurrentFlag] uses bit as follows:

0 = Inactive
1 = Active

My question has two parts:

• Is there a good reason to use the int datatype in place bit (both uses will be documented)?
• What naming conventions for boolean and boolean-like columns do you recommend? (e.g. IsActive, ActiveFlag, Active)

Answer 1

Is there a good reason to use the int datatype in place bit (both uses will be documented)?

INT is consistently supported. While BIT is becoming more common, I'll wager the support is just a mask for an INT column with a CHECK constraint. There's a good asktom question about why Oracle doesn't have a specific BIT/BOOLEAN data type...

What naming conventions for boolean and boolean-like columns do you recommend? (e.g. IsActive, ActiveFlag, Active)

Same as programming, they should be prefixed with "is" and when read, should present a yes/no question to infer the column is a boolean indicator. So "isActive" would be my decision, but I would be probing to see if the situation didn't require a STATUS table & foreign key.

Answer 2

For the first part they are probably allowing for future status codes (although with that column name it is hard to imagine what that could be...).

You will find a lot of dissent on your second question, but I prefer IsActive as a name in this case. I find it reads well, and prevents double negatives in code that you would get if you used something like IsInactive.