Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Mixed, or conditional, datatypes

Re: Mixed, or conditional, datatypes

From: JOG <jog_at_cs.nott.ac.uk>
Date: 25 Mar 2007 12:42:06 -0700
Message-ID: <1174851726.477597.224500@y80g2000hsf.googlegroups.com>


On Mar 25, 2:23 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> sinister wrote:
> > Example: suppose I'm storing responses to a diagnostic interview. One of
> > the questions asked of the subject is something like
>
> > 7. Have you always lived in the US?
>
> > 7a. If the answer to 7 is "no," how many years have you lived in the US?
>
> > What I'm wondering is, what's a good way to code this in a relational
> > database?
>
> > If I use one field, I could code as an integer, with -1 reserved if the
> > answer to 7 is "yes", but that seems kludgey.
>
> > I could use two fields, the first a boolean for 7, and the second an integer
> > for 7a, but then the table is in some sense redundant: the fields are
> > connected by the logic "if 7 is true, then 7a is NULL (or has no meaning
> > even if assigned)". That doesn't seem all that clean either.
>
> The truth of the matter is you really have two surveys; even though,
> someone presented the surveys to respondents as a union of all the
> questions. Lifelong residents answer one survey and part time residents
> answer another.
>
> From the above observations, the design seems obvious: Use two relations.

Just to echo this, without a shadow of a doubt more than one relation is required, because you are attemptiing to record more than one type of proposition - and the RM is based on collectivizing sets of propositions of the same type. A "person_details" relation and a "years_in_US" relation hence seems intuitive, with the latter containing an appropriate foreign key to the former, and a simple JOIN being utilized in queries if and when appropriate. Received on Sun Mar 25 2007 - 14:42:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US