Most efficient table storage for determining recurrence What is the most optimal table structure to store schedules / recurrence definitions so that we can efficiently query it for all events that will occur within a date range (min:1 max:32 days)?

My problem is currently MySQL but comments on other RDBMS will also help, since algorithms are mostly transferrable.

Consider the schema:  Event -|--has one or many--&lt;- Schedules

Schedule is a record defining a recurrence, e.g.


Monthly, every month Monthly, first Wednesday of every month
Monthly, Monday and Tuesday of every 3rd month from start date
Monthly, Monday and Tuesday of every May, June and July
Weekly, every Tuesday through Saturday every week
Weekly, Monday every 3rd week from start date


My table will have about 10,000 records at most.
For optimal store and later search, I would consider using two tables.

The first table would hold the event/schedule definition (frequency, internal, recurrences etc) - holding 1 line per event
The second table will be maintained by a process running on the first table and calculating->adding the data for future occurrences of each event.

Based on your system requirements you can decide how far ahead the second table should be filled.

This will make it easy to later query a certain events running within a specific time frame.

This solution should be more elegant than trying to calculate the date range on-the-fly, which is more complex and definitely slower in performance.