Laying out a database schema for a calendar application

user_52622 0
Viewed 48511

I want to write a calendar application. It is really recurring items that throw a wrench in the works for the DB schema. I would love some input on how to organize this.

What if a user creates an event, and inputs that it repeats everyone Monday, forever? How could I store all that in the database? I can't create infinite events. Do I simply put a table in there that holds the relevant info so I can calculate where all the events go? If so, I would have to calculate them every time the user views a new part of the calendar. What if they page through the months, but they have a ton of recurring items?

Also, the schema needs to handle when a user clicks an item and says "Edit this one in the sequence" not all items in the sequence. Do I then split the one item off of the sequence?

Update 1

I have not looked at iCal at all. To be clear, I think saving the info that allows you to calculate the recurring items, and splitting off any that differ from the sequence is a great way to store it to be able to transfer it. But I think that in an application, this would be too slow, to do the date math all over the place.

calendar normalization schema database
7 Answers

I recently created a calendar application and this was one of the many challenges that I faced.

I eventually came up with a semi hack-ish solution. I created an event_type column. In that column, I had either: daily, weekly, monthly, or yearly. I also had a start_date and an end_date columns. Everything else was handled in the actual backend code.

I never tried to split an event if a user edited only one event. It wasn't necessary in the situation. However, you could split an event by changing the end_date of the first, creating a new event with a new start_date and the end_date of the original, and finally, a new event for the one you just chose to edit. This process would end up creating 3 events.

Hack-ish, I know. I couldn't think of a clever way to handle this problem at the time.

user_1166730

Hold the recurring item in the events table as normal, but flagged as recurring with the appropriate start/ end dates.

If the user modifies a single instance of the appointment, just create a new event, perhaps with a 'parentId' equal to the recurring event's id.

Build logic that causes the calendar to override any recurring events on a particular day with events with matching parent IDs.

Your question about performance is basically the old speed vs. storage issue. I really don't think the calculation required would exceed the space requirement for storing so many appointments. Just read up on database optimization- indexing etc.

user_952310

Could you bridge the two worlds with a "cache" table, in which you pre-compute the next X days worth of events?

So three tables:

recurring_event_specs
one_time_events
cached_recurring_events

For any part of the calendar within X days of today, your query will UNION one_time_events and cached_recurring_events.

Then you would only have to do on-the-fly date calculations if the user tried to look at a part of the calendar more than X days in the future. I imagine you could find a sane X that would cover the majority of normal use.

The cached_recurring_events table would need to be updated whenever a user adds a new recurring event -- and possibly once a day offline, by a cron-job/scheduled-task. But only on days when no new recurring event has been created.

user_87220
Related