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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nancy - 14 Feb 2004 20:06 GMT

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

Reply to this Message

Bob Badour - 14 Feb 2004 21:38 GMT

> What's the best approach to the scenario below.
>
[quoted text clipped - 40 lines]
>
> Nancy

Have you considered whether you might have the child and parent reversed?

Reply to this Message

Nancy - 15 Feb 2004 02:47 GMT

> > What's the best approach to the scenario below.
> >
[quoted text clipped - 42 lines]
>
> 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.

Reply to this Message

Bob Badour - 15 Feb 2004 03:52 GMT

> > > What's the best approach to the scenario below.
> > >
[quoted text clipped - 46 lines]
> 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?

Reply to this Message

Nancy - 16 Feb 2004 21:32 GMT

> > "Bob Badour" <bbadour@golden.net> wrote in message
> news:<A5KdnS26YYsHCLPdRVn-hg@golden.net>...
[quoted text clipped - 52 lines]
> 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...

Reply to this Message

Dawn M. Wolthuis - 15 Feb 2004 23:33 GMT

If you were to have documents related to these entities that you placed in
web pages (such as pages for the parents with links to children and vice
versa), then what would that web of data look like? Where documents would
be linked? Alternatively, if these records were filed in a manual file
system, what file folders would you have and what "See folder xyz" links
would be written to cross-reference documents?

Model the data the way you are inclined to think about the information (as
everyone did intuitively BEFORE there was a notion of attempting to flatten
and then piece back together the language/data).. This will typically yield
graphs rather than flat relations. If you have the freedom to implement the
data in a system that permits modeling data as graphs (at least in trees),
then I would suggest doing so. If you are saddled with an RDBMS or SQL-92
compliance requirement, then look at the root of each graph -- what are the
"home pages" or "top level file folders" labeled? These would likely map to
your primary entities/tables/relations. When normalizing the rest of the
data, link it back (eventually, at least) to its root node.

Where you have 1-M or M-1 relationships, in an RDBMS implementors typically
place a foreign key in the table on the M side, since that yields a column
that is single-valued. In the case of M-M, a link table (or relationshp
table) is a common strategy.

I haven't caught quite enough clues, but the way you have described this, it
sounds something like the old (and I'll date myself here) "M card" scenario,
where a transaction file has transactions that apply to a Master file
identified with an "M" in card column 1 (for example), while some other
"card type" applys to another type of transaction that maps to another
"parent" file. In that case, your "card deck" could have either of the
options you describe, but if it is important to have 5 separate parents,
then making an additional 5 relationship/link tables might be in order.
Tables: ParentA,B,C,D,E Child,
RelationshipA-Child,B-Child,C-Child,D-Child,E-Child.

In the IBM U2 database (PICK), I'd model it with links and return links (as
one would on the web)

ParentA
MyChildren: MultiValued field

ParentB
MyChildren: MV

...
Child:
ParentType: (A-E)
ForeignKeyToParent: <string>

This is similar to your Option2. --dawn

--dawn

Reply to this Message

Jonathan Leffler - 15 Feb 2004 06:18 GMT

> What's the best approach to the scenario below.
>
[quoted text clipped - 33 lines]
> ParentIndicator Char(1) not null
> Attribute1

Bob is asking you questions - I am too.

Can you illustrate your 5 parent types? Or even a 2 parent case? Can
you give a concrete example?

Your child table will have at most one of the N parent types
associated with it - that is necessarily the case if option 2 is
viable (and I'm understanding the situation correctly).

My suspicion is that you should be using a common supertype for the N
parent types - they are all related, and have at least some attributes
in common with each other (again, this seems pretty plausible since
the same ParentRecordId column can reference any of the parent tables
in option 2). The five sub-types would then be represented in
sub-tables containing the specialized information. The child table to
which you are referring would now have a regular foreign key to the
single supertype table, from which you can deduce which sub-type applies.

These are entity supertypes and subtypes; I'm not playing with
inheritance per se here.

Signature

Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Reply to this Message

Neo - 15 Feb 2004 16:08 GMT

> There are multiple parent tables (let's say [upto] 5) that require a
> relatiionship to a single set of attributes ([0,] 1 to many).
> How would you handle this?

Because your cookie isn't retangular, when you try to corner one with
an RDM cookie cutter, you're either gonna cut of the ginger man's arm
or there's gonna be some space leftover to party with NULLs :)

Alternatively, there is a small experimental db that allows each thing
to have a variable number of parents and attributes. See
www.xdb1.com/Example/Ex102.asp where persons related to a baseball
team have different roles and attributes.

Reply to this Message

Tony - 17 Feb 2004 10:46 GMT

> What's the best approach to the scenario below.
>
[quoted text clipped - 40 lines]
>
> Nancy

Option 1 sounds like what in Oracle ERD terminology is called an "arc"
relationship. An example that comes to mind from experience is that
of a data model for addresses, where there are many different entities
that may be associated with one or more addresses like this:

create table person( person_id primary key, person_name, ... );
create table organisation( org_id primary key, org_name, ... );
create table property( prop_id primary key, prop_name, ... );
...

create table address( address_id primary key, address_line_1, ... );

create table address_usage( au_id primary key, address_id references
address, address_usage_type, person_id references person, org_id
references organisation, prop_id references property, ... );

i.e. an address_usage links an address to a person OR an organisation
OR a property OR ... (mutually exclusively).

A CHECK constraint is required to enforce the mutual exclusivity, e.g.

alter table address_usage add constraint x check
( (person_id is not null and org_id is null and prop_id is null)
or (person_id is null and org_id is not null and prop_id is null)
or (person_id is null and org_id is null and prop_id is not null)
);

Option 2 isn't desirable, because the "foreign key" cannot be enforced
via foreign key constraints.

An alternative solution (option 3?) is to treat person, organisation,
property, ... as entity subtypes of a supertype with some suitable
name, e.g. addressable_entity (I'm not saying that is a good name!):

create table addressable_entity( entity_id primary key, entity_type,
<any other common attributes>... );
create table person( entity_id references addressable_entity primary
key, person_name, ... );
create table organisation( entity_id references addressable_entity
primary key, org_name, ... );
create table property( entity_id references addressable_entity primary
key, prop_name, ... );
...

create table address( address_id primary key, address_line_1, ... );

create table address_usage( au_id primary key, address_id references
address, address_usage_type, entity_id references addressable_entity
);

Reply to this Message

Nancy - 18 Feb 2004 17:40 GMT

> Option 1 sounds like what in Oracle ERD terminology is called an "arc"
> relationship. An example that comes to mind from experience is that
[quoted text clipped - 45 lines]
> address, address_usage_type, entity_id references addressable_entity
> );

Your example is exactly what I'm dealing with - thanks for spelling it
out. Actually, the 5 parents I referred to are subtypes of a
supertype which I didn't include in the example in my feeble attempts
to simplify the problem!

So the "arc" relationship is the approach I prefer to take with the
constraint you outlined, but I'm not sure if doable in Sybase 12.5.

Your option 3 is interesting. I think the other gent recommended
something similar, except to have the table address_usage reference
the subtype rather that the supertype. That would mean a table for
every subtype referencing address -- this would then provide a join
between the subtypes (org, prop, person) and address.

Nancy

Reply to this Message