Optimal design for a Database with recurring event

Question

I want to create a php calender application. What is the optimal database design for this software if I want to create normal, weekly, monthly and yearly events.

Answer 1

I would just create an entry for each recurrence of the event, out to some horizon. However, it means that you'll need another table that you can use to project out the dates if they scan past your horizon date. I.e., you'll need an events table that contains one record for each occurrence of a repeated event (Jan 1, Jan 8, Jan 15, ... through December), and a table with each record available to seed future years (start date: Jan 1; repeat: 7; through: 2011) so that at the start of 2012 (or as soon as the user requests a view of a 2012+ month) you can generate the future events.

This has two big disadvantages:

1. Your database has data for a full year. However, if adding a full year's worth of data ruins your performance, your system is probably underpowered. (It seems a requirement that a calendar app be able to handle many years' worth of dates)
2. At the end of the event horizon, you need to generate the future dates for recurring events.

The advantages (IMO) that outweigh the disadvantages:

1. Easier math when displaying the calendar. Using Tim's method, above, if the user loads Dec 18, 2011, how are you going to calculate which recurring events should be placed on that day? You'll be forced to loop through EVERY recurring event every time you display a date. The tradeoff is disadvantage #1, which I think is the better solution that having to redo these calculations.
2. You can edit specific instances of an event. Using Tim's method, if a meeting occurred on a holiday and the user changed it to the previous day, how would you even do it? Using the one-entry-per-event method described here, you could just modify that record for the event, easily moving single occurrences around in the calendar.

Answer 2

You could have a column in the EVENT table for RECURRENCE_STATUS which tracks 4 values, does not recur, weekly, monthly, yearly. The query of the day's events would be a union of those that are set to occur on that day and which do not recur with the set of those that do recur and whose initial date's difference from today is a modulo 0 for week, month, and a year differences. The date-math calculations are a little more nuanced than that (because of the varying number of days in the month) but the structure should suffice.