I have been reading through through a number of guides on database optimisation and finest practices and lots of them suggest not using boolean flags whatsoever within the DB schema (ex http://forge.mysql.com/wiki/Top10SQLPerformanceTips). However, they never provide any reason why this really is bad. Could it be a peformance problem? could it be tough to index or query correctly?

In addition, if boolean flags can be harmful, what in the event you use to keep boolean values inside a database? Is it more beneficial to keep boolean flags being an integer and employ a bitmask? This appears like it might be less readable.

I do not think it is bad and I have never witnessed grounds mentioned with this either. Possibly some old database engines could not store them effectively, but modern ones do. While you say, it's much more readable to make use of booleans than bitmasks. Check this out question for the same discussion: http://stackoverflow.com/questions/2815987/is-adding-a-bit-mask-to-all-tables-in-a-database-helpful

The only real reason I possibly could think about could be cases best places to use ENUM rather. Sure, you simply want true and false now, but when you would add another thing after you'd have to do an ALTER TABLE operation, which is quite costly.

My prediction: portability of the design.

e.g.

  1. Microsoft Access goodies boolean as -1 as true or as false while other databases may treat boolean in a different way.

  2. In MySQL (version 4+) however, worth of zero is recognized as false. Non-zero values are thought true.

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

This thread offers the best solution that I have found. In a nutshell, approaching characteristics as boolean prevents you against modeling the information properly and individually (i.e., normalization). The greater solution -- not just in the perspective of modeling, but additionally in the perspectives of ease-of-use and ease-of-maintenance, is by using additional research tables. And when the thought of more joins and tables scares you, make sure to browse the entire thread.