Re: boolean datatype ... wtf?

From: Hugo Kornelis <hugo_at_perFact.REMOVETHIS.info.INVALID>
Date: Sat, 02 Oct 2010 11:41:37 +0200
Message-ID: <r9vda6hiel09p6lreb6cgqb3c8lp4v9q39_at_4ax.com>


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 Received on Sat Oct 02 2010 - 11:41:37 CEST

Original text of this message