Laying out a database schema for a calendar application

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.

Answer 2

I recently created a calendar application and this was one of the many challenges that I faced.

I eventually came up with a semi hack-ish solution. I created an event_type column. In that column, I had either: daily, weekly, monthly, or yearly. I also had a start_date and an end_date columns. Everything else was handled in the actual backend code.

I never tried to split an event if a user edited only one event. It wasn't necessary in the situation. However, you could split an event by changing the end_date of the first, creating a new event with a new start_date and the end_date of the original, and finally, a new event for the one you just chose to edit. This process would end up creating 3 events.

Hack-ish, I know. I couldn't think of a clever way to handle this problem at the time.