Database Design – Using Composite Keys for Dynamic Relationship Types

database-design

The scenario

I'm working on a project that has pretty heavy relationship requirements. We have two competing ideas on how to approach the database design, and we're split on which one to use.

We have two types of entities, pet and adult. We are recording the common columns in a "master" table called Organism, and the columns only applicable to pets and adults are in their respective tables, Pet and Adult.

The issue is how to relate them, as well as defining relationship type. If it was just a matter of relating pet to adults, this would be simple: a "bridge" table between the two. The complexity comes in where we have to also relate pet to pet and adult to adult. We might need to say one adult is the spouse to another adult, or the pet sitter to another adult. We may need to say two pets live together, or don't live together but are blood related. We may need to later add other tables like Vet where we relate both Pet and Adult tables to as well.

Idea no. 1

So the first idea is that for each type of relationship we add in a "bridge" table, so one between Pet and Adult, and to relate Pet to Pet we would have a "bridge" table that relates Pet to Organism, and another "bridge" table to relate Adult to Organism.

If we later added Vet, we would need to add a "bridge" table between it and Adult as well as between Vet and Pet, and continue adding more "bridge" tables for each new relationship.

Idea no. 2

The other idea is to create a "dynamic" relationship table that has composite keys for both primary and foreign keys, so:

PrimaryID    PrimarySource  Relationship  ForeignID    ForeignSource
---------    -------------  ------------  ---------    -------------
1            Adult          Pet Sitter    4            Pet
2            Adult          Spouse        10           Adult
42           Pet            Blood Sibling 76           Pet

The "source" columns determine what tables the relationships are from, and can scale up so that any additional relationships are easy to make.

Considerations

The first option from what I'm told is more standard but the people against the second method can't seem to come up with any practical reason why this design would fail. I am interested in the practical application here, and what issues could arise in the two systems, if any. Is there a clear right answer here, and if so, what is it?

Best Answer

The relationships can be simplified as

Entity <---Realtionship---> Entity

Like this

create table entity (id, entity_type /** etc **/)
create table relation (id, from_id, to_id, relation_type_id)
create table relation_type (id, name)

Pet/owner would then be

Entity(1, 'Dog') 
Entity(2, 'Person')
Relation(n, 1, 2, 'Owner')

This concept also accomodates shared ownership, i.e. a dog owned by two people:

create table relation(id, from_id, to_id, relation_type_id, share)
Entity(3, 'Person')
Relation(n, 1, 2, 'Owner', 50.0)
Relation(n, 1, 3, 'Owner', 50.0)

You should of course create constrains/checks to ensure that share is always 100 etc.

If you need to enforce business rules, for example A pet can only be owned by a person, and a person can not be owned by anybody

create table allowed_relations(from_entity_type_id, to_entity_type_id, type_id)

In order to enforce this you will need to create a trigger on the relation table and check if the proposed relation matches the allowed_relations table. If the number of business rules is small (and seldom changes), the rules can be hardcoded as a check constraint on the relation table.

Related Solutions

Name for this “restricted one to many” relationship? Can it be enforced in the DB

I don't know that there's an official or technical name for the relationship you're describing (I also don't know if you'll find many pet lovers who would like to be restricted to one dog, one cat, etc.), but here is how I would probably approach this:

CREATE TABLE dbo.People
(
  PersonID INT PRIMARY KEY
  --, ... name, etc.
);

CREATE TABLE dbo.PetTypes
(
  PetTypeID INT PRIMARY KEY
  --, ... species name, etc.
);

CREATE TABLE dbo.PeoplePets
(
  PersonID INT, -- FK to dbo.People
  PetTypeID INT, -- FK to dbo.PetTypes
  --, ... pet name etc.
  PRIMARY KEY (PersonID, PetTypeID)
);

Now whether this is simple enough, I'm not sure. You might have different attributes for different types of pets, and so you may need to extend this, but I think in general the concept is sound.

Database Design – Relate Two Tables to a Third Table

Here is a suggestion so you can enforce the constraints you want declaratively. (I've simplified the table names a bit, removed the bridge_ prefix.)

We remove footnote_num from:

Table:      a_ref         -- was named:  bridge_a_reference
     a_id, 
     ref_id
Primary key: 
     (a_id, ref_id)
Foreign keys:
     a_id  -> a
     ref_id -> reference

We add this table - which will basically store only those rows from a_ref with footnote, those you want to add children into the b_ref:

Table:      a_ref_with_footnote 
    a_id, 
    ref_id,
    footnote_num
Primary key: 
    (a_id, ref_id)
Unique key:
    (a_id, footnote_num)
Foreign keys: 
    (a_id, ref_id)  -> a_ref

And finally the 3rd table stays as in your design except the foreign keys which now reference the intermediate table (a_ref_with_footnote):

 Table:     b_ref         -- was named: bridge_b_reference:
     a_id, 
     b_id, 
     ref_id,
 Primary key: 
     (b_id, ref_id)
 Foreign keys: 
     (a_id, b_id) -> b
     (a_id, ref_id) -> a_ref_with_footnote