Is there a best practice for recurring calendar events 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:


Create a "parent" event and an occurrences table with one row for each repetition of the event.
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:


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.
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.
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.