Representing a schedule in a database
- by David Pfeffer
I have the interesting problem of representing complex schedule data in a database. I need to be able to represent the entirety of what the iCalendar (ics) format can represent, but in my database.
I don't care about insertion efficiency but query efficiency is critical. The operation I will be doing most often is providing either a single date/time or a date/time range, and trying to determine if the defined schedule matches any part of the date/time range. Other operations can be slower.
For those unfamiliar, ics allows representation of a single event or a reoccuring event based on multiple times per day, days of the week, week of a month, month, year, or some combination of those. For example, the third Thursday in November, or the 25th of December, or every two weeks starting November 2nd and continuing until September the following year.
Any suggestions?
