I am doing up a doctor's office system for a friend so I am working on
this too. I haven't gotten as far as designing this part yet but from
other calendar systems I see requirements for
- Being able to alter all the recurring entries at once - say change
them all from 1:00pm to 1:15
- Being able to alter specfic entries without impacting the others -
say change next week's to 1:15 but leave all the others at 1:00.
This much has lead me so far to
1) Create a 'Repeating Appointment' entry and place in the
RepeatingAppointmentTable
- If we only generate and store the individual appointment entries
then we create an amount of extra work for ourselves
if we ever want to do a mass change. basically we'll need to
recreate this entry so we may as well store it now.
2) Create enties for each appointment instance and place in the
appointment table, FK to the recurring appointment
3) If we want to modify an individual entry then we need to update the
appointment instance
- here is one reason why I prefer the pre-create rather than
create on the fly. In the on the fly approach, if we modify a specific
appointment instance then when we generate all the appointment
instances from the recurring appointment we'll need to subtract this
'real' appointment. It gets even worse if we delete a specfic
appointment because we now need a placeholder to show a deleted
appointment to be subtracted from the recurring appointment.
4) if we want to do a mass update, some properties we will want to
change on everything, even the modified appointments (say a location or
a doctor / trainer) others we shall want to leave as modified. e.g. If
we want to change the time for all of them but have already moved the
appointment for 10/12 back half an hour because the client/patient can
only make it at that time, well then we'll want to skip that one but
modify all the others.
- one may want to offer an option to update the already modified
entries on a case by case basis.
Summary
2 tables
RecurringAppointment - details of the recurring appointments
Appointment - Holds all the appointment details, a modified flag and a
FK to the RecurringAppointment table
Modified is an optimization idea and may not be
neccessary (or even a good idea - still whiteboarding all this)
When a Recurring Appointment is created
Add one entry recurring and n entries to appointment
Modify an instance
Modify entry in appoinrment, update the modified flag
Modify all
Modify all entries with the specific FK - don't update modified
flag
for each appointment with the modified flag set, query whether to
update the value. Offer a yes/no to all option
Delete Instance
delete entry from appointment table
Delete all
Delete entry from recurring
Delete all entries (including modified) from appointments (or offer
choice for each modified - depends on reqs)
As I said, I am working on the same type of problem so I would be very
interested in comments on the above and/or any suggestions people may
have.
Thanks,
Alan.
