Using Composite Keys To Define "Dynamic" Relationship Types 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 &quot;master&quot; 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 &quot;bridge&quot; 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 &quot;bridge&quot; table, so one between Pet and Adult, and to relate Pet to Pet we would have a &quot;bridge&quot; table that relates Pet to Organism, and another &quot;bridge&quot; table to relate Adult to Organism.
If we later added Vet, we would need to add a &quot;bridge&quot; table between it and Adult as well as between Vet and Pet, and continue adding more &quot;bridge&quot; tables for each new relationship.
Idea no. 2
The other idea is to create a &quot;dynamic&quot; 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 &quot;source&quot; 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?
The relationships can be simplified as

Entity &lt;---Realtionship---&gt; 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.