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.
Don’t support recurring events - Obviously not suitable
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.
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
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.
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
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 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.
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
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.
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!
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.
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.
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.
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…
It’s an events calendar for a city council (similar I guess to http://www.brisbane.qld.gov.au/community/inquire_online/events/cgi-bin/db_link.pl?in_type=etime&in_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.
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.
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.
I thought I had added that exception rule to my post but I hadn’t.
