Should I store dates or recurrence rules in my database when building a calendar app?
I am building a calendar website (ASP.NET MVC) application (think simple version of outlook) and i want to start supporting calendar events that are recurring (monthly, yearly, etc)
right now I am storing actual dates in my but I wanted to figure out if, with recurrence, does it make sense to continue to store dates (with some obvious cutoff), or should I store the recurrence options and generate the dates on the fly.
It got me thinking how outlook, google mail, etc does this or any other service that supports recurring calendar items.
Are there any suggestions on this?
Answer
Separate your data into two parts: the "canonical" data (the recurrence rule) and "serving" (generated dates; read-only aside from regeneration). If the canonical data changes, regenerate the "serving" data at that point. For infinite recurrences, keep some number of instances and generate more if you run out (e.g. if the user looks at their calendar for 2020).
If you had infinite processor speed, you'd only need the canonical data - but in reality, doing all the date/time processing for all the recurrence rules on every page view is likely to be too time-consuming... so you trade off some storage (and complexity) to save that repeated computation. Storage is usually pretty cheap, compared with the computation required for a large number of events. If you only need to store the dates of the events, that's really very cheap - you could easily use a 4 byte integer to represent a date, and then generate a complete date/time from that, assuming your recurrences are all date based. For time-based recurrences (e.g. "every three hours") you could full UTC instants - 8 bytes will represent that down to a pretty fine resolution for as long as you're likely to need.
You need to be careful about maintaining validity though - if a recurring meeting changes today, that doesn't change when it has happened in the past... so you probably want to also have canonical read-only data about when recurrences actually occurred. Obviously you won't want that to keep the past forever, so you probably want to "garbage collect" events more than a few years old, depending on your storage limitations.
You may also need the ability to add notes and exceptions (e.g. "meeting doesn't occur today due to a public holiday" or "moved to 4pm") on a per-occurrence basis. That becomes really fun when you change the recurrence - if you change "every Monday" to "every Tuesday" do you keep the exceptions or not? How do you even match up the exceptions when you change from "every day" to "every week"? These aren't questions which are directly about storage - but the storage decisions will affect how easy it is to implement whatever policy you decide on.