Recurring calendar event design


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Recurring calendar event design

  1. #1
    Join Date
    Dec 2004
    Posts
    27

    Recurring calendar event design

    I'm having a calendar feature developed whereby users can add recurring events. These recurring events can have start and end dates, meaning they will recur only within a date range. For instance, user A creates an event that recurs every Tuesday, from 1/1/05 - 3/2/06.

    The developer has decided that it would be good to have another table that stores each individual recurring event in a separate record. His says this will help with the display of calendar events. His logic is, if the application has to dynamically iterate through every event record in the database and logically map out which days have recurrence, then display it in the calendar, that it would not be good for performance.

    My take though, is that if one recurring event can literally add in 1,000 records, then that's even worse.

    What are your thoughts on the proper way to implement this?

  2. #2
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    As always, there is no single proper way. I sympathize with the developer. Getting the actual events helps. On the other hand, if there are never any exceptions in the recurrence pattern, the duplicated storage is quite unnecessary.
    I'd start by synthesizing the events in an intermediary step, whether that be in a stored procedure or in client logic, such that when the calendar is presented it's handed a pre-cooked set.

    However, I doubt you'll see much difference in performance either way unless the calendar presents several years at once. I assume the database fetch uses where-clauses to weed out events outside the desired date window.

    Rune
    If you hit a brick wall, you didn't jump high enough!

  3. #3
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    This is often handled by a scheduled task that runs periodically (say, once daily) and generates discrete events for some period (say, one year) into the future. This allows the user to cancel or change an individual event without affecting the recurring schedule.

    Another option is not to generate discrete events, but to maintain a table containing exceptions to the recurring schedule. When the user views the event calendar, display events according to the schedule, then query the exceptions table to adjust the calendar for changed/deleted events.

    As Rune says, performance will likely not be an issue either way, as long as you only query for events within a specific period.
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  4. #4
    Join Date
    Dec 2004
    Posts
    27
    Originally posted by Rune Bivrin
    As always, there is no single proper way. I sympathize with the developer. Getting the actual events helps. On the other hand, if there are never any exceptions in the recurrence pattern, the duplicated storage is quite unnecessary.
    I'd start by synthesizing the events in an intermediary step, whether that be in a stored procedure or in client logic, such that when the calendar is presented it's handed a pre-cooked set.

    However, I doubt you'll see much difference in performance either way unless the calendar presents several years at once. I assume the database fetch uses where-clauses to weed out events outside the desired date window.

    Rune
    Well, I don't plan on having exceptions right now. What the developer told me was that using code to iterate through, to figure out which days have an event, will be very cpu intensive and will not be scalable. My reply was that adding hundreds of records (or even thousands) into a table, just for one recurring event, would probably be more disastrous... Especially since this will be a multi-user system where thousands of users may be adding to their own calendars.

    The recurring system allows for adding events like Outlook. Daily, weekly, monthly, and yearly. The options are pretty much the same.

    What are your thoughts?

  5. #5
    Join Date
    Aug 2004
    Location
    Neitherlands
    Posts
    64
    Originally posted by Skeptical
    ...using code to iterate through, to figure out which days have an event, will be very cpu intensive and will not be scalable.
    Agree.

    ...adding hundreds of records (or even thousands) into a table, just for one recurring event, would probably be more disastrous... Especially since this will be a multi-user system where thousands of users may be adding to their own calendars.
    Strongly Agree.

    Does your Database allows you to dinamically create temporary tables?, SQL Server does (I'm pretty sure others DBs also do this), and you can create a procedure that calendarizes an event in a defined lapsus, so you don't have phisical records for every recurrence, but you can still draw the ocurrences in your calendar.

    It's an intermediate approach between the developers position and yours...

    What do you think?
    Last edited by Sanderz; 12-17-2004 at 10:56 AM.

  6. #6
    Join Date
    Dec 2004
    Posts
    27
    Originally posted by Sanderz
    Does your Database allows you to dinamically create temporary tables?, SQL Server does (I'm pretty sure others DBs also do this), and you can create a procedure that calendarizes an event in a defined lapsus, so you don't have phisical records for every recurrence, but you can still draw the ocurrences in your calendar.

    It's an intermediate approach between the developers position and yours...

    What do you think?
    The application is running on dotnet + MS SQL, so temporary tables are supported.

    However, I'm not sure I'm getting you. Could you explain that a bit more? Thanks.

  7. #7
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    I would iterate through the recurring events, and generate discreet events in the interval presented, either in SQL, quite possibly using a temporary table, or in C#/VB code. either way, there won't be all that many events actually generated, so I think your programmer doesn't quite see the better algorithm. There's no need to iterate through every day. Just find the first occurence within the presented interval, and then add whatever timespan is between the recurrences until you get outside of the interval.

    For scalability, doing it in .NET is possibly better, since it's easier to scale up to more ASP servers.

    Rune
    If you hit a brick wall, you didn't jump high enough!

  8. #8
    Join Date
    Dec 2004
    Posts
    27
    Originally posted by Rune Bivrin
    I would iterate through the recurring events, and generate discreet events in the interval presented, either in SQL, quite possibly using a temporary table, or in C#/VB code. either way, there won't be all that many events actually generated, so I think your programmer doesn't quite see the better algorithm. There's no need to iterate through every day. Just find the first occurence within the presented interval, and then add whatever timespan is between the recurrences until you get outside of the interval.

    For scalability, doing it in .NET is possibly better, since it's easier to scale up to more ASP servers.

    Rune
    Thanks for the explanation. That's what I'd hoped for actually, but according to the programmers, figuring out which events fall on which days/months/years would place a huge burden on the server. What are your thoughts on this?

    For instance, users can set an event to recur every 4 days, starting from 2/3/05. If viewing the month of 10/05, wouldn't the code need to iterate through every month up to 10/05, since Februaray days can vary?

    Or how about, if a user creates an event to recur every 3 weeks, on Thursdays and Saturdays?

    Or every second Sunday of every 2 months?

  9. #9
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    Well, in SQL there's DATEADD, and in C# there's DateTime.Add. Either way, you'll know the interval between two successive events, in days, weeks, months or whatever.

    There is the issue of finding the first event that falls in the viewed time window. That will require a little logic, but if the recurrence interval is months, for example, you'll calculate the number of full months between the start of the event series and the start of the time window. Then you add that number of months + 1 to the start of the event series. You'll then have the first possible date, except it may fall outside of the time window.

    If the recurrence pattern is every monday and wednesday, that's effectively two series as far as this algorithm is concerned.

    Rune
    If you hit a brick wall, you didn't jump high enough!

  10. #10
    Join Date
    Dec 2004
    Posts
    1

    Have you come up with a solution

    Was wondering if you have come up with a solution to your question. I am trying to create an ASP page that does what Outlook does for Recurring events. I would really like to use SQL to perform all the logic behind this application. Please let me know if you or anyone else has done something similar.

  11. #11
    Join Date
    May 2006
    Location
    Southern California
    Posts
    1
    I'm late in the thread but am looking for an Outlook type recurring events solution in PHP/MySQL.
    I will have a table of "activities" and a second one for "events" (the particular dates on which an "activity" occurs. An "activity" table is not enough because a number of the columns within an "activity" can change (e.g., go dark, different "presenter", change of time, etc.) The "event" table will contain columns for the activity id, the date, an a boolean 'override' code. If the "override" in the event table is true, then look for the particular overrides in an override table. This will keep the event table rows short and override will only be searched if need be.
    I'l open to any assistance on the Outlook recurring part - PHP and/or MySQL.
    TIA

  12. #12
    Join Date
    Jul 2006
    Posts
    1
    I'm also looking for a similar solution. I'm working on an application that sends reminder emails for recurring events based on user input. For example, a user creates an event that recurs every Tuesday and sends a reminder XX days before. In this example, the duration is indefinite so adding specific dates to a table seems like a poor option. I'm trying to figure out the table design and logic to derive which events to send reminders for on a given day. Then I'll need to figure out a way to send the message. So far I'm stumped. Any ideas?

  13. #13
    Join Date
    Sep 2006
    Posts
    1

    try recurring monthly or yearly events

    i think writing a query to determine if weekly or daily events intersect with a search time frame (such as the current day your are displaying) might work because the intervals are constant (e.g, 60*60*24*7 seconds for a week) and you can do some sort of modulus math.

    HOWEVER there are two big issues to deal with here:
    1) Daylight Savings Time (DST). Any recurring event that straddles a time change will be hard to place correctly. PHP has functions to get a proper time stamp with DST correction, but it's the search part that kills.
    2) Monthly recurrence. This is the real killer. The interval month to month varies from 28 to 31 days, making a strict mathematical formula impossible (at least i think it is impossible).

    On the other hand, i'm not talking about display here, I'm talking about search. Been bonking my head on it for days.

  14. #14
    Join Date
    Jul 2007
    Posts
    1
    When I Create An Event And Click On "recurring Event", If I Choose "monthly Event" And "same Date Of Month", That Means That On Every, Let's Say The 12th Of The Month, On The 12th Of Every Month Between Now And The Recurring Event "end Date", That Event Should Appear On The Calendar. That Is Not Happening Now. Recurring Event Means That It Happens More Than Once.

  15. #15
    Join Date
    Mar 2008
    Posts
    1

    use the kiss principal

    To use a database for recurring events, I would have a field with the startdate, enddate, and lastrundate. That way if your startdate is 01/08, and you're set to recurr every day, you don't need to iterate all the way from 01/08, but rather from the lastrundate. This way you don't lose performance on date calculations, and don't fill your db with unnecessary data.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center