title: Database design for recurring events with exceptions
body: <p>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.</p>
  
  <p><strong>Problem Description</strong></p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p><strong>Ideas</strong></p>
  
  <p>System with regular, one-time events is pretty straightforward so I won't discuss that and focus only on recurring events. </p>
  
  <p><strong>1.</strong> One possible solution is the one that resembles OOP: I can have an <code>Event</code> "class" with fields such as <code>start_date</code>, <code>end_date</code> (can be <code>null</code>), <code>recurrence_type</code> (something like enum with possible values of <code>EVERY_X_DAYS</code>, <code>DAY_OF_WEEK</code>, <code>DAY_OF_MONTH</code>) and <code>recurrence_value</code> (say <code>7</code>). When user adds new recurring event, I just create such <code>Event</code> in the database. When user wants to change 1 occurrence of this event, I add new entry to the DB of the type/class <code>MovedEvent</code> that "inherits" from <code>Event</code> with different date and has additional field <code>related_to</code> that points to the <code>ID</code> (or <code>UUID</code>, if you will) of the <code>Event</code> that it's related to. But at the same time, I need to keep track of all the <code>MovedEvent</code>s (otherwise I'd have 2 events displayed in the same week), so I need to have an array <code>moved_events</code> of <code>ID</code>s that point to all <code>MovedEvent</code>s.
  <strong>Disadvantage</strong>: every time I want to display the calendar I need to get <code>Event</code> and select all events from the <code>moved_events</code>, which is not optimal if I'll have a lot of moved events.</p>
  
  <p><strong>2.</strong> 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. <strong>Disadvantages</strong>: 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.</p>
  
  <p><strong>SQL/NoSQL? Scale details</strong></p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p><strong>Similar Questions &amp; Other Resources</strong></p>
  
  <p>I've just started reading Martin Fowler's "Recurring Events for Calendars" (<a href="http://martinfowler.com/apsupp/recurring.pdf" rel="noreferrer">http://martinfowler.com/apsupp/recurring.pdf</a>) 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).</p>
  
  <p>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:</p>
  
  <ul>
  <li><p><a href="https://stackoverflow.com/questions/123793/design-question-how-would-you-design-a-recurring-event-system">Design question: How would you design a recurring event system?</a></p></li>
  <li><p><a href="https://stackoverflow.com/questions/4634699/optimal-design-for-a-database-with-recurring-event">Optimal design for a Database with recurring event</a></p></li>
  <li><p><a href="https://stackoverflow.com/questions/14412527/design-option-for-recurring-tasks">Design option for &#39;recurring tasks&#39;</a></p></li>
  <li><p><a href="https://stackoverflow.com/questions/5183630/calendar-recurring-repeating-events-best-storage-method">Calendar Recurring/Repeating Events - Best Storage Method</a></p></li>
  <li><p><a href="https://stackoverflow.com/questions/1579638/what-is-the-best-way-to-represent-recurring-events-in-database">What is the best way to represent &quot;Recurring Events&quot; in database?</a></p></li>
  </ul>
  
  <p>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!</p>