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

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

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 Received on Wed Feb 18 2004 - 18:40:13 CET