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

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

Is there another option, or a variation of the above? How would you handle this? If there's already a thread, please point me to it.

Thank-you.

Nancy Received on Sat Feb 14 2004 - 21:06:37 CET