How to store a recurring time window

Question

What is a preferred way to store recurring time windows?
For example. If I have a calendar system where I need to be able to accommodate daily, weekly or monthly recurring events, what sort of time management system is best?

How is this best represented in a database?

More Details
The Specific goal of this is to provide sets of open time windows. Once we have these time windows, the code needs to test if a message that arrives to the system falls within one of the time windows.

Answer 1

I would create a RecurrenceType table which would hold records for each type of recurrence that your calendar system supported. Each event in the CalendarEvents table would have a reference to the ID in the RecurrenceType table.

I would then have a RecurrenceRules object in the code which would hold the business logic of calculating when the next date will be given a current or starting date or retrieve the next N dates as an array.

Most of the recurrence logic would probably have to be coded off enumerated values which are based on the RecurrenceType table's ID. I would be surprised if you could make it all data driven unless it was a very simple calendar system.

Hope that helps. It sounds like an interesting project.

Answer 2

This is long delayed, but after much testing and many heartaches, we decided on a certain scheme to make this work. For the needs of our system, we are required only to recur on a weekly basis if at all. The final product will be designed as such.

Here is the DB schema

id : int(10)
window_name : varchar(100)
start_date : datetime
end_date : datetime
start_time : time
duration : int(10)
timezone: varchar(100)
monday : tinyint(1)
tuesday : tinyint(1)
...
sunday : tinyint(1)

1. Each "window" of time will have a set start date, end date, start time and a duration.
2. The window will open on the start date and start time and run for "duration" seconds
3. The window will only open on specified days of the week

This is the only system that would allow windows to open and close on a recurring weekly basis and also have windows that would span into the next morning. I am sure there are more complex systems out there that do recurring windows, but we were looking for something flexible and fast, and did not need monthly recurring events or yearly events.