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

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

In which parent? You claim to have 5 parents and then you use the definite article as if there were only one.

What would it mean if all of the FK's were NULL? What would it mean for a child to have two or more parents?

Have you considered one table for the parent, one table for the child and five other join tables to establish the relationships between them? Received on Sun Feb 15 2004 - 04:52:08 CET