A model with changing parameters (recurring event calendar)
Moderator: General Moderators
A model with changing parameters (recurring event calendar)
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.
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.
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
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
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
Ambush, that's genius! The only part I don't understand is this:
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?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
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
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?
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.
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.
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
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.
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.
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.
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.
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
That reminds me! It's also been solved by the iCal format. They do this:
Father's day
April fool's day:
Last day of the month starting April 30th & repeat 5 times
Third thursday of the month starting April 19th 2007 and ending October 29th 2007
So how would I do this same thing, but with database columns? 
Father's day
Code: Select all
DTSTART;VALUE=DATE:17530617
RRULE:FREQ=MONTHLY;BYMONTH=6;BYDAY=3SUCode: Select all
DTSTART;VALUE=DATE:17530401
RRULE:FREQ=YEARLY;INTERVAL=1;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:20070430T150000Code: 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- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
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.
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.
yea that seems logicalEach recurring event is registered in an RecurringEvents table. Using a foreign key association, you tie rules to these events in a EventRules table.
What do you mean "dates within certain areas"?I haven't the foggiest how you would find dates within certain areas, however, without grabbing every single event defined.
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
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.