Re: The BOOLEAN data type

"Tom Ivar Helbekkmo" <tih+nr_at_eunetnorge.no> wrote in message news:86y92j8qcj.fsf_at_athene.i.eunet.no...
> "Bob Badour" <bbadour_at_golden.net> writes:
>
> > Some requirements will suggest the use of a boolean and some will not.
> > What's your point?
>
> If I may interject, I think the point of the whole discussion hinges
> on the validity of the statement by Joe Celko (for whom I hold great
> respect) that storing boolean data in a database is always wrong.
>
> Lots of more or less contrived examples have been given showing cases
> where booleans seem to be indicated or contra-indicated, but those are
> special cases. What I'd like to see, from those of you who know more
> about these things than I, is a discussion of the relative merits of
> boolean attributes (true/false/null) and text encodings (yes/no/null)
> in cases where either seems sufficient at first glance.

I'm not a big fan of null. It's definition and behaviour in SQL are inconsistent at the very minimum and extremely harmful in practice. A boolean is better than a text yes/no if yes and no mean the same as true and false respectively. Concatenation doesn't really have any meaning for yes/true and no/false, but conjunction and implication do.

People often misuse boolean just as they misuse any other type. For instance, the boolean type has two values neither of which are male or female and neither of which are shipped or returned and neither of which are open or closed and neither of which are on or off etc. etc. etc.

> Can booleans, combined with basic logic operations and the predicate
> IS UNKNOWN, be acceptable in cases where derived data is never stored,
> and each data item is true, false or unknown (null)?

Boolean is a type which comprises the set of acceptable values and the operations on those values. It makes no sense to talk about booleans combined with logic operations. It's like talking about integers combined with integer operations. Without the operations, the concept of integer is meaningless.

Since NULL and IS UNKNOWN are not necessary, I suggest we just drop them and stick to boolean.

> Or are there
> problems with the evaluation logic of SQL that will kick you in the
> teeth when you least expect it?

Well, that's true any time one attempts to use logic with SQL NULLs.

> Should a text encoding of boolean
> values be yes/no/unknown and NOT NULL?

You have not suggested any argument for using text encodings of boolean instead of boolean. Doing so only replaces boolean operations with text operations to no good purpose.

> Should booleans be NOT NULL?

Bluntly, all domains should be not null. Received on Wed Apr 09 2003 - 22:34:19 CEST