Best way to manage repeating schedules in a database application?

[blacklotus]

I currently support a very old PHP/MySQL database application meant to manage an inventory of equipment along with a ton of other related information (i.e. support groups, clients, pager schedules, billing information etc...)

 

One of that info is repeating maintenance window schedules per server. Currently, the application only stores the base information of each schedule (effective start date, start time, length, when it runs -- i.e. every day, every xx weekday(s), every month, biweekly, etc...). When we want to display the next windows (from today), we use complex function that calculates the next 3 dates based on the base information of each maintenance window.

 

This works fine, and I managed to make the code efficient enough so that this process is very fast, even though there are several hundreds of schedules to calculate sometimes.

 

The problem is when I want to search for every schedule that would be running on a given date in the future... how can I efficiently do that? For example, I would like to be able to search for a date, and return a calendar showing graphically every schedule  (with its length) that would be running on that day. It's easy for me to start from a specific, and extrapolate the next dates, but to do the opposite would be very CPU intensive wouldn't it?

 

So I was wondering how others would do this... Would it simply be best to store each day the recurrence runs into a database table so I can search for a date easily? For example, when I save a repeating schedule's base info, I would immediately insert every occurrence of the schedule into a table for a given period of time (say the next few years), which would take a lot of database space? Or is there an easier way or magical date calculation formula I can use?

 

For example, in Outlook 2003's calendar, you can see repeating schedules for MANY years ahead, and it doesn't seem to have a limit. It can't possibly store each occurrence of the series in its database? So how does it do it?