Recurring Events Database Model

Question

I've being searching for a solution for recurring events, so far I've found two approaches:

First approach:

Create an instance for each event, so if the user has a daily event for one year, it would be necessary 365 rows in the table. It sounds plausible for a fixed time frame, but how to deal with events that has no end date?

Second approach:

Create a Reccuring pattern table that creates future events on runtime using some kind of Temporal expression (Martin Fowler).

Is there any reason to not choose the first approach instead of the second one? The first approach is going to overpopulate the database and maybe affect performance, right?!

There's a quote about the approach number 1 that says:

"Storing recurring events as individual rows is a recipe for disaster." (https://github.com/bmoeskau/Extensible/blob/master/recurrence-overview.md)

What do you guys think about it? I would like some insights on why that would be a disaster.

I appreaciate your help

Answer 1

The proper answer is really both, and not either or.

Setting aside for a moment the issue of no end date for recurrence: what you want is a header that contains recurrence rules for the whole pattern. That way if you need to change the pattern, you've captured that pattern in a single record that can be edited without risking update anomalies.

Now, joining against some kind of recurrence pattern in SQL is going to be a great big pain in the neck. Furthermore, what if your rules allow you to tweak (edit, or even delete) specific instances of this recurrence pattern?

How do you handle this? You have to create an instance table with one row per recurring instance with a link (foreign key) back to the single rule that was used to create it. This let's you modify an individual child without losing sight of where it came from in case you need to edit (or delete) the entire pattern.

Consider a calendaring tool like Outlook or Google Calendar. These applications use this approach. You can move or edit an instance. You can also change the whole series. The apps ask you which you mean to do whenever you go into an editing mode.

There are some limitations to this. For example, if you edit an instance and then edit the pattern, you need to have a rule that says either (a) new parent wins or (b) modified children always win. I think Outlook and Google Calendar use approach (a).

As for why having each instance recorded explicitly, the only disastrous thing I can think of would be that if you didn't have the link back to the original recurrence pattern you would have a heck of a time cancelling the whole series in one action.

Back to no end date - This might be a case of discretion being the better part of valour and using some kind of rule of thumb that imposes a practical limit on how far into the future you extend such a series - or alternatively you could just not allow that kind of rule in a pattern. Force an end to the pattern and let the rule's creator worry about extending it at whatever future point it becomes necessary.

Answer 2

Store the calendar's event as a rule rather than just as a materialized event.

Storing recurring event materialized as a row is a recipe for disaster for the apparent reason, that the materialization will ideally be of infinite length. Since endless length table is not possible, the developer will try to mimic that behavior using some clever, incomprehensive trick - resulting in erratic behavior of the application.

---

My suggestion: Store the rules and materialize them and add as rows, only when queried - leading to a hybrid approach.

So you will have two tables store your information, first for storing rules, second, for storing rows materialized from any rule in the rules' table.

---

The general guidelines can be:

• For a one-time event, add a row to the second table.
• For a recurring event, add a row to the first table and materialize some of into the second table.
• For a query about a future date, materialize the rules and save them in the second table.
• For a modification of a specific instance of a recurring event, materialize the event up till the instance you want to modify, and then modify the last instance and store it.
  • Further, if the event is too far in the future, do not materialize it. Instead save it as a rule also and execute it later when the time arrives.

---

Plain tables will not be enough to store what you are trying to save. Keeping this kind of information in the database is best maintained when supported with Stored Procedures for access and modifications.