Page 1 of 4
A model with changing parameters (recurring event calendar)
Posted: Tue Apr 10, 2007 7:03 pm
by Luke
I am building an events calendar system that will be integrated into several other applications I've built. It is modeled after both iCal and Mozilla Sunbird. When you add an event, you can select whether the even is "recurring", and how that event "recurs". For reference, check out the "add event" menu on Mozilla Sunbird. Also, take a look
here (ignore the silly names of things).
My question is that depending on what kind of recurring event you choose (daily, monthly, weekly, yearly), you could end up with different parameters. An even that recurs daily would only require the interval (how often to repeat) and the "repeat until" value (a certain amount of occurances, until a certain date, or forever, and here if a user selects an option other than "forever", they would need to fill out an additional field (date/number of occurances)). An event that recurs monthly may require more parameters like "repeat on the 4th of every month" or "repeat on the last day of every month".
How would you guys recommend storing this data? Right now I have two columns in my database: recurring_type & recurring_params, but this method doesn't really seem to work, and when I think about trying to display this data later on (how to display recurring events), this method seems like it would be difficult to work with. Thanks for your advice, and let me know if this is confusing and I'll try and clear things up.
Posted: Tue Apr 10, 2007 7:40 pm
by Ambush Commander
Your dilemma is very clear. Here's my best stab at it:
Three columns: start timestamp, end timestamp, and interval timestamp. In this case, timestamp does not mean a Unix timestamp, but a full YYYY-MM-DDTHH:MM:SS stamp. Examples:
- An even that recurs daily would only require the interval (how often to repeat): not really, the interval is already one day
- and the "repeat until" value (a certain amount of occurances,: this could be implemented using a trivial server-side calculation to generate the end timestamp. Basically, today + number of occurences * interval
- until a certain date: easy with suggested implementation
- or forever: perhaps a magic value, or a date ridiculously far in the future
- and here if a user selects an option other than "forever", they would need to fill out an additional field (date/number of occurances)).: More of a client-side issue, nothing a smidgen of JavaScript can't fix. You could probably cram them all into one field
- An event that recurs monthly may require more parameters like "repeat on the 4th of every month" or "repeat on the last day of every month". - simply adjust the start timestamp
Posted: Wed Apr 11, 2007 9:32 am
by Luke
Ambush, that's genius! The only part I don't understand is this:
An event that recurs monthly may require more parameters like "repeat on the 4th of every month" or "repeat on the last day of every month". - simply adjust the start timestamp
How would adjusting the start timestamp let the calendar renderer know to repeat this event on the 31st of every month as opposed to the "last day" of every month?
Posted: Wed Apr 11, 2007 2:07 pm
by Ambush Commander
Ah, I didn't realize that problem. You'd probably have to use a magic number or something to get that functionality. Sorry.
Posted: Wed Apr 11, 2007 2:45 pm
by Luke
ok, thanks. Another question: if I set the column type as datetime, I can only enter values that fit the "0000-00-00 00:00:00" mold, so how, in this case, would I use "magic numbers" (I've never used them before other than using -1 for an anonymous user)? Would I need to use a different kind of column type?
Posted: Wed Apr 11, 2007 2:54 pm
by TheMoose
Use just a varchar (or any variation of a text column), but limit its format (code-side) to the standard date format of "0000-00-00 00:00:00"
You would call this column "interval" or whatever, and have its value be:
"0000-00-04 00:00:00" if you want the event to occur on the 4th of each month. This way you could look also set up recurring events for the 4th day of each month at noon (0000-00-04 12:00:00) or the 4th day of the 7th month at noon (0000-07-04 12:00:00).
It'd require a bit of code to handle it, since it's an illegal date.
That's just my idea.
Posted: Wed Apr 11, 2007 3:01 pm
by Ambush Commander
You probably can't use DATETIME: at the very least, it wouldn't work for interval since the valid range is only '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
So, that brings up a few more practical problems:
1. If we use an opaque VARCHAR field for storing this data, querying for dates between other dates becomes more difficult. However, using string comparison functions, we may able to get things to work, as the ordering of the proposed format will always ASCII sort into chronological order. Alternatively, you normalize the database, splitting each up into their own column, i.e. start_year, start_month, start_day, start_hour... at which point you would probably want a dedicated "Datetime" table.
2. How should magic numbers be managed? Sure, last day of the month makes for a fine edge case, but what about last Saturday of the month? Second to third last day of the month? I'm not convinced that a magic number would be a scalable way of doing things. Perhaps we could use negative numbers, i.e. 1999-05--1 would indicate that the date was actually 1999-04-29, but collation is still messy
3. How can all events within a certain time period be queried? This compact syntax does not suggest any obvious ways of determining this, besides selecting all start -> end scheduling times that cover the time period, and then manually calculating whether or not the interval for that recurring event causes it to fall within it. A caching mechanism where dates that are normally recurrent are inputted as single, normal events might work nicely.
Also, you probably want users to be able to say, "Every Monday for the next six months, except Monday the 5th on July 22nd." Let's first figure out what features you want to support.
Posted: Wed Apr 11, 2007 3:22 pm
by TheMoose
1: Would you ever really need to query off the interval? I don't see this as a necessity of something like "I need all the events that are recurring between the first monday and the 15th of each month". If the need arises, then normalization is definitely necessary and I agree 100%.
2: It's as if there is going to be a huge library of "special characters". Maybe something similar to a RegEx pattern recognizer? An example pattern could be something like [*-*-* *:*:* 7] to signify every saturday (days are 1-7 Sunday-Saturday). It follows the DATETIME format of YYYY-MM-DD HH:MM:SS but adds weekday attribute at the end. Although I'm not exactly sure (haven't thought enough) how to handle such cases as "2nd to last saturday". I'm sure you could build it to be more like RegEx though (ie: {-1} to signify the last of the pattern).
3: See 1. I'm still thinking about this however, need more time to contemplate ideas so I'll edit/respond in a bit
Regarding your extra note, the RegEx style method could allow for the ! identifier (or any custom you want to signify a NOT value) to cancel out specific days, or periods of time.
It'll be a lot of work though to basically build a custom RegEx style date recognizer.
Posted: Wed Apr 11, 2007 3:36 pm
by Ambush Commander
Your indicator for day of the week just made me remember that this problem has been solved before. CRON!
You'll want day of month and day of week in the format too.
Posted: Wed Apr 11, 2007 3:55 pm
by TheMoose
Cron works, yes, but it actually runs scheduled events instead of just storing and displaying them on a calendar system (not too hip on Unix things, so I'm not sure exactly).
My understanding was this was merely for a calendar system and not actually running scripts.
Posted: Wed Apr 11, 2007 3:57 pm
by Ambush Commander
Yes, but they've already hammered out a system for specifying recurring events that works quite well. Grab and steal when you can!
Anyway, the day of month parameter should be sufficient for the rest of the edge cases: simply make -1 the last day of the month, and so forth.
Posted: Wed Apr 11, 2007 3:59 pm
by Luke
That reminds me! It's also been solved by the iCal format. They do this:
Father's day
Code: Select all
DTSTART;VALUE=DATE:17530617
RRULE:FREQ=MONTHLY;BYMONTH=6;BYDAY=3SU
April fool's day:
Code: Select all
DTSTART;VALUE=DATE:17530401
RRULE:FREQ=YEARLY;INTERVAL=1;
Last day of the month starting April 30th & repeat 5 times
Code: Select all
RRULE:FREQ=MONTHLY;COUNT=5;INTERVAL=1;BYMONTHDAY=-1
DTSTART;TZID=/mozilla.org/20050126_1/America/Dawson:20070430T140000
DTEND;TZID=/mozilla.org/20050126_1/America/Dawson:20070430T150000
Third thursday of the month starting April 19th 2007 and ending October 29th 2007
Code: Select all
RRULE:FREQ=MONTHLY;UNTIL=20071030T065959;INTERVAL=1;BYDAY=3TH
DTSTART;TZID=/mozilla.org/20050126_1/America/Dawson:20070419T140000
DTEND;TZID=/mozilla.org/20050126_1/America/Dawson:20070419T150000
So how would I do this same thing, but with database columns?

Posted: Wed Apr 11, 2007 4:08 pm
by Ambush Commander
It looks like they assign each event a number of rules, each of which have different parameters such as frequency, by month, by day, each with their own syntax. You really ought to check out the
RFC to see how it was done.
Actually, looks like it would be simple, but tedious, to map to a database. Each recurring event is registered in an RecurringEvents table. Using a foreign key association, you tie rules to these events in a EventRules table. The EventRules table can contain all the possible parameters, or have be the subject of another foreign key association with a EventParameters table.
I haven't the foggiest how you would find dates within certain areas, however, without grabbing every single event defined.
Posted: Wed Apr 11, 2007 4:11 pm
by Luke
Each recurring event is registered in an RecurringEvents table. Using a foreign key association, you tie rules to these events in a EventRules table.
yea that seems logical
I haven't the foggiest how you would find dates within certain areas, however, without grabbing every single event defined.
What do you mean "dates within certain areas"?
Posted: Wed Apr 11, 2007 4:13 pm
by Ambush Commander
Suppose I wanted to print the calendar for November. Logically speaking, I'd like to find all events that occur during November. The high extensibility of iCal doesn't make it immediately obvious how to calculate which events are in November. I suppose that there are some heuristics (possibly in the RFC) one could use to get the events with minimal database querying, but it's tough without having read the RFC yet.