Re: boolean datatype ... wtf?
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”.