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--<- 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.