How to save a repeating time window

Question

What is the preferred way to store repeating time windows?

For instance. If I have a calendar system where I need to arrange daily, weekly or monthly recurring events, which time management system is better?

How is this best represented in a database?

More details
The specific goal is to provide sets of open time windows. When we have these time windows, the code should check whether the message arriving in the system falls into one of the time windows.

Answer 1

This is a long delay, but after many trials and many headaches, we decided to create a specific scheme for this work. For the needs of our system, we only need to repeat weekly, if at all possible. The final product will be developed 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) 

• Each time window will have a specified start date, end date, start time, and duration.
• A window will open at the start date and start time and the “duration” of seconds will start
• The window will be open only on certain days of the week.

This is the only system that will allow windows to open and close on a periodic weekly basis, as well as have windows that will be distributed the next morning. I am sure that there are more complex systems that make repeating windows, but we were looking for something flexible and fast, and did not need monthly repeating events or annual events.

Answer 2

I would create a RecurrenceType table that will store records for each type of repetition that your calendar system supported. Each event in the CalendarEvents table will reference an identifier in the RecurrenceType table.

Then I will have a RecurrenceRules object in the code that will contain the business logic of the calculation when the next date will be given the current or starting date or get the next N dates as an array.

Most of the repetition logic should probably be encoded with enumerated values ​​that are based on the RecurrenceType table ID. I would be surprised if you could make all the data manageable, unless it was a very simple calendar system.

Hope this helps. It sounds like an interesting project.