[database-design] Calendar Recurring/Repeating Events - Best Storage Method

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.

This question is related to database-design calendar

The answer is


@Rogue Coder

This is great!

You could simply use the modulo operation (MOD or % in mysql) to make your code simple at the end:

Instead of:

AND (
    ( CASE ( 1299132000 - EM1.`meta_value` )
        WHEN 0
          THEN 1
        ELSE ( 1299132000 - EM1.`meta_value` )
      END
    ) / EM2.`meta_value`
) = 1

Do:

$current_timestamp = 1299132000 ;

AND ( ('$current_timestamp' - EM1.`meta_value` ) MOD EM2.`meta_value`) = 1")

To take this further, one could include events that do not recur for ever.

Something like "repeat_interval_1_end" to denote the date of the last "repeat_interval_1" could be added. This however, makes the query more complicated and I can't really figure out how to do this ...

Maybe someone could help!


Sounds very much like MySQL events that are stored in system tables. You can look at the structure and figure out which columns are not needed:

   EVENT_CATALOG: NULL
    EVENT_SCHEMA: myschema
      EVENT_NAME: e_store_ts
         DEFINER: jon@ghidora
      EVENT_BODY: SQL
EVENT_DEFINITION: INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP())
      EVENT_TYPE: RECURRING
      EXECUTE_AT: NULL
  INTERVAL_VALUE: 5
  INTERVAL_FIELD: SECOND
        SQL_MODE: NULL
          STARTS: 0000-00-00 00:00:00
            ENDS: 0000-00-00 00:00:00
          STATUS: ENABLED
   ON_COMPLETION: NOT PRESERVE
         CREATED: 2006-02-09 22:36:06
    LAST_ALTERED: 2006-02-09 22:36:06
   LAST_EXECUTED: NULL
   EVENT_COMMENT:

Enhancement: replace timestamp with date

As a small enhancement to the accepted answer that was subsequently refined by ahoffner - it is possible to use a date format rather than timestamp. The advantages are:

  1. readable dates in the database
  2. no issue with the years > 2038 and timestamp
  3. removes need to be careful with timestamps that are based on seasonally adjusted dates i.e. in the UK 28th June starts one hour earlier than 28th December so deriving a timestamp from a date can break the recursion algorithm.

to do this, change the DB repeat_start to be stored as type 'date' and repeat_interval now hold days rather than seconds. i.e. 7 for a repeat of 7 days.

change the sql line:

WHERE (( 1370563200 - repeat_start) % repeat_interval = 0 )

to:

WHERE ( DATEDIFF( '2013-6-7', repeat_start ) % repeat_interval = 0)

everything else remains the same. Simples!


The two examples you've given are very simple; they can be represented as a simple interval (the first being four days, the second being 14 days). How you model this will depend entirely on the complexity of your recurrences. If what you have above is truly that simple, then store a start date and the number of days in the repeat interval.

If, however, you need to support things like

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

Then that's a much more complex pattern.


While the proposed solutions work, I was trying to implement with Full Calendar and it would require over 90 database calls for each view (as it loads current, previous, and next month), which, I wasn't too thrilled about.

I found an recursion library https://github.com/tplaner/When where you simply store the rules in the database and one query to pull all the relevant rules.

Hopefully this will help someone else, as I spent so many hours trying to find a good solution.

Edit: This Library is for PHP


For all of you who are interested in this, now you can just copy and paste to get started within minutes. I took the advice in the comments as well as I could. Let me know if I'm missing something.

"COMPLEX VERSION":

events

+----------+----------------+
| ID       | NAME           | 
+----------+----------------+
| 1        | Sample event 1 |
| 2        | Second  event  |
| 3        | Third event    |
+----------+----------------+

events_meta

+----+----------+--------------+------------------+-------------+--------------+------------+-------------+----------------+
| ID | event_id | repeat_start | repeat_interval  | repeat_year | repeat_month | repeat_day | repeat_week | repeat_weekday |
+----+----------+--------------+------------------+-------------+--------------+------------+-------------+----------------+
| 1  | 1        | 2014-07-04   | 7                | NULL        | NULL         | NULL       | NULL        | NULL           |
| 2  | 2        | 2014-06-26   | NULL             | 2014        | *            | *          | 2           | 5              |
| 3  | 3        | 2014-07-04   | NULL             | *           | *            | *          | *           | 5              |
+----+----------+--------------+------------------+-------------+--------------+------------+-------------+----------------+

SQL code:

CREATE TABLE IF NOT EXISTS `events` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `events`
--

INSERT INTO `events` (`ID`, `NAME`) VALUES
(1, 'Sample event'),
(2, 'Another event'),
(3, 'Third event...');

CREATE TABLE IF NOT EXISTS `events_meta` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `event_id` int(11) NOT NULL,
  `repeat_start` date NOT NULL,
  `repeat_interval` varchar(255) NOT NULL,
  `repeat_year` varchar(255) NOT NULL,
  `repeat_month` varchar(255) NOT NULL,
  `repeat_day` varchar(255) NOT NULL,
  `repeat_week` varchar(255) NOT NULL,
  `repeat_weekday` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `events_meta`
--

INSERT INTO `events_meta` (`ID`, `event_id`, `repeat_start`, `repeat_interval`, `repeat_year`, `repeat_month`, `repeat_day`, `repeat_week`, `repeat_weekday`) VALUES
(1, 1, '2014-07-04', '7', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL'),
(2, 2, '2014-06-26', 'NULL', '2014', '*', '*', '2', '5'),
(3, 3, '2014-07-04', 'NULL', '*', '*', '*', '*', '1');

also available as MySQL export (for easy access)

PHP example code index.php:

<?php
    require 'connect.php';    

    $now = strtotime("yesterday");

    $pushToFirst = -11;
    for($i = $pushToFirst; $i < $pushToFirst+30; $i++)
    {
        $now = strtotime("+".$i." day");
        $year = date("Y", $now);
        $month = date("m", $now);
        $day = date("d", $now);
        $nowString = $year . "-" . $month . "-" . $day;
        $week = (int) ((date('d', $now) - 1) / 7) + 1;
        $weekday = date("N", $now);

        echo $nowString . "<br />";
        echo $week . " " . $weekday . "<br />";



        $sql = "SELECT EV.*
                FROM `events` EV
                RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
                WHERE ( DATEDIFF( '$nowString', repeat_start ) % repeat_interval = 0 )
                OR ( 
                    (repeat_year = $year OR repeat_year = '*' )
                    AND
                    (repeat_month = $month OR repeat_month = '*' )
                    AND
                    (repeat_day = $day OR repeat_day = '*' )
                    AND
                    (repeat_week = $week OR repeat_week = '*' )
                    AND
                    (repeat_weekday = $weekday OR repeat_weekday = '*' )
                    AND repeat_start <= DATE('$nowString')
                )";
        foreach ($dbConnect->query($sql) as $row) {
            print $row['ID'] . "\t";
            print $row['NAME'] . "<br />";
        }

        echo "<br /><br /><br />";
    }
?>

PHP example code connect.php:

<?
// ----------------------------------------------------------------------------------------------------
//                                       Connecting to database
// ----------------------------------------------------------------------------------------------------
// Database variables
$username = "";
$password = "";
$hostname = ""; 
$database = ""; 

// Try to connect to database and set charset to UTF8
try {
    $dbConnect = new PDO("mysql:host=$hostname;dbname=$database;charset=utf8", $username, $password);
    $dbConnect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}
// ----------------------------------------------------------------------------------------------------
//                                      / Connecting to database
// ----------------------------------------------------------------------------------------------------
?>

Also the php code is available here (for better readability):
index.php
and
connect.php
Now setting this up should take you minutes. Not hours. :)


I would follow this guide: https://github.com/bmoeskau/Extensible/blob/master/recurrence-overview.md

Also make sure you use the iCal format so not to reinvent the wheel and remember Rule #0: Do NOT store individual recurring event instances as rows in your database!


I developed an esoteric programming language just for this case. The best part about it is that it is schema less and platform independent. You just have to write a selector program, for your schedule, syntax of which is constrained by the set of rules described here -

https://github.com/tusharmath/sheql/wiki/Rules

The rules are extendible and you can add any sort of customization based on the kind of repetition logic you want to perform, without worrying about schema migrations etc.

This is a completely different approach and might have some disadvantages of its own.


RRULE standard is built for exactly this requirement i.e. saving and understanding recurrences. Microsoft and google both use it in their calendar events. Please go through this document for more details. https://icalendar.org/iCalendar-RFC-5545/3-8-5-3-recurrence-rule.html


Why not use a mechanism similar to Apache cron jobs? http://en.wikipedia.org/wiki/Cron

For calendar\scheduling I'd use slightly different values for "bits" to accommodate standard calendar reoccurence events - instead of [day of week (0 - 7), month (1 - 12), day of month (1 - 31), hour (0 - 23), min (0 - 59)]

-- I'd use something like [Year (repeat every N years), month (1 - 12), day of month (1 - 31), week of month (1-5), day of week (0 - 7)]

Hope this helps.


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!