What's the Best Way to Model Recurring Events in a Calendar Application

What's the best way to model recurring events in a calendar application?

I would use a 'link' concept for all future recurring events. They are dynamically displayed in the calendar and link back to a single reference object. When events have taken place the link is broken and the event becomes a standalone instance. If you attempt to edit a recurring event then prompt to change all future items (i.e. change single linked reference) or change just that instance (in which case convert this to a standalone instance and then make change). The latter cased is slightly problematic as you need to keep track in your recurring list of all future events that were converted to single instance. But, this is entirely do-able.

So, in essence, have 2 classes of events - single instances and recurring events.

Calendar Recurring/Repeating Events - Best Storage Method

Storing "Simple" Repeating Patterns

For my PHP/MySQL based calendar, I wanted to store repeating/recurring event information as efficiently as possibly. I didn't want to have a large number of rows, and I wanted to easily lookup all events that would take place on a specific date.

The method below is great at storing repeating information that occurs at regular intervals, such as every day, every n days, every week, every month every year, etc etc. This includes every Tuesday and Thursday type patterns as well, because they are stored separately as every week starting on a Tuesday and every week starting on a Thursday.

Assuming I have two tables, one called events like this:

ID    NAME
1     Sample Event
2     Another Event

And a table called events_meta like this:

ID    event_id      meta_key           meta_value
1     1             repeat_start       1299132000
2     1             repeat_interval_1  432000

With repeat_start being a date with no time as a unix timestamp, and repeat_interval an amount in seconds between intervals (432000 is 5 days).

repeat_interval_1 goes with repeat_start of the ID 1. So if I have an event that repeats every Tuesday and every Thursday, the repeat_interval would be 604800 (7 days), and there would be 2 repeat_starts and 2 repeat_intervals. The table would look like this:

ID    event_id      meta_key           meta_value
1     1             repeat_start       1298959200 -- This is for the Tuesday repeat
2     1             repeat_interval_1  604800
3     1             repeat_start       1299132000 -- This is for the Thursday repeat
4     1             repeat_interval_3  604800
5     2             repeat_start       1299132000
6     2             repeat_interval_5  1          -- Using 1 as a value gives us an event that only happens once

Then, if you have a calendar that loops through every day, grabbing the events for the day it's at, the query would look like this:

SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
RIGHT JOIN `events_meta` EM2 ON EM2.`meta_key` = CONCAT( 'repeat_interval_', EM1.`id` )
WHERE EM1.meta_key = 'repeat_start'
    AND (
        ( CASE ( 1299132000 - EM1.`meta_value` )
            WHEN 0
              THEN 1
            ELSE ( 1299132000 - EM1.`meta_value` )
          END
        ) / EM2.`meta_value`
    ) = 1
LIMIT 0 , 30

Replacing {current_timestamp} with the unix timestamp for the current date (Minus the time, so the hour, minute and second values would be set to 0).

Hopefully this will help somebody else too!

---

Storing "Complex" Repeating Patterns

This method is better suited for storing complex patterns such as

Event A repeats every month on the 3rd of the month starting on March 3, 2011

or

Event A repeats Friday of the 2nd week of the month starting on March 11, 2011

I'd recommend combining this with the above system for the most flexibility. The tables for this should like like:

ID    NAME
1     Sample Event
2     Another Event

And a table called events_meta like this:

ID    event_id      meta_key           meta_value
1     1             repeat_start       1299132000 -- March 3rd, 2011
2     1             repeat_year_1      *
3     1             repeat_month_1     *
4     1             repeat_week_im_1   2
5     1             repeat_weekday_1   6

repeat_week_im represents the week of the current month, which could be between 1 and 5 potentially. repeat_weekday in the day of the week, 1-7.

Now assuming you are looping through the days/weeks to create a month view in your calendar, you could compose a query like this:

SELECT EV . *
FROM `events` AS EV
JOIN `events_meta` EM1 ON EM1.event_id = EV.id
AND EM1.meta_key = 'repeat_start'
LEFT JOIN `events_meta` EM2 ON EM2.meta_key = CONCAT( 'repeat_year_', EM1.id )
LEFT JOIN `events_meta` EM3 ON EM3.meta_key = CONCAT( 'repeat_month_', EM1.id )
LEFT JOIN `events_meta` EM4 ON EM4.meta_key = CONCAT( 'repeat_week_im_', EM1.id )
LEFT JOIN `events_meta` EM5 ON EM5.meta_key = CONCAT( 'repeat_weekday_', EM1.id )
WHERE (
  EM2.meta_value =2011
  OR EM2.meta_value = '*'
)
AND (
  EM3.meta_value =4
  OR EM3.meta_value = '*'
)
AND (
  EM4.meta_value =2
  OR EM4.meta_value = '*'
)
AND (
  EM5.meta_value =6
  OR EM5.meta_value = '*'
)
AND EM1.meta_value >= {current_timestamp}
LIMIT 0 , 30

This combined with the above method could be combined to cover most repeating/recurring event patterns. If I've missed anything please leave a comment.

What is the best way to represent Recurring Events in database?

The sysjobs, sysjobsschedule and sysschedules tables in SQL Server does a pretty good job of this. I wouldn't reinvent the wheel, I'd just copy their design.

Here are some of the important fields from sysschedules

freq_type

How frequently a job runs for this schedule.

1 = One time only

4 = Daily

8 = Weekly

16 = Monthly

32 = Monthly, relative to freq_interval

64 = Runs when the SQL Server Agent service starts

128 = Runs when the computer is idle

freq_interval

Days that the job is executed. Depends on the value of freq_type. The default value is 0, which indicates that freq_interval is unused.
Value of freq_type Effect on freq_interval

1 (once) freq_interval is unused (0)

4 (daily) Every freq_interval days

8 (weekly) freq_interval is one or more of the following: 1 = Sunday 2 = Monday 4 = Tuesday 8 = Wednesday 16 = Thursday 32 = Friday 64 = Saturday

16 (monthly) On the freq_interval day of the month

32 (monthly, relative) freq_interval is one of the following: 1 = Sunday 2 = Monday 3 = Tuesday 4 = Wednesday 5 = Thursday 6 = Friday 7 = Saturday 8 = Day 9 = Weekday 10 = Weekend day

64 (starts when SQL Server Agent service starts) freq_interval is unused (0)

128 (runs when computer is idle) freq_interval is unused (0)

freq_subday_type

Units for the freq_subday_interval. Can be one of the following values:
Value Description (unit)

1 At the specified time

2 Seconds

4 Minutes

8 Hours

freq_subday_interval

Number of freq_subday_type periods to occur between each execution of the job.

freq_relative_interval

When freq_interval occurs in each month, if freq_interval is 32 (monthly relative). Can be one of the following values:

0 = freq_relative_interval is unused

1 = First

2 = Second

4 = Third

8 = Fourth

16 = Last

freq_recurrence_factor

Number of weeks or months between the scheduled execution of a job. freq_recurrence_factor is used only if freq_type is 8, 16, or 32. If this column contains 0, freq_recurrence_factor is unused.

---

Related Topics