iCal backend - recurring dates

Question

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?

eggyal · Answer 1 · 2012-06-27T14:38:08.687

1

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;

edited Jun 27 '12 at 14:38

answered Apr 25 '12 at 17:26

eggyal

122,705
18
212
237

Thanks for the reply, I will look towards using your example. I am more interested in the concept of having an additional table, and whether this is the right approach. There are a number of examples of iCal, but not many about the best way to manage the events. – Dave Apr 26 '12 at 19:34
I think iCal is a good format for the exchange of calendar data between systems, but perhaps not best suited to structuring calendar data within a system? Whether you implement the above technique as a separate table, or add such fields to a more generic `Appointments` table, is entirely up to you. – eggyal Apr 27 '12 at 06:58
your reply was appreciated, but didn't really answer the question, which was about use of the iCal format and RRULES for a calendaring system, and whether a second table would be required (or was necessary), and whether my thoughts and approach was actually a good idea.FYI I have decided to use the iCal and RRULES format to generate a list of future dates. These are then stored in a separate table. The complexity comes when a user wishes to update some/all of the dates in a sequence. – Dave May 02 '12 at 16:42

score 1 · Accepted Answer · edited May 23 '17 at 11:56

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.

I had searched this site, but failed to find this linked page which contains a lot of useful information - thanks. I think that the 2 table approach is correct, one for the rrule which in turn generates an events table. I will have to give thought to never-ending (or long running) events and whether to just limit them, or expand it periodically (probably on a cron job). The calendar is not just available to the user that created it, but needs to be queried to find 'free slots' by the system, so I will need to keep the rrule(s) expanded fully for all users covering the relevant time frame(s). — Dave, May 06 '12 at 17:35

iCal backend - recurring dates

2 Answers2