Data structure for storing recurring events?

Question

I'm looking for a data structure pattern for storing recurring events, but everything I came up with would result in a high number of special case handling or user input and data retrieval are overly complex. (I get the distinct feeling, that I haven't understand the problem domain well enough to do this.)

How can I store Outlook-style recurring events?

• Every day at 8am
• Every first tuesday in a month
• Every December 1st for three years
• Every two hours for a week
• ...

Answer 1

There are various papers describing data structures and algorithms for this use case. In addition you can see the code or descriptions of open source implementation of crontab and of Quartz (Java) or Quartz.NET (.NET).

This is one such paper

http://portal.acm.org/citation.cfm?id=359763.359801&coll=ACM&dl=ACM&CFID=63647367&CFTOKEN=55814330

For example, cron stores the information like this (* means every, so a * under month means every month)

.---------------- minute (0 - 59) 
|  .------------- hour (0 - 23)
|  |  .---------- day of month (1 - 31)
|  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ... 
|  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7)  OR sun,mon,tue,wed,thu,fri,sat 
|  |  |  |  |
*  *  *  *  * 

There are several special entries, most of which are just shortcuts, 
that can be used instead of specifying the full cron entry:

Entry      Description                 Equivalent To
@reboot    Run once, at startup.       None
@yearly    Run once a year             0 0 1 1 *
@annually  (same as @yearly)           0 0 1 1 *
@monthly   Run once a month            0 0 1 * *
@weekly    Run once a week             0 0 * * 0
@daily     Run once a day              0 0 * * *
@midnight  (same as @daily)            0 0 * * *
@hourly    Run once an hour            0 * * * *

Answer 2

Support the standard iCalendar Event types

The IETF put some thought into this when they created the Internet Calendaring and Scheduling Core Object Specification, better known as iCalendar.

The specification includes event recurrence.

As an added bonus, your database will be amenable to sharing data with other iCalendar compatible data sources such as Google and Apple calendars.

https://www.rfc-editor.org/rfc/rfc5545

Answer 3

Event:

StartDate
EndDate (calculated on change of NumberOfOccurances)
NumberOfOccurances (calculated on change of EndDate )
Frequency e.g. 1/2hrs, 1/month, 1/day, ....
CorrectionFunction e.g. first Tuesday, last Sunday, ...

bool OccuresOn(day)
Date NextOccurance(date)

Answer 4

Here is my take -- please let me know if I am missing anything:

Based on the Outlook Recurrence options, you have a table with the regular necessary fields:

FieldName       DataType      Sample Data
ID              int           primary key
EventID         int           foreign key (to EventID from Event Table)
StartTime       DateTime      8:00 AM
EndTime         DateTime      8:30 AM
Duration        int           30 (minutes)
StartDate       DateTime      01/25/2014
EndBy           DateTime      01/25/2024
NoEndDate       bit           False
NumOccurrences  int           10
RecurrenceType  int           ****See below for instructions on how to use these last 6 fields  
Int1            int           
Int2            int
Int3            int
String1         nvarchar(50)
IntYears        int

Here is where the magic happens. this logic only requires 4 integers and one string.

The month of year (1 = Jan, 12 = Dec), 
The day of the month (1 = the 1st, 31 = 31st), 
Day of the week (0 = Sunday, 1=Monday, 6= Saturday), 
Week of the month (1 = first, 4 = forth, 5 = last),
Yearly reocurrence ( 1=1,2=2)   
When multiple days can be selected I use a comma delimited string (1,3,5 = Monday, Wed, Friday)

I enter the 3 integers in the order they appear in the outlook Appointment Recurrence scheduler, this saves extra feilds, logic, annoyance. *If you open to the outlook appt scheduler, this will be slightly easier to follow:

The RecurrenceType field can be any of the 7 following choices 

(There are 2 options for daily, Monthly and Yearly, and one option for weekly):

10 = Daily (Every `Int1` day(s) )    
            Every     4  day(s)
11 = Daily (Every Weekday)  -- no variables needed 
            Every Weekday (MTWTF)
20 = Weekly (Recur every `Int1` week(s) on: `String1`
             Recur every     3  week(s) on  Monday, Wednesday, Friday   
(`String1` will be a list of days selected (0=Sunday, 1=Monday, 2=Tuesday... 7=Saturday) so for (Mon, Wed, Fri) String1 would hold "1,3,5". You would parse this on the code side to pull the actual days.)
30 = Monthly (Day `Int1` of every `int2' month(s) 
              Day    28  of every     2  month(s)
31 = Monthly (The `Int1`  `Int2` of every `Int3` month(s) 
              The forth Tuesday  of every     1  month(s)
40 = Yearly (Recur every `intYears` year(s) On `Int1` `Int2`) -- 
             Recur every         1  year(s) on   Jan   28th
41 = Yearly (Recur every `intYears` year(s) on the `Int1` `Int2` of `Int3`) -- 
             Recur every         1  year(s) on the forth Tuesday of January

The code to pull or save the reocurrence becomes fairly simple

if (RecurrenceType = 10 )
    Every `int1` days
if (RecurrenceType = 11)
    Every Weekday
if (RecurrenceType = 20)
    Every `int1 weeks on 
    parse `string1` and populate checkboxes for Mon, Tues, ...
if (RecurrenceType = 30)
    `int1 day of every `int2` month

etc...

I hope I am explaining this thoroughly enough. Let me know if anything is unclear or if it will not work. I am building this for a current app. Thanks to all.