Re: Foreign key(s) in a single child table relating to multiple parents

Nancy wrote:

> What's the best approach to the scenario below.
>
> There are multiple parent tables (let's say 5) that require a
> relatiionship to a single set of attributes (1 to many). From a
> database maintenance point of view, the preference is to have a single
> child table that relates to all of the parents. Outside of creating
> separate child tables, I can think of two other approaches below:
>
> Option 1:
>
> In the child table, have 5 FKs. This will result in every record
> having 4 of the FKs with null values (unless there's a dummy record in
> each parent, which I'd like to avoid).
>
> ChildTable:
> ChildRecordId PK
> ParentRecord1Id FK null
> ParentRecord2Id FK null
> ParentRecord3Id FK null
> ParentRecord4Id FK null
> ParentRecord5Id FK null
>
> Option 2:
>
> In the child table, have 1 FK that relates to each parent table (the
> PK datatypes in the parents are the same as we use record
> identifiers), and an additional field identifying which parent it
> relates to. This will require that the join between the parent and
> the child to include the criteria of which parent table you're
> relating it to since each parent's PK could potentially have the same
> value.
>
> ChildTable:
> ChildRecordId PK
> ParentRecordId FK not null
> ParentIndicator Char(1) not null
> Attribute1

Bob is asking you questions - I am too.

Can you illustrate your 5 parent types? Or even a 2 parent case? Can you give a concrete example?

Your child table will have at most one of the N parent types associated with it - that is necessarily the case if option 2 is viable (and I'm understanding the situation correctly).

My suspicion is that you should be using a common supertype for the N parent types - they are all related, and have at least some attributes in common with each other (again, this seems pretty plausible since the same ParentRecordId column can reference any of the parent tables in option 2). The five sub-types would then be represented in sub-tables containing the specialized information. The child table to which you are referring would now have a regular foreign key to the single supertype table, from which you can deduce which sub-type applies.

These are entity supertypes and subtypes; I'm not playing with inheritance per se here.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/