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