SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Member
    Join Date
    May 2004
    Location
    Brisbane
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Design for recurring events in a Calendar

    I have been contacted to create a PHP/MySQL Events Calendar for an organisation and have come to a roadblock with how to design my tables to best support recurring / repeating / reoccuring events.

    I've looked at other event calendars out there and they use one of three solutions.

    1. Don't support recurring events - Obviously not suitable

    2. Store every occurence of the event as a seperate record - Usually, the repeating event is limited in some way(endDate is startDate+1 year or only 50 records are added at the one time, etc). Each "child" occurence has it's own ID but also inherits it's parents ID so that we can keep track of it.

    When a user wants to see certain events from a date range you can just SELECT..WHERE startDate = foo and endDate = bar. If the user wants to see a single day's events then it's just SELECT..WHERE startDate = foo.

    3. Store a recurring event as one record, with a startDate, endDate and some recurranceValue (could be 1 day, weekly, fortnite, etc)
    In order to then display the results you need to calculate the dates of each occurence, check if they fall in between startDate and endDate and then display them


    There is a discussion about the same problem here:
    http://discuss.fogcreek.com/joelonso...&ixPost=109937

    And OrgCal (a PHP+MySQL solution) implements it:
    http://kevin.tridubdesign.com/content.php?article.6

    While the second solution would be (I think) easier to implement, the third solution just seems "better".

    Any ideas? OR better yet, any working examples?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i did not read the other discussion, but i don't need to for this question:

    don't you need to know where each event occurs?

    suppose the "weekly staff meeting" on june 16th is not going to be held in boardroom 4 as per usual, instead it will be held in meeting room B

    you cannot record this fact in scenario 3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    May 2004
    Location
    Brisbane
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    I take exception with your comments!

    well, no, not really. But the db does.

    In the second proposed solution (from now on referred to as lotsaRecords) the change on June 16th would just be an update to the record referring to that date.

    I really should have mentioned this in my first post, but in the third solution (from here on in known as fewaRecords) the June 16th occurence is stored as a seperate record - an exception to the rule, you might say.

    So eith fewaRecords we query our db, return our reoccuring event and also check to see if their are any "exception" records stored as well. If so, then on those dates the exception is listed, not the usual reoccuring record.

    The last 2 comments by Allison and Veal at the fogcreek site make this situation clearer.

    I can also post the Org Cal db design if needed.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    ehando, that's not fair, that is a crucial difference you should not have failed to mention

    it kinda feels like you've already decided on 3 anyway, so why don't you just go ahead and do it that way



    for what it's worth, after 17 years of writing sql and over 30 years designing databases, i would definitely go for scenario 2

    you might feel that the fewer records the better, but while this might be true in general if you're just looking at the database, perhaps you should actually try writing all the different sql you will need (booking, checking for availability, re-scheduling, etc.) and then decide
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    May 2004
    Location
    Brisbane
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ehando, that's not fair, that is a crucial difference you should not have failed to mention
    I'm an idiot. I thought I had added that exception rule to my post but I hadn't.

    you might feel that the fewer records the better, but while this might be true in general if you're just looking at the database, perhaps you should actually try writing all the different sql you will need (booking, checking for availability, re-scheduling, etc.) and then decide
    The total number of records isn't really a huge concern with lotsaRecords since I can easily keep track of everything with that "child/parent" relationship. My main concern with "fewaRecords" is that there could be a performance hit (minimal or substantial I don't know) with the extra PHP processing and db calls in order to calculate reoccuring events, grab the exception events and display them. In particular, I'm concerned with how that potential performance penalty would scale to a larger database with many reoccuring events displayed as a year format (a week or month of events shouldn't be too bad). I guess also, with the exception events and added coding, fewaRecords increases the complexity and possibly maintainability.

    fewaRecords = faster INSERT / slower display + more complex
    lotsaRecords = slower INSERT / faster display + less "nice"

    Since the events calendar is likely to be viewed FAR MORE then it will be added events to, maybe the tradeoff on slower INSERT/ faster display is the best way to go.

    I'll keep you all posted.

  6. #6
    My precious!!! astericks's Avatar
    Join Date
    Mar 2002
    Location
    Vancouver, BC
    Posts
    1,971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, here's what i'm thinking:

    setup two column in your event table, as flags.

    flag1: Whether event is to be repeated or not
    values 0 or 1

    flag2: How often is even repeated?
    Values:
    0 -> Daily
    1 -> Weekly
    2 -> Monthly etc

    And then program around those 2 restrictions.

    This is my take on it


    edit: this is pretty much as your option #3 i guess...hehe

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by astericks
    And then program around those 2 restrictions.
    written any sql for something like this recently, have you?

    i'd love to see it

    my take is: the sql for #3 is horrid, and complex beyond your imagination

    hmmm, i wonder if anybody who has actually implemented #2 and #3 can comment on them

    i've done #2 but never attempted #3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    My precious!!! astericks's Avatar
    Join Date
    Mar 2002
    Location
    Vancouver, BC
    Posts
    1,971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i should specify. i didnt mean write SQL for that, but php/asp whatever. I really dont think I am excellent with sql

  9. #9
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't see why you wouldn't want to store every occurence of the same event. I mean, isn't historical tracking of any significance to the customer (or possibly even to the design, perhaps it just hasn't been uncovered yet )? And I don't fully agree on how #1 absolutely states to not support reoccuring events, heck me and my coworkers would be out of a job!

    Maybe I'm confused or immature on the details on your specific scenario.

  10. #10
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    PA, USA
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another simpler solution you could use would be to split the event date into 3 fields, day, month and year. make them nullable, that way, if you do a select checking for null columns you can use the following rules to implement repeating dates:

    day set, month set, year set - does NOT repeat
    day set, month set, null year - repeats annually
    day set, month & year null - repeats monthly
    day, month & year all null - repeats daily...

    The fourth scenario is possibly a little fine grained, but lets face it, its better to support the most extreme use cases than to implement arbitrary restrictions on your code

  11. #11
    SitePoint Member
    Join Date
    May 2004
    Location
    Atlanta, GA
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool My implementation, small, a little complex, but very effective

    I have had to answer this same question with a website I am building for a local ministry. Being a non-profit, donation type organization, you can imagine money is tight and as such so is their options on how much storage/data space they can afford. (Not everyone has access to terabytes of storage space for their database). At any rate, I needed to build a system for a Calendar of Events, that people could volunteer to help out for. Many of the events recur on a regular basis, so recurrence had to be supported. My solution, (and it took me about a week of hard thought to get it to work), is not too complex, but does allow for very small tables. I have two tables to handle my data. An Events table and an EventExceptions table. I store all events in the first table regardless of whether it is a recurring event or not, as one record. If someone decides to edit a single occurence of an event series, I would enter that date in the second table, and then depending on whether they changed or deleted the occurrence, I would either insert a new record (non-recurring with a specified date), or do nothing else. This leaves me to have to do a little bit more coding in my web page, but as I'm actually a software/web developer and not so much a database guru, I'm perfectly fine with this. The sql needed to get the information out of the database is not really that complex, though it is a little big in the where clause.

    If you're interested, let me know and I can explain further how I implemented this solution.

  12. #12
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Newcastle, England
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'd definitely side on option 2 being the best bet. I don't understand why you think option 3 is "nice" in comparison - there's nothing wrong with option 2, you still get a full picture of the recurrance of any event (due to the "parent-child" relationship). In my own opinion, storing exceptions as you suggest for option 3 makes it less elegant, not more.

    I'd also add (more for shaggy13) that storage space etc. should really not be a concern under option 2. The size of a row in the events table should be very small (on the order of 30-50 bytes at a guess), so even ten thousand records or so wouldn't take much space at all (less than 1Mb); and if your ministry has more events than that planned for the forseeable future, they're not "small" by any means . If your events table is much wider than that, it may be worth a look to see if anything can be split out into seperate tables and normalised a little.

    I'd also suggest that the relevant ease of development would more than pay for the slight data storage overhead, as it would mean less money spent on developers wages (or less time if you're volunteering your labour), or more features implemented.

    It's personal choice of course, but option 3 has very few merits over option 2, but one major drawback in terms of complexity of accessing the data. That alone should swing the choice; but of course, it's possible either way. Good luck whatever you decide!
    Nick Wilson [ - email - ]

  13. #13
    SitePoint Member
    Join Date
    May 2004
    Location
    Atlanta, GA
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I do agree that the storage size may not be small at first, but if you want to be able to recur to inifinity, (which you would want to do if you didn't want to ever worry about having to go back into your db or code to reset some arbitrary future "end date" for those times when your user chooses the "no end date" option. Otherwise, how do you enter a single record for and each occurrence of an event with no end date? If you make it some really far off end date, and you have a daily or weekly recurring events, you could see how you could end up having lots of records for your events. Using my option, I not only keep my tables small, but I keep the recordsets that I have to grab from the db, equally small.

    When I said that it took me about a weeks worth of hard thought, that was kind of an exaggeration. I did have to think hard on how I was going to design my database, and thinking of the solution did take several days, albeit I wasn't thinking on it 24/7, rather just a couple hours a day. Once I did have the tables designed, the sql stored proc took me about 2 days (again, not all day just a few hours a day), and the coding only took me a few hours as well. It's a great system, that is very light-weight and that also offers me the ability to use other formats to store the data if need be (xml, text files, etc).
    It all comes down to preference I guess. Good luck.

    Quote Originally Posted by CrazyCrane
    I'd definitely side on option 2 being the best bet. I don't understand why you think option 3 is "nice" in comparison - there's nothing wrong with option 2, you still get a full picture of the recurrance of any event (due to the "parent-child" relationship). In my own opinion, storing exceptions as you suggest for option 3 makes it less elegant, not more.

    I'd also add (more for shaggy13) that storage space etc. should really not be a concern under option 2. The size of a row in the events table should be very small (on the order of 30-50 bytes at a guess), so even ten thousand records or so wouldn't take much space at all (less than 1Mb); and if your ministry has more events than that planned for the forseeable future, they're not "small" by any means . If your events table is much wider than that, it may be worth a look to see if anything can be split out into seperate tables and normalised a little.

    I'd also suggest that the relevant ease of development would more than pay for the slight data storage overhead, as it would mean less money spent on developers wages (or less time if you're volunteering your labour), or more features implemented.

    It's personal choice of course, but option 3 has very few merits over option 2, but one major drawback in terms of complexity of accessing the data. That alone should swing the choice; but of course, it's possible either way. Good luck whatever you decide!

  14. #14
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why not use a "mostly number two" solution with a "little bit of three" thrown in?

    You could store the info as in proposed solution three for use in generating the records in proposed solution two. Those meetings that are recurring could then be pre-generated as per solution two for a given time frame. Then you can have a scheduled job/task (or more if needed) that periodically (say nightly) runs through all recurring meetings adding more "pre-generated" meetings to the database as needed...thus you keep your "pre-generated" meeting table up-to-date forever (in the case of meetings that will recur for long time frames).

    You will always have the "template" of the meeting (as stored per solution three) and the pre-generated entries (per solution two) for ease in querying as well as record keeping and modifications to meetings (like a location change). The "template" would then provide continued use for keeping the recurrances up-to-date.

    That scheduled job/task routine would also have to be callable for those who may want to modify a meeting that is way in the future...so make it a bit flexible in its use.

  15. #15
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    PA, USA
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why not just split the date fields into 3 nullable fields, day, month and year. hell, you could even add hour, minute and second if you wanted to make the repetition even more finely grained
    The basic idea is, any field that is null in an inserted date, will be repeated in that timeframe... so for example;

    day = 11
    month = null
    year = null

    would repeat on the 11th day of every month.

    day = 12
    month = 11
    year = null

    would repeat on the 12th of November every year.

    day = 12
    month = 11
    year = 2004.

    Will occur on the 12th November 2004 and will not repeat.

    And of course, lastly:

    day = null
    month = null
    year = null

    this even repeats every single day.

    This can be implemented with a single SQL insert, has no need for stored procedures, is flexible and is completely simple to understand.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    maaxiim, you may be missing some of the nuances

    how do you represent the 11th of every month except if it's on a friday

    or the 12th of november every year except this year

    and stuff like that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    PA, USA
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was trying to outline a simplified method for defining repeating events. The exception rules can still be used with this technique to flag any exceptions... the SQL required to generate the rules would still be one fairly simple query joined to the exception table. Sorry if I never made that clearer...

  18. #18
    SitePoint Member
    Join Date
    May 2004
    Location
    Brisbane
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Progress of sorts

    Still working on a solution at the moment.

    More info on the project:

    It's an events calendar for a city council (similar I guess to http://www.brisbane.qld.gov.au/commu..._search1=today )
    I've chosen Brisbane since it's the most extreme example. Our council is probably a 5th the size (population, activites, etc) so it won't be handling anywhere near as many events. BUT you can see from the example the type of problem I need to address - dealing with recurring events, events (such as an art exhibit) that might be scheduled daily for 6 months, festivals that span a weekend or a week, etc.

    There's no real need to break things down any further than the day/month/year since the the purpose is an events calendar rather than a "daily planner" or "personal planner".

    Shaggy13 - I'd be interested in hearing more about your solution. You mentioned that you used a stored procedure but AFAIK that won't be possible with my implementation since it's using Mysql 3.x

    I'm tearing my hair out at the moment trying to balance "technically correct" with ease of use/maintanence for the client's developers who aren't quite as technically proficient as I am - and that's saying something

    StephenBauer - I think a scheduled task/cron job would be a little too complex given the current circumstances, but at one stage I was thinking of adding simnilar functionality to the project.

  19. #19
    SitePoint Member
    Join Date
    May 2004
    Location
    Brisbane
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, project is 90% done (need to add some validation checks in and finetune formatting but that's about it).

    In the end I went with solution 2 - each occurence is stored as a record. Due to time and programming constraints this was the most straight-forward way to do things.

    I tested both designs and found that although solution 3 made it easier to "add and forget" the recurring records, there was a considerable performance hit in displaying the records on the website due to the increased PHP processing.

    These were very rough tests and to be honest also pitted procedural PHP versus OO (so I guess could be argued completely invalidate the results), but basically with solution 2, to display a month of events with ~40 records took less than a second. To display the same results set up as recurring events took up to 40 secs.

    While I would have like to have gone back and setup a level playing field between the two approaches, realistically this was just not possible. I needed something to sway me either way and that was good enough for me.

    So to summarise: I went with a quick and easy approach , as suggested by r937 in the 2nd or so post of this thread.

  20. #20
    SitePoint Member
    Join Date
    May 2004
    Location
    Atlanta, GA
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up ehando's solution

    ehando, glad to see you've come up with your solution. I'm definitely not one to think that one size fits all, just thougth I'd offer my 2 cents. I actually didn't realize your project was being coded in php/mysql. I'm coding a asp.net/sql server project. Because everything is compiled and not interpreted code, it does tend to run a bit faster. Anyway, glad to hear you got what you need.

    Quote Originally Posted by ehando
    ok, project is 90% done (need to add some validation checks in and finetune formatting but that's about it).

    In the end I went with solution 2 - each occurence is stored as a record. Due to time and programming constraints this was the most straight-forward way to do things.

    I tested both designs and found that although solution 3 made it easier to "add and forget" the recurring records, there was a considerable performance hit in displaying the records on the website due to the increased PHP processing.

    These were very rough tests and to be honest also pitted procedural PHP versus OO (so I guess could be argued completely invalidate the results), but basically with solution 2, to display a month of events with ~40 records took less than a second. To display the same results set up as recurring events took up to 40 secs.

    While I would have like to have gone back and setup a level playing field between the two approaches, realistically this was just not possible. I needed something to sway me either way and that was good enough for me.

    So to summarise: I went with a quick and easy approach , as suggested by r937 in the 2nd or so post of this thread.


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
  •