Re: Mixed, or conditional, datatypes
Date: 26 Mar 2007 05:00:31 -0700
Message-ID: <1174910431.097114.171000_at_y80g2000hsf.googlegroups.com>
On Mar 25, 2:14 pm, "sinister" <sinis..._at_nospam.invalid> 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.
>
> TIA
Sounds like you may need to establish a hierarchical type of
implementation with a M:N cardinality between questions, answers and
1:0 cardinality between question and lineage. I see at least 4
relations.
SK: surrogate key PK: natural primary key FK: foreign key
U: Unique
question: questionid(SK), question_phrasing(PK) answer: answerid(SK), answer_phrasing(PK)
question_answer: answerid(FK--> answer), questionid(FK--> question) question_lineage: answerid(FK--> answer), questionid(FK--> question), question_nextid(FK --> question)
(answerid + questionid + question_nextid) --> PK
Of course this is purely hypothetical.
Hope this helps... Received on Mon Mar 26 2007 - 14:00:31 CEST