Laying out a database schema for a calendar application

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)DescriptionStartDatePeriodType - days, weeks, months, yearsPeriodFreq - # of days, weeks, etc between eventsEndDate... 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.

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.

Hold the recurring item in the events table as normal, but flagged as recurring with the appropriate start/ end dates.

If the user modifies a single instance of the appointment, just create a new event, perhaps with a 'parentId' equal to the recurring event's id.

Build logic that causes the calendar to override any recurring events on a particular day with events with matching parent IDs.

Your question about performance is basically the old speed vs. storage issue. I really don't think the calculation required would exceed the space requirement for storing so many appointments. Just read up on database optimization- indexing etc.