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

Iolite

Because, the database stores data for millions users. Then in total might be 365 millions rows in table for 1 year. – Horny

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.

Theotokos

The no end date is the easiest part - go up to 2525-01-01 and make it somebody else's problem. – Soapbark

@ArmandoBallaci, could you check it again? I don't see this behavior. It overwrites all changes to specific events after an update to the pattern. No time or title changes are preserved. – Mailbox

@Mailbox I tested it and it seems to work much better now. As I was developing a calendar application this was a really pain while ago – Misjudge

Google Calendar uses the approach @ZoharPeled mentioned. They stop all recurring events in 2100. People of the future will have to figure out how to backfill that data, not them. – Xylophagous

Does anyone have any example schemas for this comment? I'm struggling with implementing this as I'm unsure how I would structure the data. Also, should recurrence patterns follow iCal recurrence pattern strings? – Tenor

@ryker .. check these martinfowler.com/apsupp/recurring.pdf vertabelo.com/blog/… – Gittle

@DevinRiegle how do you know that Google Calendar uses such an approach? – Bocock

@Bocock I just tried this out in Google Calendar. Create a recurring event without an end date in Google Calendar. View that date in 2099, you should see the event. View that date in 2100, you should not see the event. All recurring events stop in 2099. You can adjust the year in the URL to easily navigate between years: calendar.google.com/calendar/u/0/r/week/2100/2/9 – Xylophagous

@DevinRiegle interesting, thanks for sharing. I wonder how is it possible then that they create so many records so instantaneously – Bocock

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.

Extend

materialised events Which could be risky if user tries to book when slot is cancelled – Unfetter

Answer 3

from the answers in the blog post and answers here:

1- eat DB storage and memory with these recurrences (with no need) , with the extreme case of "no-end date"

2- impact performance (for query / join / update / ...)

3- in case of update (or generally in any case you need to handle the recurrence set as a set not as individual occurrences) , you will need to update all rows

Gittle