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?