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

"Bob Badour" <bbadour_at_golden.net> wrote in message news:<8NKdnXGiptGHcLPdRVn-ug_at_golden.net>...
> "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.

I goofed - I meant parents.

> 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?

The child would relate to only one parent record in one of the 5 parent tables. So in option 1, that would mean only one of the FK's would have a value.

> Have you considered one table for the parent, one table for the child and
> five other join tables to establish the relationships between them?

Actually, I did. With this approach, are there potential problems in having orphan records in the child? Since the child record is meaningless without the parent relationship, I tend to avoid this approach, but sounds like I need to re-evaluate.

In your previous email's suggestion, I did think thru on having the parent containing the FK to the child if it's a 1:1 or 0:1, and not a 1:N or 0:N. But that would mean the parent FK could contain null values. Is that an acceptable practice if the FK in the parent is not part of an alternate or composite key? This would require an outer join, which I try to avoid.

Hope this makes sense... Received on Mon Feb 16 2004 - 22:32:40 CET