Hello,
In an application I'm designing, the user has to be able to specify rather complex event scheduling (continuous time-block vs. daily time-blocks, exception date/times, recurrence patterns etc.)
Does anyone know of a good design page for such a thing online? For example, I was highly impressed with this page's description of how to do database audit trails, and would love something similar.
Current thinking
- My database would contain of the following tables: Events and ScheduleItems
- The relationship would be Events {1 -- 0..*} ScheduleItems
- Events would have the following columns: eventId, schedulePattern
- ScheduleItems would have the following columns: eventId, startDateTime, endDateTime
- The front end controls would allow the user to specify general rules (daily/continuous, includes/excludes weekends, start/end date/time etc.). If they are not satisfied with the existing controls, they could then opt to display and manually tweak the generated "time-blocks"
- On saving the event schedule...
- If only the provided controls were used (no tweaking) I would save their selections as a pattern in the Events table (i.e. "sd:2010-04-28;st:09:20:00;ed:2010-05-12;et:17:20:00;r:2w[M-Th];z:EST" etc.)
- If the user manually tweaked the generated time-blocks, I would save each individual time-block within the the ScheduleItems table and have Events.schedulePattern be given a special code ("MANUAL" or something).
Pros
I should be able to save > 90% of the events through the pattern field directly, and be able to handle any other corner cases through the "brute-force" ScheduleItems table. Since some of the handled cases include events that can go on for months (which would otherwise result in a very large number of time-blocks), having it in one line is rather attractive.
Cons
This is a fairly complex solution; any other system requiring this data would need to be able to handle parsing the schedulePattern as well as knowing when to fetch ScheduleItems.