Note: I wrote this about 2 years ago and forgot to publish it, it still applies.
While working on a client application I was faced with the challenge of storing recurring events.
The problem with recurrence is the database, linear sequential data is what the db is built for, recurrent events are not necessarily sequential. One might argue that they occur in order so therefore some sort of sequence exists. In my opinion that is both right and wrong.
Say for example you need to schedule and event that occurs today and reoccurs every week on the same week day at the same time for next 12 weeks. You could write a method to iterate through and save all instances of this event as recurring items in a table.
There are a couple of problems with that take. Let’s say you need not only to record the dates the events will happen on, but also the times. Suppose those date/time combinations also inforce “unicallity” scoped to a specific location, therefore not allowing other events to be scheduled for the same time/date. Say you have an event that happens every weekday at 1, 3, 4 and 7 o’clock for the next six months. You just added 500+ records to your database each to hold that date/time/location spot.
Iterating through those events later on, in order to sanely display them in a calendar format will be as much fun as poking yourself in the eye with a sharp #2 pencil. This method not only may cause your database to grow immensely unnecessarily, it may also cause you much headache and grief in the future when an recurring event is updated and/or deleted.
Martin Fowler has provided great insight into this issue. He introduced the idea of using temporal expressions for scheduling of recurring events.
Using temporal expressions to specify the recurrence of a recurrent event is a step in the right direction. The schedule needs to figure out if and which events happen on a given at a given time. A recurrence can be referred to as a schedule element. A schedule element is created for each event recurrence “with the ‘when’ part delegated to a temporal expression” [Fowler]. The temporal expression has an instance method to whether a date/time lies within the temporal expression. With that we can separate event matching from date/time matching.