After implementing an events calendar in Java/JSP (which itself was an almost-knockoff of third-party library ExtCalendar, with extended invitation and permission functionality), I started thinking there must be a better way to represent repeated events. Luckily, in that case, every event was a single instance, but for a multi-function calendar, recurrence (that being an event repeating multiple times, possibly to infinity, even) is a necessity.

There are multiple classes of time duration between recurrences, that may include:

  • daily
  • [multiple] day of the week

    • Mon, Wed, Fri
    • Tue, Thu
    • weekdays
    • weekend days
  • every X days
  • weekly
  • biweekly
  • monthly
  • day of the month
  • first occurrence of day in the week of the month (e.g., first Monday in April, second Friday in May)
  • annual

An annual event may not necessarily take place on the same day every year, so you must provide a mechanism to copy events over. You do this by tracking events that have expired, then marking them as moderated when you decide whether to copy them over or not. So that will complicate the data model even more.

An event has a start date and an expiry date where the recurrence continues until. The recurrence expiry date is chosen based on the number of appointments/recurrences and the class/type of recurrence (e.g., to select 4 appointments that are weekly, calculate expiry to be start date * appointment duration * 4 appointments * 7 days/week.)

The basic SQL table representation is:

In the below PHP code, Model_Event::fetchEntriesByMonth($year, $month) is used to retrieve potential events in the given month from the database, then more business logic must be thrown into a ModelController class’ action method to filter those that actually do appear in the month (maybe overlapping with the previous/next months for those with a duration longer than a day.)

For SQL SELECT queries, to find all events between times A and B, you must select every event that may possibly (but not necessarily) fall between those times. You do this using the start and expiry dates along with the event’s duration per recurrence. Then a switch statement is used on the recurrence type when looping through all events for each individual day in between A and B. That’s O(n2) for the smarty-pants in the crowd. Creating an exact filter in the SQL is way too complicated for the different classes of recurrence types available. You may as well create your own meta language to handle this situation. Keep Prolog out of it.

On top of this complexity, you have to also consider exporting your events to Microsoft Outlook, Google Calendar, and any other calendar suite. I was ignorant in my Java implementation and just assumed it was local time for each event where the user can assume the timezone based on the event’s location. But to export without ambiguity, you have to factor in timezone along with daylight savings for every single event, or else prompt to the user to set the timezone upon export, which is an unacceptable query, really.

In summary, get an already-existing fully tested piece of events calendar software rather than rolling out your own solution. You’ll thank yourself for the prevented headaches caused by unforeseen roadblocks that I haven’t even touched upon.