In the simplified database schema depicted below, I'm considering denormalizing by adding a short-circuit key (EventId, relation shown in red) on Appointment.
One advantage is not needing a join to access appointments data by event (the usual way it will be queried). An additional advantage involves constraints. I'd like to allow only one appointment per person per event. The short-circuit key makes it easy to add a unique constraint on PersonId+EventId on the Appointment table, whereas it's unclear how I'd achieve this constraint at the database level otherwise.
At the business level, timeslots are invariant once created, and appointment editing is already restricted to changing the timeslot within the same event, so I don't see much cost to keeping the data consistent here.
It also appeals to me intuitively, as I think of both the appointments and the timeslots as belonging to the event.
Thoughts on this denormalization? Does it seem reasonable?
Best Answer
In an RDBMS context, the first question is: why do you want to avoid the extra join? RDBMSes are designed to optimize such queries; do you experience any tangible performance issue? If it is for programming convenience only, you could very well create a view to automate the shortcut without real redundancy.
Looking at your design and requirements, it seems that a
Timeslot
is a component of anEvent
(i.e. in the sense of UML composition): the slot makes no sense without the event, and probably, if an event would be deleted, so would be all its slots. If this is true:Timeslot
made ofEventId
and a sequence number of the slot in the event.Appointment
would then be replaced by a pairEventId
,Sequence
Event
is the root of an aggregate that includes alsoTimeslot
; and access to an aggregate's entities shall always go via the aggregate root.If at this stage, you still think you need to denormalize, then evaluate the pros and cons carefully:
But ultimately, if the pros exceed the cons, this specific shortcut, does not seem intrinsically unreasonable.