I've a fascinating problem attempting to represent complex schedule data inside a database. Like a guideline, I have to have the ability to represent the whole of the items the iCalendar -- ics -- format can represent, however in a database. I am not really applying anything relevant to ics, however it provides a good scope of the kind of rules I have to have the ability to model for my particular project.

I have to allow allow representation of merely one event or perhaps a recurring event according to multiple occasions daily, days each week, week of the month, month, year, or some mixture of individuals. For instance, the 3rd Thursday in November yearly, or even the 25th of December yearly, or every two days beginning November 2 and ongoing until September 8 the year after.

I do not worry about insertion efficiency but query efficiency is crucial. The operation I'll be doing most frequently is supplying whether single date/time or perhaps a date/time range, and seeking to find out when the defined schedule matches any area of the date/time range. Other procedures could be reduced. For instance, given The month of january 15, 2010 at 10:00 AM through The month of january 15, 2010 at 11:00 AM, find all agendas that match a minimum of a part of that point. (i.e. an agenda that covers 10:30 - 11:00 still matches.)

Any suggestions? I checked out http://stackoverflow.com/questions/1016170/how-would-one-represent-scheduled-events-in-an-rdbms however it does not cover the scope of the kind of recurrence rules Let me model.

Generate an income did something such as sony playstation have two tables. If the event didn't have recurring pattern, then just keep date, start time, and finish time. Your query inspections when the time your trying to find is more than the beginning duration of any entry and under or comparable to the finish duration of that same event.

For recurring occasions, I am much less acquainted with how iCalendar stores repeated episodes, but when you store each event during the day each week (you may have to possess multiple rows for any single event whether it repeats on several day per week), then search it almost exactly the same way because the above table. For stranger repeated episodes such as the third Tuesday each week, you might have an additional column explaining the particular condition. I would have the ability to provide you with a better answer with this should you could let me know much more about how ics signifies that type of recurrence.

Hopefully helps. I do not cash time at this time. You are able to get in touch later if you wish to discuss this. I am presently in Missouri so my availability for the following week will probably be erratic.

Ultimately, this publish was most useful:

http://stackoverflow.com/questions/1054201/ical-field-list-for-database-schema-based-on-ical-standard

We made the decision to follow along with the iCal model pretty exactly because the men who authored that standard were built with a great sense of the issue domain.

This can be a trivial solution, but what will be the disadvantages of adding a column that defines the recurrence from the event (i.e. every x days, yearly, weekly, etc) and taking advantage of that because qualifying criterion?