Storing recurring events in a database

One problem that seems to affect many web developers (and desktop programmers as well) is how to store recurring events in a database. There are several ways to do this, all with varying degrees of complexity (both inserting and selecting), storage requirements, and requirements on the type of recurrences. These can all be found by googling, and most are language agnostic in their implementations.  In my case, I needed to store events that could have a very complex set of requirements. Some examples:

• Weekly on Thursday and Friday forever
• Once a month on Friday for 6 months
• Every other Friday
• Every third Friday of the month

Also, it needed to be able to handle exceptions and extensions to existing recurring events, and the events could not be purely virtualized instances, since other objects would references event id numbers as a way of collating data collected. After all this, a set of three tables seems appropriate, with Event, EventModel, and EventException objects.  Each Event is an instantized version of EventModel, and once instantized will remain forever in the database.  While this is not best practice, negative infinity in our case is relatively manageable for now.  In the future, implicit grouping of data according to a index-less key may be used.  EventModel, which contains all the attributes of a single event, plus meta data relating to recurrences acts as a prototype event for recurring events.  To normalize the data a bit, even non-recurring events will be stored as EventModels.  This will aid in data manipulation later.  The EventException object belongs to a third table, which stores individual event exceptions.

In practice, a daily cron script will create events for that day based upon the rules in EventModels.

When viewing events, the easiest way is to view EventModels, with sub-grouping of recent and upcoming events associated with that EventModel.

Of course, I’m not an actual programmer or computer scientist, so suggestions are welcome.