December 17, 2003 at 3:23 pm
I have a custom application that will access a SQL Server database to retrieve calendar events via stored procedures. Since I am primarily an application developer (i.e. not a SQL Server guru) I am having some difficulty trying to figure out how best to store these recurring calendar events. In case the concept of recurring meetings isn’t familiar to you, I am trying mimic the functionality that MS Outlook performs when it creates/manages recurring meetings.
I have been researching this today and found a variety of strategies that all seemed to boil down to one of three methods.
Method 1: “Virtual Events”
With this method, the application would store one ‘master’ event (i.e. the initial event) and also store the recurring pattern. From these two pieces of data, the recurring pattern would be applied to the initial event to calculate future event dates via stored procedure “on the fly”. This method would trade database size for application speed (and query complexity). Some of the drawbacks are that the virtual events (i.e. the returned dataset) would need to be calculated many times (i.e. for each user each time the application is launched). Some side effects are that it greatly increases complexity for “Jump to [some future date]” type functions. Also, since the calendar events don’t actually exist in the database, any exceptions to the pattern must be physically stored. So if a user has a recurring meeting with 10 instances and one (say the 5th one) needs to be changed, the details for that particular meeting would have to be stored since it is part of the sequence, but deviates from the pattern.
Method 2: “Immediate data population”
The recurring pattern is applied to the initial event and all subsequent events are created and stored in the database. This is the opposite of the above method 1. The pros here are that changing or modifying calendar entries becomes trivial. Query complexity is very low. The sequence can be easily maintained even if deviations to the pattern are introduced since each event has its own details stored. The drawbacks are obviously this will obviously dramatically increase the size of the database (especially if there isn’t a practical limit on how far into the future the events can be created).
Method 3: “Pre-calculate and store the date in a separate table”
This is an interesting hybrid that I found in a post on Google. In this method, the initial event would be stored along with the recurring pattern (just like method 1) as well as the pre-calculated date of all the instances of the recurring event (stored in a secondary table). So the dates are “known” (and can easily be changed if the user changes the recurrence pattern) but not all of event details. In theory this will keep database size fairly low application speed reasonable by allowing the application to quickly recognize that a calendar event exists for a given day. The application could then run another query to get the details (i.e. StartTime, EndTime, Description, Owner, Attendees, etc.) and create a dataset to bind to the calendar control.
I don’t know if this is a common problem that has been solved for by other means, but if so please let me know. As I mentioned before I am hoping someone much more familiar with the complexities of data storage can shed some light on this.
All opinions are welcome.
Thanks,
Russell Brackett
Russell Brackett
December 22, 2003 at 8:00 am
This was removed by the editor as SPAM
March 11, 2004 at 3:25 am
Well - here's my thoughts (although I haven't thought everything through that well!!).
I'd have a table to store the events. I would have a separate (but linked) table which can have an optional entry for the recurrance information.
When creating a recurring event, I'd create all the events in the events table. The first event would also have an entry with the details of the recurring pattern in the second table. All subsequent events would store the ID of the first event.
You can change individual events easily. To change the entire series, you could change all the associated records easily with update queries using the ID of the first, 'master' item.
This system would work well in an environment where viewing the calendar happens more frequently than editing it.
Anyway - like I say - I haven't thought that through fully - but it's my initial reaction! Hope it helps!
- Chris
PS. One other thought is how well this would work for events that have no end date!?!?!
May 19, 2005 at 9:44 am
I also have a custom application that needs to access a SQL Server database to retrieve calendar events.
To deal with recurring events, I would like to mimic the functionality that you can find in Outlook.
Before I dive in and create an object model for that, I wanted to ask around and see if and how others have solved this problem.
Any suggestions?
Thanks,
jmr
May 19, 2005 at 11:43 am
If it's an application that I'm writing, I tend to wrap everything in stored procedures.
I'd use a datamodel to define events and recurrence and also an actual date/what's going on table.
In that manner, you can use a stored procedure to "get_calendar" for a specific date range. That procedure can both populate & clean up entries in the actual event calendar table based on the event definitions. The work to create future event entries is going to have to happen anyway, why not encapsulate the retrieval of it with a process that maintains it. Then you only will generate an entry for a range that is actually looked at.
May 19, 2005 at 3:24 pm
I've been digging a bit further...
I think that the scheduling pattern used by outlook conforms to a standard called RFC2445 (iCalendar).
What I'm looking for is a set of tables/stored procedures that are set up to store and retrieve information based on this standard.
Here is a link to a document entitled:
Internet Calendaring and Scheduling Core Object Specification
(iCalendar)
http://www.ietf.org/rfc/rfc2445.txt
So if anyone has any information regarding tables/stored procs that can contain this information, please let me know!!
Thanks,
-jmr
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply