Why is it bad to use boolean flags in databases? And what should be used instead?

Question

I've been reading through some of guides on database optimization and best practices and a lot of them suggest not using boolean flags at all in the DB schema (ex http://forge.mysql.com/wiki/Top10SQLPerformanceTips). However, they never provide any reason as to why this is bad. Is it a peformance issue? is it hard to index or query properly?

Furthermore, if boolean flags are bad, what should you use to store boolean values in a database? Is it better to store boolean flags as an integer and use a bitmask? This seems like it would be less readable.

Answer 1

I don't think it is bad and I've never seen a reason stated for this either. Perhaps some old database engines couldn't store them efficiently, but modern ones do. As you say, it's a lot more readable to use booleans than bitmasks. See this question for a similar discussion: http://stackoverflow.com/questions/2815987/is-adding-a-bit-mask-to-all-tables-in-a-database-useful

Answer 2

The only reason I could think of would be cases where you should use ENUM instead. Sure, you only want true and false now, but if you'd want to add something else later than you'd need to do an ALTER TABLE operation, which could be very expensive.

Answer 3

My guess: portability of your design.

e.g.

1. Microsoft Access treats boolean as -1 as true or 0 as false while other databases may treat boolean differently.

2. In MySQL (version 4+) on the other hand, value of zero is considered false. Non-zero values are considered true.

Answer 4

Granted database practice has little to do with theory, I'll still attempt theoretical explanation. Tables are finite relations. Each relation is an extension of predicate. A Boolean attribute is a misnomer for a predicate.