Opinion: Should you use bit columns?

In an earlier post, I discussed why bit columns can be useful in SQL Server Indexes.

I used an example of a transaction table that had an IsFinalized column. And if it did, then it’s very likely that I’d want to have it indexed. But what I didn’t discuss was whether that bit column made sense in the first place.

An IsFinalized column in a transaction table seems simple enough. You might think of it as “yes it’s complete”. But what does that actually mean? Chances are that something has occurred to make it finalized. Perhaps it has been posted, or it’s an invoice that has been paid, or it’s a payment that has been applied against another transaction.

The point is that it probably represents a lack of normalization, at least to some degree.

Even if it’s just a column that a user updates to say it’s finalized, chances are that you’d want to know when that happened. So a column that held the finalization date and time might be better, and would be NULL if that hadn’t happened. (Whether or not these NULLable columns are desirable is the topic of a future post).

The other time that I regularly see these types of flags is when they represent a characteristic of an entity, but one that changes its nature. For example, I might have a People table and might have an IsEmployee column.

The concern with that type of design is that it often hides further normalization issues. I remember a post that Scott Hanselman made years ago where he mentioned that if you are designing a class in a high-level language, the presence of boolean attributes for the class usually means that you’ve messed up the class design.

And the same thing applies here. Rather than a People table with an IsEmployee column, there probably should be an Employees table that refers to that People table instead.

I don’t take an overly purist view on this. For example, yes the presence of a related row in an Employees table would indicate the same thing, but if there are no other attributes about the person being an employee, I might not want to create that other table.

2018-04-24