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

"Bob Badour" <bbadour_at_golden.net> wrote in message news:<A5KdnS26YYsHCLPdRVn-hg_at_golden.net>...
> "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?

I was wrong in stating the relationship was a 1 to many -- it's actually 1 to 0 or more. Many records in the parent will not have a child record, and some will primarily have 1, others more than 1. Received on Sun Feb 15 2004 - 03:47:54 CET