Using Composite Keys To Define "Dynamic" Relationship Types

Question

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?

Answer 1

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.

• Marcel Marino: Thank you for your response. I'm the original poster, I thought I was signed in but I was mistaken. I'll be honest, what you proposed was my first attempt to solve the problem. One of our DBAs is insisting this "isn't standard" and "not part of any database theory". He also insists that this will be inefficient for a large environment as it will frequently require the entity table to be locked, which is why I switched over to the composite key method. Could you speak to these concerns in any way?
• RDFozz: First, as someone who's been working with databases for almost 30 years: "isn't standard" is something worth noting as a concern - but should not stop you from proceeding. "Not part of any database theory" isn't an argument I'd ever use, under any circumstance. Now - Frequent locking of the entity table is potentially a matter of concern. However, beyond an initial population, I suspect that it will be used more in reads than in writes - in which case, locks would usually be able to be shared amongst readers, and wouldn't be a huge problem.
• Marcel Marino: @RDFozz Would you say the proposed solution is or is not standard?
• RDFozz: I believe I've seen similar things, though I can recall having implemented any. My concern for your original solution is that the foreign key relationship cannot be maintained using the built-in FOREIGN KEY mechanisms. Since both of the identifying keys could belong to multiple tables, everything has to be maintained via constraints (or, more likely, triggers). Which is entirely doable - but is not necessarily easy, and could have issues that aren't easy to notice until it's too late.
• RDFozz: This solution avoids that by having everything in one table, but, there will presumably still be owner, pet, vet, etc. tables, as the fields needed for a person would be notably different than those for a pet. Queries become more complicated by needing to join across more tables - but that's not necessarily an issue. I would have explicit person_id, pet_id, vet_id, etc. columns in entity (or you're back to maintain some foreign keys "manually"); but, this can actually work as a substitute for the entity_type value (or a means to compute it).