Re: Multiple parent design

Perhaps the design question got lost in the specifics. Let me state the question in a more generic manner.

Suppose we have entities 1 through N stored in tables Parent1...ParentN. Now suppose we have a Child entity where each Child instance can be associated with one and only one Parent instance that may be located in any one of the tables Parent1 to ParentN.

I'd like to hear any ideas on implementing a design that would prevent the deletion of a Parent instance if for that instance there existed any Child instances to which it was associated.

I know of two solutions:

1. Use triggers
2. Use a master "Parents" table that stores all primary key values from all tables Parent1...ParentN. Create a foreign key from this new master "Parents" table to each of the tables Parent1...ParentN as well as to the Child table. This solution has a couple of downsides. Firstly, on the surface it does not prevent the deletion of a Parent instance if it has Child instances associated with it. One would have to use triggers or business layer components to manage this. Secondly, each addition of a Parent instance requires entry into two tables in sequence (First to the master Parents table then to the specific ParentN table). Neither of these downsides are overwhelming of course, but it'd be nice to know if there is a better solution or established design concepts realting to multiple parents. In essence, the "Parents" table would act as Laconic2's "Subscriber" table.

Is there is a more elegent way of solving this problem?

Thomas

"Laconic2" <laconic2_at_comcast.net> wrote in message news:vu2dnTRAvoIYYuLdRVn-gg_at_comcast.com...
> OK, let's try another approach.
>
> Every in service phone line has a subscriber.
> Every subscriber is either a person or a company.
> Every person that has a phone line is a subscriber.
> Every company that has a phone line is a subscriber.
>
> Can't we start from these assertions, and build the tables from there?
>
>
Received on Fri Apr 16 2004 - 21:19:50 CDT