Re: boolean datatype ... wtf?

From: Paul Mansour <psmansour2000_at_yahoo.com>
Date: Sat, 2 Oct 2010 04:52:38 -0700 (PDT)
Message-ID: <be53aa01-8729-4225-bad8-947a9c98a2ab_at_l20g2000yqm.googlegroups.com>


On Oct 2, 5:41 am, Hugo Kornelis
<h..._at_perFact.REMOVETHIS.info.INVALID> wrote:
> On Fri, 1 Oct 2010 07:51:37 -0700 (PDT), Paul Mansour wrote:
> >Erwin, suppose then that we do want to explicity take "unknown" into
> >account with two checkboxes as you say, or I think better yet, with
> >three mutually exclusive radio buttons, to explicity specifiy  Yes,
> >No, and Unknown or Male,Female and Unkown, or what have you. How then
> >would you model this? Is this a column  with three allowable values,
> >or a column with two values where no corresponding row indicates the
> >third option? Alternatively, there could be just two radio buttons
> >indicating, say, Male and Female, and neither one is pre-selected, so
> >it is clear whether or not the question was answered. Same question,
> >how is this then modeled?  I think Tony Andrews has a good question,
> >and it's more fundamental than the GUI.
>
> The most logical choices, IMO, are:
>
> 1) A nullable column with two possible values. Whether boolean or
> CHAR(n) and a CHECK constraint for the legal values is immaterial; any
> column with two possible values is basically a boolean in disguise
> (except that the DBMS doesn't hand you the algebraic operations on a
> platter, you have to explicitly code the ones you need).
>
> 2) For the NULL haters, that same column (but now not nullable) in a
> seperate table, so you can avoid the NULL by omitting the row there.
>
> (And of course, the NULL haters who also don't like extra tables will
> simply add a third value to the CHECK constraint and add the extra logic
> in their queries to make that third value act like NULL, so they can
> think that they have avoided the issue by simply renaming it).
>
> Best, Hugo

This is how I thought it would be done. And I wouldn't have thought it would be controversial.

You write above that “any column with two possible values is basically a boolean in disguise”. Contrast this a comment by Erwin above. Erwin writes about Gene’s post that in Genes’s case, his Boolean “really isn't just a Boolean”.

This highlights something I was thinking about during this discussion. I wonder is it useful to make the distinction between truly Boolean and superficially Boolean? Consider what was I think Vadim’s original examply, IsCreditWorthy – truly a Boolean as it divides the world into two parts, credit worthy and not credit worthy (and as Vadim notes, computable, so you don’t need a column for it anyway). You can’t simply add another category without changing the meaning of the first two categories. Now consider a column Size with the two allowable values: “Small” and “Large”. Maybe this is just superficially Boolean? “Medium” is probably just around the corner, even if it was not part of the original requirements of the system. We can add it without changing the name of the column, or changing the meaning of the two existing values. Perhaps then if a column is superficially Boolean it should not be treated any differently than a column with n potential values. Or maybe I’m just talking nonsense. Received on Sat Oct 02 2010 - 13:52:38 CEST

Original text of this message