From: Richard Huxton <dev@archonet.com>
To: j.random.programmer <javadesigner@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL design question: null vs. boolean values
Date: Sat, 15 Jan 2005 15:09:11 +0000
Message-ID: <41E93217.5020900@archonet.com> (raw)
In-Reply-To: <20050115144018.8932.qmail@web14207.mail.yahoo.com>
References: <20050115144018.8932.qmail@web14207.mail.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, javadesigner@yahoo.com
Subject: Re: SQL design question: null vs. boolean values
In-Reply-To: <41E93217.5020900@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox