pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL design question: null vs. boolean values
5+ messages / 4 participants
[nested] [flat]
* SQL design question: null vs. boolean values
@ 2005-01-15 14:40 j.random.programmer <javadesigner@yahoo.com>
2005-01-15 15:05 ` Re: SQL design question: null vs. boolean values Andrew Sullivan <ajs@crankycanuck.ca>
2005-01-15 15:09 ` Re: SQL design question: null vs. boolean values Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 5+ messages in thread
From: j.random.programmer @ 2005-01-15 14:40 UTC (permalink / raw)
To: pgsql-sql
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'.
(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 ?
Best regards,
--j
__________________________________
Do you Yahoo!?
Yahoo! Mail - 250MB free storage. Do more. Manage less.
http://info.mail.yahoo.com/mail_250
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL design question: null vs. boolean values
2005-01-15 14:40 SQL design question: null vs. boolean values j.random.programmer <javadesigner@yahoo.com>
@ 2005-01-15 15:05 ` Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 0 replies; 5+ messages in thread
From: Andrew Sullivan @ 2005-01-15 15:05 UTC (permalink / raw)
To: pgsql-sql
On Sat, Jan 15, 2005 at 06:40:18AM -0800, j.random.programmer wrote:
> 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'.
NULL actually means "unknown". SQL uses 3-valued logic: T, F, and
NULL. So NULL here is a not-unreasonable choice. (Some would argue,
however, that it's always better to have definite data. in which
case, your three-option choice is what they'd prefer. My own view
is that nullable boolean columns capture exactly the 3-value logic of
SQL, so what's the problem?)
> 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')
I'd use "boolean not null default 'f'", myself. But in any case,
this is _not_ a use for NULL, because you know absolutely what the
deal was: either the user selected, or else it didn't.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
--Alexander Hamilton
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL design question: null vs. boolean values
2005-01-15 14:40 SQL design question: null vs. boolean values j.random.programmer <javadesigner@yahoo.com>
@ 2005-01-15 15:09 ` Richard Huxton <dev@archonet.com>
2005-01-16 13:59 ` Re: SQL design question: null vs. boolean values j.random.programmer <javadesigner@yahoo.com>
1 sibling, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2005-01-15 15:09 UTC (permalink / raw)
To: j.random.programmer <javadesigner@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL design question: null vs. boolean values
2005-01-15 14:40 SQL design question: null vs. boolean values j.random.programmer <javadesigner@yahoo.com>
2005-01-15 15:09 ` Re: SQL design question: null vs. boolean values Richard Huxton <dev@archonet.com>
@ 2005-01-16 13:59 ` j.random.programmer <javadesigner@yahoo.com>
2005-01-16 18:47 ` Re: SQL design question: null vs. boolean values PFC <lists@boutiquenumerique.com>
0 siblings, 1 reply; 5+ messages in thread
From: j.random.programmer @ 2005-01-16 13:59 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL design question: null vs. boolean values
2005-01-15 14:40 SQL design question: null vs. boolean values j.random.programmer <javadesigner@yahoo.com>
2005-01-15 15:09 ` Re: SQL design question: null vs. boolean values Richard Huxton <dev@archonet.com>
2005-01-16 13:59 ` Re: SQL design question: null vs. boolean values j.random.programmer <javadesigner@yahoo.com>
@ 2005-01-16 18:47 ` PFC <lists@boutiquenumerique.com>
0 siblings, 0 replies; 5+ messages in thread
From: PFC @ 2005-01-16 18:47 UTC (permalink / raw)
To: j.random.programmer <javadesigner@yahoo.com>; Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
> 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
> )
> );
A simpler check would be :
CHECK(
(field_foo = 'y' AND foo_detail IS NOT NULL)
OR ( (field_foo = 'n' OR field_foo IS NULL) AND foo_detail IS NULL)
)
Which means " field_foo can be y, n, or NULL, and foo_detail should be
null except if field_foo is 'y' "
Also, IMHO, the Y/N/unknown case should have three values, NULL meaning
'the user has not answered this question'. Because if you insert a blank
row in the table and fill it afterwards, you'll know if it was 'answered
unknown' or 'unanswered'.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-01-16 18:47 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-15 14:40 SQL design question: null vs. boolean values j.random.programmer <javadesigner@yahoo.com>
2005-01-15 15:05 ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-01-15 15:09 ` Richard Huxton <dev@archonet.com>
2005-01-16 13:59 ` j.random.programmer <javadesigner@yahoo.com>
2005-01-16 18:47 ` PFC <lists@boutiquenumerique.com>
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