Has one and many2many etc relationships are about possibility not reality

This is part of the Semicolon&Sons Code Diary - consisting of lessons learned on the job. You're in the data category.

Last Updated: 2024-10-12

I had a "ticket" entity, which was connected to an "appointment" which in turn was connected to a "slot" entity.

The appointments table, among other things, contained these fields:

ticket_id
slot_id

(implying that an appointment belonged to both a ticket and a slot)

In business logic parlance, it was "obvious" to me that a ticket could only have one slot, so when I wanted to get a ticket's related slot, I thought it must be a has_one or belongs_to relationship. But these didn't work.

Why? When you look at the table structure it seems obvious:

In truth, the system was a many-to-many relationship, using the appointments table to pivot. The business rules of "1 ticket gives 1 slot" only happened due to data integrity constraints (i.e. that ticket_id must be unique in the appointments table)

And with this DB schema (leaving aside data integrity constraints), it was possible for a slot to have multiple tickets:

Aside: Part of what's confusing is that the appointments table had many non-pivot columns - i.e. it was more than a simple pivot table.