Re: Multiple parent design

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

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