Calendar Recurring/Repeating Events - Best Storage Method
Database DesignCalendarDatabase Design Problem Overview
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.
Database Design Solutions
Solution 1 - Database Design
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.
Solution 2 - Database Design
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!
Solution 3 - Database Design
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:
- readable dates in the database
- no issue with the years > 2038 and timestamp
- 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!
Solution 4 - Database Design
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!
Solution 5 - Database Design
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. :)
Solution 6 - Database Design
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.
Solution 7 - Database Design
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
Solution 8 - Database Design
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
Solution 9 - Database Design
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.
Solution 10 - Database Design
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:
Solution 11 - Database Design
@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!
Solution 12 - Database Design
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.