Re: Foreign key to a view (UNION of two or more tables), any alternative? - Mailing list pgsql-general

On 6/19/05, Karl O. Pinc <kop@meme.com> wrote:
>
> 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.
>

Thanks a lot for your suggestion. I like its dynamic nature, and I had
planned to have something like that raising a bit the level of
abstraction, but for a future project (they do a lot of questionnaires
here :o) )

I've been thinking about the problem and I finally opted for another
solution: I'm going to separate this data in two columns: answer and
reason for unavailable answer. This would double the number of columns
for questions where optional/ N/A allowed, but this way I may keep
data integrity using a combination of referential integrity
constraints (valid codes), and check constraints (only one of those
two columns with a value other than null); this way I also avoid
writing triggers that should be translated in the case of an
hypotetical DBMS change. And I also have the feeling that this is a
more correct design from a conceptual point of view. I'll solve the
issue of having directly usable data for statistics (just one column
per question) using a view.

If anyone thinks there's a better approach, I'd be glad to hear...

Thanks again, best regards
Jose