How to model a calendar application? [duplicate]

Question

I want to write a calendar application. It is really recurring items that throw a wrench in the works for the DB schema. I would love some input on how to organize this.

What if a user creates an event, and inputs that it repeats everyone Monday, forever? How could I store all that in the database? I can't create infinite events. Do I simply put a table in there that holds the relevant info so I can calculate where all the events go? If so, I would have to calculate them every time the user views a new part of the calendar. What if they page through the months, but they have a ton of recurring items?

Also, the schema needs to handle when a user clicks an item and says "Edit this one in the sequence" not all items in the sequence. Do I then split the one item off of the sequence?

Update 1

I have not looked at iCal at all. To be clear, I think saving the info that allows you to calculate the recurring items, and splitting off any that differ from the sequence is a great way to store it to be able to transfer it. But I think that in an application, this would be too slow, to do the date math all over the place.

Answer 1

I have been struggling with the same problem, and I was actually toying with the "cache table" idea suggested above, but then I came across an alternative (suggested here) that doesn't seem to have been represented yet.

Build a table containing all events

EventID (primary key)
Description
StartDate
PeriodType - days, weeks, months, years
PeriodFreq - # of days, weeks, etc between events
EndDate
... other attributes that can be modified

Then add a table for exceptions to these events. This table uses a composite key, made up of the EventID that maps to the event table, and an instance ID to pick the particular event in the series.

EventID (key)
InstanceID (key)
InstanceDate - the modified date of the exception 
IsCancelled - a flag to skip this date when traversing the series
... other attributes that can be modified

It seems to keep the event table normalised, and avoids splitting up series to handle exceptions.