Comment by whack | Hacker News Reader

Comment by whack | Hacker News Reader

Parent Full thread

How did you handle the database storage for recurring events? Did you have just one entry that represents the recurring event as a abstract whole, or a database entry for each instance of the recurring event?

The former seems "better", but you also run into a whole lot of complications:

1) The user can delete specific instances of a recurring event. Eg: delete the event for thanksgiving Thursday but leave it intact for all other Thursdays

2) The user can make instance-specific edits. Eg: edit the event description with custom meeting-notes that's specific to that week's instance

3) The user can invite/dis-invite people for specific instances. Eg: invite Dave only for the event this Thursday, but not for the following weeks'

Creating a database entry for each instance avoids the above complications, but comes with its own drawbacks: if a user creates a recurring event with no end-date, you have to populate a large number of instances, all the way until some arbitrary MAX_DATE.

I was asked this question in an interview once and I recommended the latter option as the lesser evil, and the interviewer was visibly displeased with my recommendation. I'm wondering what the better solution would be.

For our to-do app, Matterlist (https://matterlist.com), we went with the first approach: we represent a recurring task as a separate entity. Here's a quick explanation: https://matterlist.com/recurring.html

Matterlist's recurring task descriptors ("Schedule Items") essentialy define infinite sequences of recurring events, which are not represented in the database, but are visible and editable via the same UI, just as the regular, non-recurring tasks.

Here's how we deal with the problems you outlined:

1) When a user deletes a specific instance, we create an "exclusion" record for that day, so that the function that generates an endless sequence of recurrences doesn't generate one for that day.

2) When a user edits a specific instance of a recurring task, we also create an "exclusion" record for that day (because otherwise we would have two tasks on that day, the edited instance, now with a database record, and the virtual instance), and we create a database record for the edited instance, so it just becomes a regular task.

3) We don't have shared tasks yet, but that would be handled via the same framework, as outlined in 1 and 2 above. We'd just de-virtualize an instance and add/remove users to it.

Creating a database entry for each instance of a recurring task was unacceptable for us, because we wanted infinite forward visibility, and because when a user edits the parameters of a recurring task (e.g. changes it from daily to weekly), we'd have to delete all the old records and create the new records.

BTW, we have finally released an Android app, so you can see our recurring tasks in action: https://matterlist.com/#apps

I went with the latter. We limited the total number of events to something like 5 or 10 years to avoid it [edit: the max age problem]. The only real issues that came up from that approach were:

* UX was hard when people want to change individual events that are also part of a recurring event (who wins)

* daylight savings time is an issue when you store datetime in UTC (you need to shift the UTC value in Nov vs in June to have it be the same "8am every Friday" event)

* saving a change across all of a large number of events created a large, slow transaction that caused slow responses (in some cases triggering heroku timeouts)

I was lucky that we didn't need to implement inviting guests, as that wasn't needed.

There were times when I wish I'd implemented the RFC standard: https://www.ietf.org/rfc/rfc2445.txt but never got to that. (We also evaluated just building on top of Google Calendar, but I was worried about building on top of an API that we weren't paying for, and there was the issue of tying in child equipment events.)

Some very quick thoughts.. Have something to resemble the event series as a single instance - then have 1...n relation to actual occurrences for occasions when there has been a change, added notes or cancellation. These should be created when the change/added notes/etc. are made.

I would say store a default event together with the pattern it repeats in. Then create exceptions that hold additional info or a cancellation when needed. When they finally add an end date in one way or the other, add the end date to the default.

On load/view time, generate events from the list of defaults and amend with the exceptions as needed. The user should not see the default/exceptions system, just events and the pattern.

I’d say working with exceptions in this way is more efficient than storing many copies of the same event.

I would have one meta event, and if you modify it, you implicitly clone it and modify that clone

This is a good read about recurring events: https://martinfowler.com/apsupp/recurring.pdf

Outlook/exchange uses the former approach, a pattern with a list of instance-specific exceptions. One reservation system I worked on used the latter approach, with every instance saved to the database. I was tasked with building an outlook plugin to smoothly integrate the two, and it was probably the most difficult piece of software to get working reliably I ever wrote. The prototype took a few days, the fully reliable version compatible with all outlook versions took two years. Outlook’s internals are pure madness.

I have worked with recurring events, and the answer that I've seen is generally both. You have one record which is the "template" and then populate the database out with specific instances to some arbitrary date. Every so often then run a cron to populate further and further out.

That's pretty much what I did, but the original record wasn't stored in the DB, just the specific instances. Our use case was such that folks were moving around events periodically (it was a scheduling app), so we rarely had anyone "run off the end" of their repeated events.

Reply on news.ycombinator.com