Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	b=Yf2ogB1GnlH/DNT0vDbl92Pwl0FYCCx96zJp/x5n83BB2OYWRhLzsugzomvIGrgVFkXQr/TMw7biCuY6hUUuEYOq370Fic67d4Tk9QEoQJuP8r3PjiOBn56AcAsEzq/NJIwfP5B9T9rV1LVsIEAgCNUhGNHAjTd628ADIGj6OIk=
	;
Message-ID: <20050116135936.28081.qmail@web14206.mail.yahoo.com>
Date: Sun, 16 Jan 2005 05:59:36 -0800 (PST)
From: "j.random.programmer" <javadesigner@yahoo.com>
Subject: Re: SQL design question: null vs. boolean values
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <41E93217.5020900@archonet.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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