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.