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.
Best Answer
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:And a table called
events_meta
like this: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:
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:
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:
And a table called
events_meta
like this: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:
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.