Why are BOOLEAN type columns problematic in relational database design?

Question

I've been working mostly with Oracle for the past few years, and am quite used to seeing single character varchar columns used as boolean values.

I can also see (per stack overflow answers), that suggested type for MySQL is TINYINT.

Now I've taken on my little side project - using DerbyDB, and it supports BOOLEAN columns, but not until after version 10 or so.

So, the question is, why is it so hard to incorporate a BOOLEAN column while designing a relational database? Am I missing something, or is it just pushed down the to-do list as unimportant, since you can use another column type meanwhile?

Answer 1

In the case of Derby, specifically, the answer is a bit of strange history: Derby, the open source database, was once called Cloudscape, and was a proprietary product. At that time, it fully supported BOOLEAN.

Subsequently, Cloudscape was purchased by Informix which was purchased by IBM, and IBM engineering decided to make Derby compatible with DB2. The reason for this was that, if the two databases were compatible, it would be easier for users to migrate their applications between Derby databases and DB2 databases. The engineering staff, however, did not remove the non-DB2-compatible features from Derby, they simply disabled them in the SQL grammar, leaving most of the implementation in place.

Subsequently, IBM open-sourced Cloudscape to the Apache Software Foundation, naming it Derby. The open source community, no longer bound by the requirement that Derby be completely compatible with DB2, decided to revive the BOOLEAN datatype support. And so Derby now has BOOLEAN datatype support.

Answer 2

Tom Kyte pretty much echoes your last sentence in this blog entry:

"It just isn't a type we have -- I can say no more and no less. ANSI doesn't have it -- many databases don't have it (we are certainly not alone). In the grand scheme of things -- I would say the priotization of this is pretty "low" (thats my opinion there)."

He's speaking from the Oracle perspective, but it applies to any relational RDBMS.