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@crankycanuck.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