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

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, ... );

(  (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)

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 - 11:46:33 CET