Message-ID: <41E93217.5020900@archonet.com>
Date: Sat, 15 Jan 2005 15:09:11 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: "j.random.programmer" <javadesigner@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL design question: null vs. boolean values
References: <20050115144018.8932.qmail@web14207.mail.yahoo.com>
In-Reply-To: <20050115144018.8932.qmail@web14207.mail.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

j.random.programmer wrote:
> Hi all:
> 
> I was wondering if anyone had recommendations for the
> following scenarios:
> 
> (A) 
> I have three radio boxes in the user form
> 
> field_foo
> []yes  
> []no   
> []unknown
> 
> These are mutually exclusive and user input is always
> required.
> 
> So in the database, should I have something like:
> 
> field_foo  char(1)  not null check (field_foo in 'y',
> 'n', 'u')
>      ....OR....
> 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'.

Option 1 - the value is known, the user made a choice and it was to 
click the "unknown" box. The box could be labelled "marmalade" just as 
easily.

> (B)
> 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')
>      ....OR....
> field_foo char(1) check (field_foo in 'y')
> 
> The second choice always implies that NULL means not
> selected whereas whereas for the first choice,
> selected is coded 
> as 'y' and not selected coded as 'n'
> 
> Any advice, dear SQL experts ? 

First option. I'm not convinced the choice is optional - you've 
presented the tickbox to them so you have to assume they've read it and 
chosen not to tick it.

--
   Richard Huxton
   Archonet Ltd