1

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.

Improve this question
2

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.

Improve this answer
2
  • A very astute observation! Somehow I got tunnel-visioned into the on-the-fly approach, when as you say, the pre-calculation of the actualized events makes retrieval almost immediate at the expense of storage and some limitations (e.g. generate last 50 years to next 50 years). Do you have any comments on a suitable data structure though? – 孔夫子 Jul 15 '15 at 3:55
  • Looking at SQL Server, there's a scheduling mechanism that works to execute jobs. You can apply a logic similar to what they've done. Here's the link to the schedule table: msdn.microsoft.com/query/… – asapir Jul 15 '15 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.