Imagine the following situation:
> A person has a passport. > A person *owns only* one passport. > One passport can only *be owned by a single* person.
This is a clear case of a one-to-one relationship. For the sake of simplicity let us imagine that a person only has a name and the passport only has a nationality. What is bothering me the most is that it seems that everyone is doing this differently. From what I can tell there are four strategies that can be taken to map this relationship:
- Everything on the same table:
- A foreign key on the owner side that references the owned side.
- Same value for primary keys on both tables.
- A foreign key on the owned side with a unique key on top:
- Number 1 is pretty self explanatory and I do not see any problem when the entities are small, but if they have a lot of fields we will have a gigantic table.
- Number 2 seems fine and this is the way I have seen most people and frameworks do it (e.g. Entity Framework, Hibernate) and it is also the way I was taught in school. The big problem here is that, because of referential integrity, our deletion logic will be upside down. I should be able to delete a passport without any problem, but in this case I will not be able to delete it without deleting the person as well, which does not make any sense.
- Number 3 and 4 seem pretty much identical. With the foreign key on the passport side, I am able to delete passports without deleting people, which makes sense. If I remove a person, I should have to remove their passport. The main advantage that I see for using option number 4 instead of number 3 or all the others, is that if for ever reason I decide that I now want a user to have multiple passports I only have to remove the unique key constraint, which is incredibly easy and a lot less hassle than changing the keys from both tables.
So my question basically is reduced to the following: With so many advantages with number 4 why do people keep using the other strategies? My biggest grip is with ORMs such as Hibernate which, in my opinion, do things the opposite way. The owning side of the relation tracked by Hibernate is the side of the relation that owns the foreign key in the database. So if I tried to do this in Java with Hibernate my relationship would be swapped. If I included the foreign key to the passport on the person table like it wants me too, it would ruin the deletion logic like I have explained before. I have the impression that EF also works this way. So, with all of this, why do people keep preferring the "disadvantageous" approaches?