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:
slot_id
. It did not.
This was on apointments.ticket_id
. Again it does
nt. It was on apponitments.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:
ticket_id = 1
{ticket_id=1, slot_id=1}
, {ticket_id=1,
slot_id=2}
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.