Today I have discovered for myself that may-have-a, one-to-zero-or-one, (1-0..1) relationship can be implemented in two different ways:
Let A be the "main" table (left side of ..) and B be the "linked" table (right side of ..).
-
The table A has a column which can be either a number OR NULL. The number links to the primary key of B.
-
The table B has a UNIQUE column which links to a primary key in table A.
Mathematically these two ways to link tables are equivalent (they are both 1-0..1 that is may-have-a), but the structure of the tables is different for these two variants.
I am writing a lightweight ORM for our DB. (I know I should not, but so things work in our company.)
The bad thing is that we have may-have-a relationships in our database implemented sometimes in one way, sometimes in the other.
Should we restructure our DB to retain only one of these two relationships? Which of the two variants to retain?
As a temporary measurement, I propose to create in my ORM two relationships:
- may-have-a1
- may-have-a2
Is there any way to avoid this trickery?
Best Answer
You've got 2 conflicting design goals here. One is to design a good database. The other is to play nicely with OR mappers. You might be able to shift to a more consistent approach to mapping partial participation constraints, but you will continue to have design conflicts in the future.
One option would be to create a set of views to worm with your ORM, and so abstract the problem away.