Calendar Recurring/Repeating Events - Best Storage Method

Question

I am building a custom events system, and if you have a repeating event that looks like this:

Event A repeats every 4 days starting on March 3, 2011

or

Event B repeats every 2 weeks on Tuesday starting on March 1, 2011

How can I store that in a Database in a way that would make it simple to lookup. I don't want performance issues if there are a large number of events, and I have to go through each and every one when rendering the calendar.

Answer 1

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.

Answer 2

While the currently accepted answer was a huge help to me, I wanted to share some useful modifications that simplify the queries and also increase performance.

---

"Simple" Repeat Events

To handle events which recur at regular intervals, such as:

Repeat every other day  

or

Repeat every week on Tuesday  

You should create 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      repeat_start       repeat_interval 1     1             1369008000         604800            -- Repeats every Monday after May 20th 2013 1     1             1369008000         604800            -- Also repeats every Friday after May 20th 2013 

With repeat_start being a unix timestamp date with no time (1369008000 corresponds to May 20th 2013) , and repeat_interval an amount in seconds between intervals (604800 is 7 days).

By looping over each day in the calendar you can get repeat events using this simple query:

SELECT EV.* FROM `events` EV RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id` WHERE  (( 1299736800 - repeat_start) % repeat_interval = 0 ) 

Just substitute in the unix-timestamp (1299736800) for each date in your calendar.

Note the use of the modulo (% sign). This symbol is like regular division, but returns the ''remainder'' instead of the quotient, and as such is 0 whenever the current date is an exact multiple of the repeat_interval from the repeat_start.

Performance Comparison

This is significantly faster than the previously suggested "meta_keys"-based answer, which was as follows:

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 

If you run EXPLAIN this query, you'll note that it required the use of a join buffer:

+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------------+ | id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra                          | +----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------------+ |  1 | SIMPLE      | EM1   | ALL    | NULL          | NULL    | NULL    | NULL             |    2 | Using where                    | |  1 | SIMPLE      | EV    | eq_ref | PRIMARY       | PRIMARY | 4       | bcs.EM1.event_id |    1 |                                | |  1 | SIMPLE      | EM2   | ALL    | NULL          | NULL    | NULL    | NULL             |    2 | Using where; Using join buffer | +----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------------+ 

The solution with 1 join above requires no such buffer.

---

"Complex" Patterns

You can add support for more complex types to support these types of repeat rules:

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

or

Event A repeats second Friday of the month starting on March 11, 2011 

Your events table can look exactly the same:

ID    NAME 1     Sample Event 2     Another Event 

Then to add support for these complex rules add columns to events_meta like so:

ID    event_id      repeat_start       repeat_interval    repeat_year    repeat_month    repeat_day    repeat_week    repeat_weekday 1     1             1369008000         604800             NULL           NULL            NULL          NULL           NULL             -- Repeats every Monday after May 20, 2013 1     1             1368144000         604800             NULL           NULL            NULL          NULL           NULL             -- Repeats every Friday after May 10, 2013 2     2             1369008000         NULL               2013           *               *             2              5                -- Repeats on Friday of the 2nd week in every month     

Note that you simply need to either specify a repeat_interval or a set of repeat_year, repeat_month, repeat_day, repeat_week, and repeat_weekday data.

This makes selection of both types simultaneously very simple. Just loop through each day and fill in the correct values, (1370563200 for June 7th 2013, and then the year, month, day, week number and weekday as follows):

SELECT EV.* FROM `events` EV RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id` WHERE  (( 1370563200 - repeat_start) % repeat_interval = 0 )   OR (      (repeat_year = 2013 OR repeat_year = '*' )     AND     (repeat_month = 6 OR repeat_month = '*' )     AND     (repeat_day = 7 OR repeat_day = '*' )     AND     (repeat_week = 2 OR repeat_week = '*' )     AND     (repeat_weekday = 5 OR repeat_weekday = '*' )     AND repeat_start <= 1370563200   ) 

This returns all events that repeat on the Friday of the 2nd week, as well as any events that repeat every Friday, so it returns both event ID 1 and 2:

ID    NAME 1     Sample Event 2     Another Event 

*Sidenote in the above SQL I used PHP Date's default weekday indexes, so "5" for Friday

---

Hope this helps others as much as the original answer helped me!