From: j.random.programmer <javadesigner@yahoo.com>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL design question: null vs. boolean values
Date: Sun, 16 Jan 2005 05:59:36 -0800 (PST)
Message-ID: <20050116135936.28081.qmail@web14206.mail.yahoo.com> (raw)
In-Reply-To: <41E93217.5020900@archonet.com>
Hi:
> > (A)
> > I have three radio boxes in the user form
> >
> > field_foo
> > []yes
> > []no
> > []unknown
> > ...
> > field_foo char(1) not null check
> > (field_foo in 'y', 'n', 'u')
> > ....OR....
> > field_foo char(1) check (field_foo in 'y', 'n')
> >
> 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.
I see what you are saying. It's "known" that the user
actually selected something (the choice they selected
just happened to have a label "unknown").
NULL would be apprpriate if they had selected
nothing at all, right ?
However, if a choice is required (meaning the front
end
html form cannot be submitted without some selection
at least), then couldn't we fold unknown into NULL ?
i.e.,:
user choice
yes -> 'y'
no -> 'n'
unknown -> null
Since it's guaranteed that the user will always
choose something ?
In fact, this is as you say similar to:
user choice
yes -> 'y'
no -> 'n'
marmalade -> null
I ran into another issue while designing my front end
html form.
------------------------------------------
field_foo
[ ] yes
[ ] no
if you answered "yes" in field_foo above, you must
enter detail here
foo_detail
[ ]
-------------------------------------------
This is a little tricky to capture in the database.
The issue
is that the nullability of one column depends AT
RUNTIME
on the value of another column (NOT at design time).
I ended up doing something like
create table xyz
(
field_foo char(1) check (field_foo in 'y', 'n'),
foo_detail varchar(255),
check (
case
when field_foo='y' and foo_detail is null
then false
else true
end
)
);
The constraint uses a case that *requires* some value
foo_detail if field_foo is 'y'.
I don't know whether this is the recommended
way to do the above or I'm making things too
complicated.. Any other opinions/suggestions ?
Best regards,
--j
__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail
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: javadesigner@yahoo.com, dev@archonet.com
Subject: Re: SQL design question: null vs. boolean values
In-Reply-To: <20050116135936.28081.qmail@web14206.mail.yahoo.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