Re: SQL design question: null vs. boolean values

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