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

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