Re: Foreign key to a view (UNION of two or more tables),

* Karl O. Pinc <kop(at)meme(dot)com> 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(at)metux(dot)de
---------------------------------------------------------------------
Realtime Forex/Stock Exchange trading powered by postgresSQL :))
http://www.fxignal.net/
---------------------------------------------------------------------