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

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 neagtives in code that you would get if you used something like IsInactive.

Answer 2

Whatever floats your boat. The only important thing is naming convention and consistency across the database.

Using int for a flag isn't justified, I would've used tinyint, I hardly ever use bit at all, because I'm always open for all kinds of possibilities out there.

I usually name those columns Active (if there are only two statuses) and StatusId (if there is more than one status).

Answer 3

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.