Talk Like A Duck

Joe Van Dyk, a reader, posted a question in response to my recent article about my work towards a new gem providing iCalendar support for Ruby

How do people represent recurring events in a database? I’ve never been able to come up with a good way.

My particular problem is that each event that reoccurs needs to be able to be worked with individually (i.e. comments added to it), deleted, moved, etc.

Rather than answering directly in the comments section, I'll give my thoughts on this as a new article

My approach to this would start with recognizing the difference between an event, and an occurrence of that event. Each event would be stored as a single entity on the database, no matter how many occurrences that event had. Each event entity would have a start date-time which would be the start time of the first occurrence, and a last occurence end date-time which would be the end of the last occurrence if the event had a defined last-occurrence, or null if the recurrence set was open ended.

A non-recurring event would be handled as a degenerate case of a recurring event, with a single occurrence.

The idea is to allow for a database query which returns the set of (recurring) events which have any occurrences between two, points in time. The use-case here is displaying a calendar page, where I need to find all of the events for a given, day, week, month, etc.<\p>

Once I had those events, I'd ask each one for a list of occurrences within the target range, which for some events and some ranges might be empty.

Individual Occurrences

As Joe points out there are time when you need to talk about individual occurrences of an event. RFC 2445 talks about this. There are basic mechanisms in iCalendar to support use-cases like editing a series of recurring events. So iCalendar, defines three attributes which combine to connote a particular event, set of occurrences, or a particular occurrence

UID

A globally unique identifier which identifies a particular event with all of it's occurrences. The set of occurrences may change if the event is edited, with different edits being identified by the ...

Sequence Number

The revision sequence number of a calendar component, in this case a VEVENT, with a given UID

Recurrence-ID

Which represents either a particular occurrence of an event identified by a UID and sequence number, or subsequence either at the beginning or end of the list of occurrences for an event. The value of the recurrence id is a string containing either:

1. A string representation of the start time and date for the occurrence or
2. A string representation of the date of the occurrence in the case of an anniversary (all-day) event or
3. Either of the first two, preceded by "THISANDFUTURE:", indicating all occurrences starting with the one identified by the date or date-time or
4. Either of the first two, preceded by "THISANDPRIOR:", indicating all occurrences starting with the first through the one identified by the date or date-time or

I'd put any attributes associated with an individual occurrence into a separate entity belonging to the event, which would have many of these, and identified with a recurence-id.

Just a word of caution, here. This article reflects a mixture of things I have actually done in the past, with some thought experimentation. I haven't actually had to deal with attributes for occurences, but what I've described 'feels right' to me as a first approximation.