Re: Multiple parent design

Mr. Celko,

In a general sense, your solution does appear to solve the problem. However, relation DBMS's (perhaps even relation design) do not provide for this situation because it would require that each Child instance's parentId exist in *all* tables Parent1...ParentN. I'd rather like to have the ability to say that each Child instance must be associated with one and only one Parent instance stored in tables Parent1...ParentN, but that Parent instance could be in any table Parent1...ParentN. Further, I'd like to prevent the deletion a Parent instance from any table Parent1...ParentN if there exists an associated Child instance.

Tony is correct in stating that this is common in subtype-supertype situations which is exactly I'm trying to manage. However, it would also arrise in a partitioned environment where tables Parent1...ParentN might be partitioned for performance reasons. For the purpose of this discussion, a solution that provides for each Parent1...ParentN being a different entity is more useful because it would also provide for a parittioned situation where each Parent1...ParentN is the same entity simply stored in separate tables.

Thomas

"--CELKO--" <joe.celko_at_northface.edu> wrote in message news:a264e7ea.0404170651.4e38f00b_at_posting.google.com...
> Is this close to what you want?
>
> CREATE TABLE Parent1
> (parent_id INTEGER NOT NULL,
> parent_type INTEGER DEFAULT 1 NOT NULL
> CHECK(parent_type = 1),
> PRIMARY KEY (parent_id, parent_type)
> FOREIGN KEY (parent_id, parent_type)
> REFERENCES Children (parent_id, parent_type),
> ..);
>
> CREATE TABLE Parent2
> (parent_id INTEGER NOT NULL,
> parent_type INTEGER DEFAULT 2 NOT NULL
> CHECK(parent_type = 2),
> PRIMARY KEY (parent_id, parent_type),
> FOREIGN KEY (parent_id, parent_type)
> REFERENCES Children (parent_id, parent_type),
> ..);
>
> Etc.
>
> CREATE TABLE Children
> (child_id INTEGER NOT NULL PRIMARY KEY,
> parent_id INTEGER NOT NULL,
> parent_type INTEGER NOT NULL
> CHECK(parent_type IN (1,2, ..)),
> UNIQUE(parent_id, parent_type),
> ..);
>
> This requires that each child has one and only one parent in one and
> only one parent table. No child can be deleted while he has a parent.
Received on Sat Apr 17 2004 - 20:03:30 CEST