Re: foreign key references two different table?

[Quoted] "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? Received on Sat May 31 2003 - 02:15:25 CEST