Assume you have a system in which a patient can schedule appointments with a given doctor. In this system, aside from being able to create an appointment, the both the doctor and the patient should be able to modify a created appointment. This includes time changes and cancellations. In the system, cancellations are seen as one-way deactivations. Anyway, here’s an example of a simple (working?) way, and a better way.
In the first try, I had appointments referring to an availability. An appointment can’t really exist otherwise. This easily satisfies the requirement that only one person is ever scheduled for a given time. During a cancellation, the appointment that referred to the availability is marked as deactivated. In the simple design, the appointment must loose its hold/reference to the availability.
Unseen side affect – now, without having to dig through audit tables (we don’t want to have to do this), how can we now reference the availability that the appointment originally referenced? Holding the ID of the original appointment is a possibility, but not really enough. Making the relationship an aggregate and giving the appointment a Boolean marker for the “current appointment” sounded nice, but not enough. It’s possible that 2 or more appointments were made and canceled for the same availability.
At first, I thought of an association class between the availability and the appointment, but that was more than needed. I don’t need state on the association. All the required state, including audit info, such as last updated by and time, is already on the involved entities. Finally, I thought, “Add an numeric index”. This resolved a number of requirements, and allowed me to create a unique index on the relationship. Nothing a little caffeine and time couldn’t handle…
