Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Multiple parent design

Multiple parent design

From: T <anyone_at_anywhere.com>
Date: Fri, 16 Apr 2004 04:49:01 GMT
Message-ID: <1BJfc.12399$k05.4404@newsread2.news.pas.earthlink.net>


I am trying to find a design that maintains referential integrity with multiple parent tables. For example, suppose we have the following:

Create Table Companies (Id uniqueidentifier PRIMARY KEY

, Name VarChar(100))

Create Table Persons (Id uniqueidentifier PRIMARY KEY

, Name VarChar(100))

Create Table Phones(Id uniqueidentifier PRIMARY KEY

            , ParentId uniqueidentifier

, Name VarChar(100))

In this scenario, the parentId of the Phones table can represent either a Company or a Person. Further, each Phone can be associated with one and only one parent (i.e. either a Company or a Person but not both.)

In this scenario, a standard foreign key relationship is not possible because the ParentId may come from different tables.

At the moment, I know of only two solutions:

  1.. Use triggers
  2.. Use a central "Parents" table that represents every possible parent in the universe. Add a foreign key from the "Parents" table to the Companies and Persons table. All entry would first have to go into the Parents table and then the respective table.

I'm not really keen on either of these solutions and was wondering if there was more elegant, more "hoyle" method.

Thanks

Thomas Received on Thu Apr 15 2004 - 23:49:01 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US