Date: Sat, 15 Jan 2005 10:05:33 -0500
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: SQL design question: null vs. boolean values
Message-ID: <20050115150533.GB6804@phlogiston.dyndns.org>
Mail-Followup-To: pgsql-sql@postgresql.org
References: <20050115144018.8932.qmail@web14207.mail.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20050115144018.8932.qmail@web14207.mail.yahoo.com>
User-Agent: Mutt/1.5.6+20040907i

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