Re: Foreign key to a view (UNION of two or more tables),
От | Karl O. Pinc |
---|---|
Тема | Re: Foreign key to a view (UNION of two or more tables), |
Дата | |
Msg-id | 1119213356l.16631l.0l@mofo обсуждение исходный текст |
Ответ на | Re: Foreign key to a view (UNION of two or more tables), any alternative? (Jose Gonzalez Gomez <jgonzalez.openinput@gmail.com>) |
Ответы |
Re: Foreign key to a view (UNION of two or more tables), any alternative?
Re: Foreign key to a view (UNION of two or more tables), |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: