* Karl O. Pinc <kop@xxxxxxxx> wrote:
<snip>
> So the problem then is that there are codes (e.g. cities) that are
> used by multiple questions, sometimes optional or N/A is allowed
> and sometimes not.
For such cases you could introduce another layer, like a datatype.
Each question can be answered with some datatype, and may optionally
be empty - just like rows in a database :).
CREATE TABLE q_types
(
id oid default nextval('q_type_id'),
description text,
..
);
CREATE TABLE q_type_values
(
type_id oid,
answer_id oid,
title text,
...,
PRIMARY KEY(type_id, answer_id)
);
CREATE TABLE q_question
(
id oid default nextval,
qtype oid references q_types (id),
question text
);
CREATE TABLE q_answer
(
user_id oid references q_user(id),
question_id oid references q_question(id),
value oid
);
...
Maintaining the integrity of q_answer.value is a little bit more
complicated. I don't know if its possible with an foreign key,
since it spans over multiple tables ( question_id->qtype + value )
You probably need an hand-written trigger.
Unanswered questions (or selected n/a) could be marked by simply
setting value to NULL. You also could introduce a separate flag
in q_answer for that.
BTW: its probably not such a bad idea to present the whole
stuff as one writable view to the frontend and let the database
do the logic of mapping answer texts <-> ids.
cu
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT service
phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact@xxxxxxxx
---------------------------------------------------------------------
Realtime Forex/Stock Exchange trading powered by postgresSQL :))
http://www.fxignal.net/
---------------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
