Re: SQL design question: null vs. boolean values

On Sat, Jan 15, 2005 at 06:40:18AM -0800, j.random.programmer wrote:
> field_foo char(1) check (field_foo in 'y', 'n')
>
> The second choice always implies that NULL means
> unknown,
> whereas for the first choice, unknown is coded as 'u'.

NULL actually means "unknown". SQL uses 3-valued logic: T, F, and
NULL. So NULL here is a not-unreasonable choice. (Some would argue,
however, that it's always better to have definite data. in which
case, your three-option choice is what they'd prefer. My own view
is that nullable boolean columns capture exactly the 3-value logic of
SQL, so what's the problem?)

> In the user form, I have a field like:
>
> field_bar
> [] select_me
>
> with ONE choice, which is optional.
>
> Should I code this as:
>
> field_bar char(1) not null check (field_foo in 'y',
> 'n')

I'd use "boolean not null default 'f'", myself. But in any case,
this is _not_ a use for NULL, because you know absolutely what the
deal was: either the user selected, or else it didn't.

A
--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
--Alexander Hamilton