Two ways to define may-have-a (1..0-1) relationship

Question

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 ..).

1. The table A has a column which can be either a number OR NULL. The number links to the primary key of B.

2. 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:

1. may-have-a1
2. may-have-a2

Is there any way to avoid this trickery?

Answer 1

Your second design (2) is truly a 1 - 0..1 relationship.

The first design (1) is a mash-up, more like a 0..1 - 0..1 relationship (assuming this "number or null" column has a Unique constraint, too. If not, it's 0..n - 0..1.).

Answer 2

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.

Answer 3

Both ways can be valid depending on the scenario.

Method 2 is the more standard, common way. It is a 1-N relationship only N is either 0 or 1. FK columns are by default indexed in most databases and go on the table on the N side (just like you describe with your unique index only an additional FK constraint fails if you insert an FK-column-value which does not match any PKs in A).

Method 1 follows the same structure as an N-1 relationship (FK column is on the N side). It can be done for example in hibernate using a ManyToOne relationship where optional=true. It is an N-to-0/1 relationship where N happens to be always 1.

Method 1 can make complete sense. Say A is "Client" and B is "SalesContact". Client will only have 0 or 1 SalesContact. Perhaps each SalesContact is only assigned to 1 Client. But we want to support SalesContact being assigned to multiple Clients in the future.

Method 2 can make complete sense. Say you have "Client" and "Address". An Client can have 0 or 1 Address. But in the future, we want to support clients having multiple addresses.

But if you know you will always have 1-to-0/1 and will never want 1-N or N-1 in the future, you can choose either way. Method 2 is probably a better choice.

Most ORMs do not have a 1-to-0/1 construct specifically, just 1-N, N-1 and maybe 1-1. In hibernate, the 1 side of 1-N/N-1 can be 0 for optional=true.

Further note: I am currently considering Method 1 for efficiency reasons. But I notice at least in mysql that SELECT * WHERE key = NULL (not IS NULL, outright null-lookup-in-index similar to key = :inserted_value with inserted_value as null), EXPLAIN is indicating an index lookup anyways! You might think that if the key NULL is being looked up in the index, the db would just skip the lookup and return nothing (maybe it still does that, I'm not sure). Not only that but when I log generated SQL from hibernate, using ManyToOne + optional=true + Eager it always does a left join - when I look up either 1 or multiple A's. When I switch to optional=false (N-1 not N-to-0/1), it changes the left join to an inner join. So in my case, OneToMany and ManyToOne are both table joins. It is possible that the db engine is still faster on ManyToOne but only if it starts with the A table and is smart enough to skip the null lookup on B. Note that whether the db engine starts with A or B is up to the db engine query optimization logic but for certain queries it will always choose A.

Answer 4

Usually when you have a 0-1 to 1 relationship, you do one of two things:

1. Put the 0-1 fields in the 1 table and allow them to be nulls. This is denormalized, but practical.
2. Create a second table for the 0-1 fields and use the exact same primary key as the 1 table. This is normalized, but complex.

I can't envision a situation where you'd need to create a key for the 0-1 table that you'd then store in the 1 table but allow to be null. That seems like just the worst of both worlds.

---

Generally the second option is preferable because deletion from B doesn't require updates of A.