Database design for recurring events with exceptions

Question

I'm building a system that needs to store/manage different types of events. For simplicity, I will focus on designing a calendar (I'm building something slightly different, but calendar is a good analogy and it's easy to reason about). I'd like to hear about possible database/schema design ideas.

Problem Description

I have a calendar with different types of events (for simplicity sake, say there is only 1 type of event: Task). User can add new event for a particular date, edit (change some details, like title or move to another date) or delete. There can be one-time events and recurring events (with different types of recurrence: every X days, every 15th day of the month, every week on Monday; kind of like simple cron). When user moves recurring event, all other instances of this event are moved in the same manner (e.g: +3 days). Important part: recurring events can have exceptions. So, for example, let's say I have an recurring event A which is repeated every 7 days. But I want to change it's date for next week, so instead of Tuesday, it's be assigned to Friday, after that it'll still occur on Tuesday. This "exception" event shouldn't be affected when "parent" event is moved.

Also, every recurring event can have additional info, that is related only to 1 particular instance, e.g: I have the same recurring event A repeated every 7 days, I want to add a note for this week instance that says "X", and I want to add another note for the event A next month that says "Y" - those fields are only visible to that single instances.

Ideas

System with regular, one-time events is pretty straightforward so I won't discuss that and focus only on recurring events.

1. One possible solution is the one that resembles OOP: I can have an Event "class" with fields such as start_date, end_date (can be null), recurrence_type (something like enum with possible values of EVERY_X_DAYS, DAY_OF_WEEK, DAY_OF_MONTH) and recurrence_value (say 7). When user adds new recurring event, I just create such Event in the database. When user wants to change 1 occurrence of this event, I add new entry to the DB of the type/class MovedEvent that "inherits" from Event with different date and has additional field related_to that points to the ID (or UUID, if you will) of the Event that it's related to. But at the same time, I need to keep track of all the MovedEvents (otherwise I'd have 2 events displayed in the same week), so I need to have an array moved_events of IDs that point to all MovedEvents. Disadvantage: every time I want to display the calendar I need to get Event and select all events from the moved_events, which is not optimal if I'll have a lot of moved events.

2. Another idea is to store every event as a separate record. IMO it's a terrible idea, but I just mentioning it because it's a possibility. Disadvantages: every time I want to edit the main event (e.g: I want to change the event from occurring "every 7 days" to "every 9 days") I need to change every single occurrence of the event. "Exceptions" (changing single instance) is easier, though.

SQL/NoSQL? Scale details

I'm using PostgreSQL in my project, but I have basic knowledge in NoSQL databases and if they are better suited for this kind of a problem, I can use it.

Scale: Let's say I have 5k users, and each will have on average 150 events/week, 40% of which can be "exceptions". Therefore I want to design this system to be efficient.

Similar Questions & Other Resources

I've just started reading Martin Fowler's "Recurring Events for Calendars" (http://martinfowler.com/apsupp/recurring.pdf) but I'm not sure if it applies to my problem and if so, how one would design database schema according to this document (suggestions are welcome).

There are similar questions, but I didn't see any mention of "exceptions" (changing 1 event instance without affecting other), but maybe someone will find these links useful:

• Design question: How would you design a recurring event system?

• Optimal design for a Database with recurring event

• Design option for 'recurring tasks'

• Calendar Recurring/Repeating Events - Best Storage Method

• What is the best way to represent "Recurring Events" in database?

Sorry for a long question, I wanted to describe the problem well. Yet, I feel that's pretty chaotic, so if you have additional questions, I will happily provide more details. Again, I'd like to hear about possible database/schema design ideas plus any other suggestions. Thank you!

Answer 1

Use iCalendar RRules and ExDates

If it's a recurring event, just store the start/end datetimes and RRules and ExDates for the event.

Use a Materialized View to pre-calculate upcoming actual events, say for the next 30 days or 365 days.

As you are using Postgres, you can use existing python, perl, or javascript RRule libraries (such as dateutil) inside pg function for calculating future events based on the rrules and exdates

UPDATE: check out pg_rrule extension: https://github.com/petropavel13/pg_rrule