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

On 06/19/2005 11:16:34 AM, Jose Gonzalez Gomez wrote:
> On 6/17/05, Bruno Wolff III <bruno@wolff.to> wrote:
> > On Fri, Jun 17, 2005 at 14:35:01 +0200,
> >   Jose Gonzalez Gomez <jgonzalez.openinput@gmail.com> wrote:
> > >
> > > The problem comes when you have questions that may be not
> applicable
> > > (8), or optional (doesn't know, doesn't answer) (9). The easy
> solution
> > > would be to have four tables:
> > >
> > > yes_no
> > > yes_no_not_applicable
> > > yes_no_optional
> > > yes_no_not_applicable_optional
> >
> > How about having a table with the valid codes for each question?
> > This should be relatively easy maintain and you can easily set up
> > a foreign key reference to this table to enforce integrity.
> >
> There would be no problem in doing so with such an easy case, but
> think about having a table with cities (hundred, thousands?) and then
> have four copies for each of the above posibilities with its related
> maintenance nightmare.

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.

Don't use constraints, use triggers instead and have them check that
the data is on the appropriate table.  You then have two approaches.
The first is completely dynamic.  You have a "control" table
with a row for every column (question).  In the row
you store whether or not n/a is allowed, whether or not optional
is allowed, and what table to use for validation otherwise.
The trigger reads the control table for each column/question
and validates.  The trigger uses plpgsql EXECUTE (or equivalent)
to dynamically look up the data value in the appropriate table.

The second approach is to hardcode the trigger.  I'd use m4
as a pre-processor as your code will be very repetitious.


Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein