|
Design Discussion
You're designing a database ... you need to store appointments (a "thing" that is scheduled to occur at a given datetime).
Some of these appointments may elect to recur based upon some interval (everyhour, everyday, everyweek, every 2 days, every 2 weeks, every whatever). Bastards.
Sometimes, those recurrences may elect to be cancelled or have the datetime altered, but the baseline recurrence should remain. Bastards!
What pointers could you offer?
Steve
Friday, January 30, 2004
char *pAppointment;
:)
Friday, January 30, 2004
http://www.martinfowler.com/apsupp/recurring.pdf
Martin Fowler Plugger
Friday, January 30, 2004
I had to design a system to do this once. I was given about a month to do everything (Requirements, DB Design, UI, etc.) so I did the simplest thing possible.
Here are the main fields from the "Appointments" table:
ScheduleID int
AppointmentID int IDENTITY
ApptStart datetime
ApptEnd datetime
Status int
SetDate datetime
SetBy int
Locked bit
... other fields specific to application...
ScheduleID is a foreign key to the "Schedules" table, Status is a foreign key to a lookup table of Statuses (0 = Available, 1 = Set, 2 = Cancelled, 3 = Expired, ...)
(a schedule is just a label for a set of appointments, and might have some other info about who can set appointments in that schedule, etc.)
SetDate is the date that the appointment was last set and SetBy is a User ID of the person who set the appointment.
When someone wants to create new, "available" appointments, your UI asks them "Is this a one time appointment or recurring". If it's one time, you create one record int the Appointments table.
If it's recurring, you collect the data that indicates the range/frequency of the recurrence and immediately create ALL of the available appointment records that satisfy that criteria. Then, you wait for people to set the appointments.
For ideas on how the UI should look for recurring data I looked at the Windows Task Scheduler and Outlook.
Another way would be to store the range/frequency data of recurring appointments instead of creating all of the appointment slots up front, but I found this to be too confusing and a waste of time.
Wayne
Friday, January 30, 2004
Oh yeah, and if you need some event to take place based on an appointment being set what I did was just make a Windows Service that wakes up every 1 minute and runs a query to check if any "Set" appointments time has come and processes them.
You can also make it "retro" process appointments that were Set but not processed when the service starts up, in case the Service or the DB died at any point.
Wayne
Friday, January 30, 2004
tblAppointments
sDescription - VarChar
dtScheduled - Date/Time
iRecurIntervalDays - Long
iRecurIntervalHours - Long
bRecurring - Boolean
bReccuranceCanceled - Boolean
Friday, January 30, 2004
You got some good comments here already.
A few things:
For repeating events, do NOT make multiple entries.
One record, with the start date, end date, and the time/occurrence is the way to go
Fire up Outlook , or even better use your palm pilot, and take a look at how the repeating appoints are done (way cool!).
If you try and modify a repeating appoint in palm (say, something really cool like the 3rd Thursday of every month), then you are prompted to apply the change to the current appointment, or “all”. Obviously if you hit all, then only the ONE appoint record in the database is modified. If you whack current, then obviously new record is crated in the appoint database
The beauty of storing start/end and the repeating factor means that you VERY few records in the database. And you can do cool things like that 3rd Thursdays of the month, but that falls on a different date every time.
It also enables you to set appointments or remember for things like daylight save time. In my palm, to setup a reminder for Spring daylight, it reads:
The last Sunday of every 12th month
The above text statement is what the palm spits out (cool eh!).
So, the setting was:
Every 12 months (note that the number 12, and the “months” can be changed to Day, Week, Month , Year.
Repeat by DAY / DATE Here I select “day” in this case Sunday
No ending date set. The above thus means:
The last Sunday of every 12th month, starting on that Sunday when I entered the repeating task. The above example is for Oct 31, 2004
So, your design should allow changes in the Interval: Day, week, Month, Year.
The week interval will thus allow you to say the 2nd week of every month (or every year, or whatever).
Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com
http://www.attcanada.net/~kallal.msn
Albert D. Kallal
Friday, January 30, 2004
Yeah, I admit making all the entries up front can take up more table space than you want and is not very scalable, especially if you have recurring events that have no end date!
Fortunately for me, we decided to limit it to 1 year max for our simple application (a phone based interview scheduling system).
Wayne
Friday, January 30, 2004
Why not just use the same format as cron? You can have entries for both scheduled and cancelled entries, they are all stored the same.
http://www.nncron.ru/nncronlt/help/EN/working/cron-format.htm
Tom H
Friday, January 30, 2004
"The beauty of storing start/end and the repeating factor means that you VERY few records in the database"
Conceivably the second-worst reason for a design decision (after "The CEO said one record per recurring appointment.")
Let's look at the suggested record format:
4 ints, 3 datetimes, and a bit. That's about 40 bytes/record. Call it 64 bytes/record - if you have a THOUSAND repeating appointments, that's [drum roll] less than 64k.
Not a big deal.
The *problem* with a single flagged record - what's the sql for testing for a conflict with existing appointments?
Philo
Philo
Friday, January 30, 2004
The record per occurrence design has another strength too, in that you can generate a bunch of appointment records and then selectively delete them.
For instance, Sally generates appointments for 1 year based on a normal schedule...Weekdays 9-5, appointment per hour except for lunch. Now you have approximately 1890 available appointment records in your appointments table (not much for an enterprise DB Server).
Next week when Sally can't make it on Wednesday morning, they delete or flag 3 records from the appointments table and you're done.
The advantage is that the logic is very simple compared to what you have to think of to enable "atomic" exclusion using the method where you only store the date range/frequency information.
Wayne
Friday, January 30, 2004
Depending on your app, this might work too (it worked for us):
Allow users to define a "template" which consists of a single day's worth of appoinments. A typical user might create two templates called Weekday and Saturday.
Once the user defines the template, you can allow them to choose that template as a basis for generating appointment records within a given range and on selected days of the week.
So if the "Weekday" template consists of 7 - 1 hour appointments (9AM-10, 10-11, 11-12, 1-2, 2-3, 3-4, 4-5PM) and the user says "Generate me some appointments using that template, for 1/1/2004 - 1/1/2005 for weekdays only", you create a bunch of appointment records.
Then, he can go and delete all the appointments that happen on Holidays, Vacations, etc.
Wayne
Friday, January 30, 2004
Take a look at Outlook's header file, as if you were going to perform automation on it. That will give you a great sense of the data structure.
Suggest you also create a class called TaskSpan that, given a beginning and ending timestamp... will read the schedule and recurrences... generating the appropriate appointment data.
p.s., I've done this and it works great. No duplication of recurrence data whatsoever.
dir at badblue dot com
Friday, January 30, 2004
There are other reasons for keeping the actual appointments as individual records (though it is good to keep the template of 'every third thursday' and that's not hard to calculate since there's only one thursday in a week and they're all 7 away from each other).
Its not unusual for periodic meetings to get changed, if you change the next meeting its not going to affect any others which have already been created. But the best reason is that its the closest model to actual reality.
For appointment systems, time recording, shift patterns and the rest, I store the date and time of the event as the elapsed seconds that year, along with the year itself. That makes calculations on overlaps and such much simpler and means indexing is cheaper. Of course there's a payment to be made in converting in and out but its worth the effort.
Simon Lucy
Saturday, January 31, 2004
I support the multiple records too. And as another reinforcement I believe this is what happens in Lotus Pants * (aka Lotus Notes)
I can't see how you can make it work with a single record. And of course I don't buy the space saving!
* Pants - for the benefit of the American audience I should point out that pants=underwear. It's the most polite way I can refer to the Lotus BagOfShite product.
Gwyn
Sunday, February 1, 2004
Always strive to model as close as possible to the actual situation, and you'll minimize your grief. (1) Listen to your clearest description in English, and you'll probably find the best data arrangement. (2) Adjust your programmatic perspective on the problem to allow for the best data arrangement.
In this case that means to record the recurring event as a single entity. When the event must be moved or dropped, such as to move it around a holiday, store that as an exception entity. To know whether today holds the event, select applicable recurring events, apply any exceptions, and you have the answer. (If you hadn't already been thinking along the lines of "does today hold the event" as the primitive programmatic operation in this problem, make the necessary adjustments to your code.)
veal
Sunday, February 1, 2004
For what it's worth, here's how I would do it.
one table to store Appointments.
one table to store 'recurrance'
when user creates a recurring appointment, 1 record is added to the recurrance table that stores the rules, eg every hour, day, week blah. whatever.
All the appointments are created in advance, we have a set limit, you can't create more than 500 in one go or something. All the appointment records have a recurrence id field to point back to the master recurrence record.
So if you are looking at/editing an appointment that links to a recurrence, you can access the rules for this recurrence and also choose to apply changes to all or just this instance. Applying the change to all would mean just updating all the appointment records that have the same recurrence id.
Alison, Dublin
Monday, February 2, 2004
 Recent Topics
Fog Creek Home
|
