iCal backend - recurring dates

1
votes

I am building a calendaring system using FullCalendar as the front end. The backend will be MySQL/PHP.

It will store a list of public appointments for users which are generated and inserted by the application. These appointments will be a single event which will start and finish on the same day. In addition users will be able to mark their unavailablity in the calendar due to personal commitments. This latter functionality requires the use of recurring events. Rather than re-invent the wheel I have been looking at using a structure based on iCal. This post was very helpful ical-field-list-for-database-schema-based-on-ical-standard in determining the database structure.

I have created the application form which allows the user to enter the necessary data in order to store a private single/recurring appointment. Upon submission of the form, the data is sent via Ajax to the server. I have found a great PHP script which generates an array of the recurring dates based on the parameters entered by the user, either in their native format or using RRULE.

I am unsure what is the best way to store/retrieve these recurring dates. The application needs to be able to render a calendar view for the user including the public and private dates. The application will also need to be able to return for example all users who may be free for a given time/date period.

Is it sufficient to store all events in the iCal format, and be able to retrieve events upon demand? The problem that I foresee is that repeating events are not easily searchable as their parameters would have to be expanded on the fly? I was considering creating a second table of every individual event (as generated ) with a reference back to the original RRULE that created it. I would look to limit the number of recurring dates that users may enter in order to prevent users from entering an event every day for the next 100 years! I think that this approach would also me to edit individual events which were originally created by a recurring rule.

Is this a good approach, or is there a better way?

2
phpmysqlicalendar

2 Answers

1
votes

Have a look at when building a calendar app, should i store dates or recurrence rules in my database?.

You should store the rrule, exrule, rdate and exdate associated with validity information (to be able to track changes over time : like if your users might want to look back in the past when they had a specific event happen and if the rrule changed between the occurence and the point of time when he/she looks back), For events which have finite number of occurences do a pre-computation of start and end for a time window for easier queries and have a rolling window for which you have the occurences of all events in a table. When users look for occurences out of the time window (should be rare to have people looking more than one year back or more than one year in future) you compute relevant events specific for the time window the users is requesting and generate them on the fly.

1
votes

How about a table along the following lines:

CREATE TABLE RecurringAppointments (
    startdate DATE NOT NULL,
    enddate   DATE,
    freq      INT  NOT NULL, -- contains #days between each occurrence
    -- etc.
 )

Then, to fetch all such appointments that occur on a given thedate:

SELECT * FROM RecurringAppointments
WHERE
      thedate BETWEEN startdate AND enddate
  AND DATEDIFF(thedate, startdate) % freq = 0;