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

"Nancy" <nbrophy_at_ucsd.edu> wrote in message news:6d7a27f7.0402141206.7971f171_at_posting.google.com...
> 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
>
> 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

Have you considered whether you might have the child and parent reversed? Received on Sat Feb 14 2004 - 22:38:48 CET