Re: Multiple parent design

"T" <anyone_at_anywhere.com> wrote in message news:<av0gc.13656$k05.490_at_newsread2.news.pas.earthlink.net>...
> 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.

Not exactly true: you would be prevented from deleting the master Parent row, but not from deleting additional information "about" the Parent held in ParentN.

If the DBMS supported SQL assertions you could also constrain that a Parent of type N must have an associated ParentN row, or alternatively simply that a Parent must also have a Parent1 or a Parent2 ... or a ParentN row. However, since AFAIK no commercial DBMS implements assertions, this is perhaps a moot point.

> 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?

Maybe someone will suggest one, but the type of design discussed above is very common for representing supertypes and subtypes in a SQL database, and is considered correct for the relational model (see Fabian Pascal's book Practical Issues in Database Management). You can make each pair of tables look like one table using a view, and it may even be possible to enable DML on those views (e.g. using Oracle's INSTEAD OF triggers). Received on Sat Apr 17 2004 - 12:09:16 CEST