[SQL] SQL design question: null vs. boolean values

[j.random.programmer]

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

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] SQL design question: null vs. boolean values

[j.random.programmer]

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

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]