The Complex World of Calendars: Database Design

In part one we discuss the use of RRule to describe the recurrences of a repeating event. Now let's cover the basic schema design for this system.

First, we need to store the actual event data so let’s create a model called Event

model Event {
  id              Int
  title           String
  description     String
  host            String
  price           Int
}

Here we can store basic information about an event. We now need to consider where to store information about the recurrence and we also need to think about handling exceptions and cancellations.

Let's create two models for repeating the event and adding exceptions.

model EventParent {
  id              Int
  rule            String
  event           Event
  exceptions      EventException[]
}
model EventException {
  id           Int
  originalDate DateTime
  date         DateTime
  parent       EventParent
  event        Event
}

Here you notice that both EventParent and EventException reference Event. This is because we do not want to duplicate the same fields across multiple models, so we use a relationship instead.

Now when requesting an EventParent we have all the information to handle its recurrence rules and exceptions. A JSON result for EventParent would look like:

Now we have the possibility to modify any field of the parent event, without modifying the original event at all.

Generating the final RRule

Using the fields date and originalDate in EventException we can update the final rule RDATE and EXDATE fields to ensure that the replaced event no longer appears in the output. This would look something like:

DTSTART:20200101T140000Z
RRULE:FREQ=DAILY;INTERVAL=1
RDATE:20200520T150000Z
EXDATE:20200520T140000Z

The same logic can apply for cancellations so the final set of models would look like:

model Event {
  id              Int
  title           String
  description     String
  host            String
  price           Int
}
model EventParent {
  id              Int
  rule            String
  event           Event
  exceptions      EventException[]
  cancellations   EventCancellations[]
}
model EventException {
  id           Int
  originalDate DateTime
  date         DateTime
  parent       EventParent
  event        Event
}
model EventCancellations {
  id           Int
  originalDate DateTime
}

This is a simplified example but should be enough to handle many edge cases with calendars.