Re: foreign key references two different table?

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Sat, 31 May 2003 09:59:54 -0700
Message-ID: <3ED8DF8A.37C8C131_at_exxesolutions.com>


Mikito Harakiri wrote:

> "Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message
> news:3ED7E6B0.FFB5FB89_at_exxesolutions.com...
> > Well if you want to bring a view into it the solution could be written
> using
> > an INSTEAD OF trigger.
>
> Declarative constraints are superior to triggers. There are maybe a dozen
> developers in the world who could implement foreign key constraint
> correctly. (It is not as simple as it sounds because you have to lock tables
> properly). I would bet that some those developers are employed by oracle,
> rather than pretend that anybody my organization would be capable writing
> such a trigger on a short notice.
>
> > But it is still a bad design.
>
> That request is common when one wants to model subclassing. For example,
> there is base class People, and 2 subclasses Customers and Salesmen. If 2
> subclasses -- Customers and Salesmen -- don't have anything in common, then
> the People table is redundant. However, some other table can refer to People
> and the only way to implement it without introducting People table is a
> foreign key constraint to a view.
>
> Even oracle Apps faced that problem. In a new "TCA architecture" (if I can
> call 5 year old idea "new":-) they have a table hz_parties that encompasses
> many disjoint entities. Essentially, there is nothing in that table except
> id and party type. Not even name, because for organizations they have 1
> name, but for people they have 2! Is it superior design in your opinion?

You simultaneously over-estimate the abilities of some of those at Oracle and vastly underestimate the abilities of many in the developer community.

And yes declarative constraints are superior. But there is a caveat. That caveat being that they must be functional. To implement a delarative foreign key to two tables is not possilbe therefore a trigger is a reasonable solution.

I have little use for purists except when teaching is an academic exercise. A design need not be superior based upon some theoretical, academic, solution. In the real world is needs to be functional and maintainable. Nothing more.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Sat May 31 2003 - 18:59:54 CEST

Original text of this message