Multiple parent design
Date: Fri, 16 Apr 2004 04:49:01 GMT
Message-ID: <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.
Thanks
Thomas Received on Fri Apr 16 2004 - 06:49:01 CEST