Efficiently storing irregular/repeating intervals (think calendar/events)

1. Always look for standards that support your requirements. What is a standard that supports recurring calendar events? ICalendar RRULEs:

   http://en.wikipedia.org/wiki/ICalendar

   http://www.kanzaki.com/docs/ical/rrule.html

   You can either store the rule as plain text and parse it as needed, or use a database schema for them:

   SQL Schema: https://stackoverflow.com/questions/1054201/ical-field-list-for-database-schema-based-on-ical-standard/1397019#1397019

   Perl: http://search.cpan.org/~rfrankel/iCal-Parser-1.16/lib/iCal/Parser.pm

   PostgreSQL-specific: http://svn.expressolivre.org/contrib/davical/dba/rrule_functions-8.1.sql

2. Performance-wise, you can use Materialized Views to calculate events, say one month in advance and one month prior (if required):

   https://stackoverflow.com/questions/4239871/when-building-a-calendar-app-should-i-store-dates-or-recurrence-rules-in-my-dat/10151804#10151804

Additional information : http://www.vertabelo.com/blog/technical-articles/again-and-again-managing-recurring-events-in-a-data-model This article explains each and every aspect of repeated events. Please read it before coming to any conclusions.

As Zoltan pointed out, unless you have MANY millions of rows, I don't see a scaling issue. There are also many libraries for scheduling things such as Quartz on Java for example. These will store the recurring schedule as a cron-like expression. Because your example above has a flaw, if the recurrence is every Monday, then it's 52 x number of years the service will go for.

So you can store a date, or a recurrence pattern.

Make a different table with the a part for te message id

table 1: id - message

table 2: id - date

See if you can somehow arrange the "table 2" by its date, and format the date as following:

min - hour - dom - mon - wom - dow (dom = day of month, mon = month, wom = week of month, dow = day of week)

You can use numbers as "12", or wildcards as "*", maybe ad a first as "<" or a last as ">".

Then you query the second table first, you may also use a 2nd and 3rd table where the 2nd talbe only have single use messages, and the 3rd table have repeating instructions. you can then also query the 3rd table to add messages to the 2nd table, but only fill up the second table up for a month or year.

This inspiration came from the way crontab schedules work

*     *     *   *    *        command to be executed
-     -     -   -    -
|     |     |   |    |
|     |     |   |    +----- day of week (0 - 6) (Sunday=0)
|     |     |   +------- month (1 - 12)
|     |     +--------- day of        month (1 - 31)
|     +----------- hour (0 - 23)
+------------- min (0 - 59)