How to best handle exception to repeating calendar events

Question

I'm working on a project that will require me to implement a calendar. I'm trying to come up with a system that is very flexible: can handle repeating events, exceptions to repeats, etc. I've looked at the schema for applications like iCal, Lotus Notes, and Mozilla to get an idea of how to go about implementing such a system. Currently I'm having trouble deciding what is the best way to handle exceptions to repeating events. I've used databases quite a bit but don't have a ton of experience with really optimizing everything so I'm not sure which method of the two I'm considering would be optimal in terms of overall performance and ability to query/search:

1. Breaking the repeating event. So taking the changing the ending date on the current row for the repeating event, inserting a new row with the exception, and adding another row continuing the old sequence.
2. Simply adding an exception. So adding a new row with some field that indicates it as an override.

So here is why I can't decide. Method one will result in a lot more rows since each edit requires 2 extra rows as apposed to only one row by the second method. On the other hand I think the query to find an event would be much simper, and thus possibly faster(?) using the first method. The second method seems like it will require more calculating on the application server since once you get the data you'll have to remove the intersection of the two rows. I know databases are often the bottleneck for websites and while I'm sure a lot of you are thinking either is fine because your project will probably never get large enough for the difference in efficiency to really matter, I'd still like to implement the best solution. So what method would you guys pick, or would you do something completely different?

Also, as a side note I'll be using MySQL and PHP. If there is another technology that you think would be better suited for this, especially in the database area, please mention it.

Thanks for the advice.

Answer 1

Events are not permanent, are subject to change or termination.

You should insert a row for each date in the event and, upon editing a repeated event, give the option to edit all instances of the event or just one.

When doing this, you should really only need an extra field to indicate if the event is active or not.

When properly indexed, this will not make a considerable difference on your queries.

An example would be:

SELECT * FROM events WHERE event_id = 123 AND active = 1;

On which case you'd be required to index the event_id and active columns.

Answer 2

I've decided to just enter a row with the exceptions and use code to remove them from the repeating set. It'll be awhile before I'm done with it but I'll try and remember to post back here with some performance results when it's all done.