Re: Multiple parent design

From: Theo Peterbroers <>
Date: 16 Apr 2004 04:13:02 -0700
Message-ID: <>

"T" <> wrote in message news:<1BJfc.12399$>...
> 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.

Create two tables Companyphone and Personphone. Add check constraint "phone not in {the other table}". Create obvious foreign keys. Create view Allphone as Companyphone UNION Personphone. Received on Fri Apr 16 2004 - 13:13:02 CEST

Original text of this message