Is there a best practice for recurring calendar events

Question

I'm not a dba but a php programmer, so that I'm not an SQL pro. Now, I have searched in both programmers and dba stackexchange sites but the answers are too specific or not conclusive.

My question is very simple, I need to create a calendar. just a calendar, like Google calendar, with normal and recurring events.

Now to summarize what I have learn in my research about recurring events, appears to be 2 main schools:

1. Create a "parent" event and an occurrences table with one row for each repetition of the event.
2. Create an events table (1 row for a recurring event) and a second table to store repetition pasterns (generally 1 or 2 row per event).

There are good and bad things in each of the 2 solutions:

1. Easy to query, easy to create exception (like changing only 1 occurrence in the series), easy to store complex patters, BUT will create a lot of rows, hard to export (ex iCal), easy to cause data integrity issues.
2. Easy to store complex patterns, clean, easy to export, BUT difficult to query.

When a I say hard or easy to query, I mean to query for a specific time range. For example I want to query all events (or occurrences) for this month, having 2 Timestamps.

The complexity of the recurring events should also be very granular. For example, repeat every 2 months on the 2nd Saturday, or repeat every 2 weeks on Friday and Monday, or repeat every Friday to Monday of the first week of every other month.

To summarize I am creating a very basic task here and I'm sure there is a well know DB schema for this. Lets call it a best practice.

So, what is the best Schema to create those events?

BTW: I use MySQL.

Answer 1

Separate the data from the processing. A database contains data; application code does the processing.

In your situation there are effectively two sets of data:

• Raw data (especially for recurring events, such as "4pm every 3rd tues of the month until next June")
• Calendar events, such as the the dozens of entries that the above item implies.

Consider starting with two tables, one for each of those. Then write the messy code that translates the definition of a recurring event into the individual entries. (Note: That is code, not SQL.)

Obviously, if there is the option to change a recurring event, there needs to be a way to make potentially massive changes to the second table. This implies an id for the recurring event that is stored in the second table. A FOREIGN KEY might be the way to go. (I prefer to do such myself.)

There are probably other tables, such as for 'normalizing' users, etc. (Do not normalize dates or datetimes.)

"What's scheduled for this afternoon?" That turns into a range query.

Hint: If something is ongoing (eg vacationing for 2 weeks), break it into multiple daily events (14, midnight to midnight) for the second table. Otherwise the SQL can get messy and inefficient.

How does all that feel?

Answer 2

This is just a suggestion which, while obviously not helping the OP, may help others with similar/analogous problems (won't fit as a comment).

You may also wish to consider using MySQL Events for your application. Depending on your app's functionality, they may provide a more elegant solution to a scheduling/calendar range of capabilities.

Check out the documentation here, and these sites (1, 2). I was looking at this recently and it's really quite powerful and can do a lot of the heavy lifting in the scheduling/polling area of this sort of programme.