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,
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
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
Adult, and to relate
Pet we would have a "bridge" table that relates
Organism, and another "bridge" table to relate
If we later added
Vet, we would need to add a "bridge" table between it and
Adult as well as between
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.
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?