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

From: Tony <andrewst_at_onetel.net.uk>
Date: 17 Feb 2004 02:46:33 -0800
Message-ID: <c0e3f26e.0402170246.6978e54d@posting.google.com>

nbrophy_at_ucsd.edu (Nancy) 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

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 ); Received on Tue Feb 17 2004 - 04:46:33 CST