SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Zealot Oggle's Avatar
    Join Date
    Jul 2003
    Location
    Cali
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Recurring calendar event design. What are your thoughts?

    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 logic is
    that this will help with the display of calendar months. 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, that it would not
    be good for performance.

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

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

  2. #2
    SitePoint Guru gavwvin's Avatar
    Join Date
    Nov 2004
    Location
    Cornwall, UK
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I had this same problem. I came up with a solution that worked, although a bit messy. I had a column for whether it was an annual event or a one-off. Then on the page displaying it it would check through the databse and increment the year of past events that were annual. It works, but I would have liked a more elegant solution.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Oct 2004
    Location
    calpines california
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are there any free / gpl php scripts that have recurring events?

  5. #5
    SitePoint Zealot Oggle's Avatar
    Join Date
    Jul 2003
    Location
    Cali
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the link! I've gone through and read it, along with other linked pages.

    I'm thinking of going option 2, where each individual recurring event is stored as a separate record, with a main master record in another table, which stores the full range of the recurring event.

    However, from what I see in the database design by the developer so far, each recurring event record contains the subject and description. Since the description field allows for long text, this would lead to a huge database bloat. Wouldn't it be better to have the subject and description pulled from the master record instead?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    let's say you have a recurring weekly staff meeting

    on december 24 you want the description to say "annual christmas staff meeting"

    where does that description go?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot Oggle's Avatar
    Join Date
    Jul 2003
    Location
    Cali
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    let's say you have a recurring weekly staff meeting

    on december 24 you want the description to say "annual christmas staff meeting"

    where does that description go?
    Currently it's a free calendar system and I don't plan on giving users the ability to do that.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    oh, okay

    what was your question again?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot Oggle's Avatar
    Join Date
    Jul 2003
    Location
    Cali
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My question is, what is the best way to design the database and code? Using one record and have the code go through dynamically to figure out which days individual events land on, or store individual events in another table so that it loads faster (at the expense of adding a bunch of records per event)?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    there is no best way

    the way i would do it, each recurring event would have its individual dates generated into a table of events at the time it is added
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot Oggle's Avatar
    Join Date
    Jul 2003
    Location
    Cali
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    there is no best way

    the way i would do it, each recurring event would have its individual dates generated into a table of events at the time it is added
    Is there a specific reason why you would go for such an approach? Is it because of load issues during displays of the calendar?

    How would you address the issue of the server possibly adding hundreds/thousands of records with one event creation?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    specific reason? simplicity

    having many rows in a table is not necessarily something that should be avoided by making code more complex
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •