5

I need to develop an application that supports "schedules". Example of schedules:

  • Jan 1, 2011 at 9am
  • Jan 1, 2011 from 9am to 10am
  • Every Monday at 9am, from Jan 1, 2011
  • Every Monday at 9am, from Jan 1, 2011 to Feb 1, 2011
  • Every Monday at 9am, from Jan 1, 2011 for 10 occurrences
  • etc.

If you have seen Outlook's scheduler, that's basically what I need. Here's a screen shot of their UI: http://www.question-defense.com/wp-content/uploads/2009/04/outlook-meeting-recurrance-settings.gif

How would I model such information in a database? Keep in mind that I also need to query this, such as:

  • What are the scheduled events for today?
  • What are the next 10 dates/times for a particular recurring scheduled event?
  • Etc.

I'm using PHP/MySQL, but am open to alternative solutions. Suggestions?

StackOverflowNewbie
  • 39,403
  • 111
  • 277
  • 441

1 Answers1

3

My personal opinion is to create all the events separately, with a start and end date. Then generate a unique identifier for the event (perhaps the event ID of the first you create) and assign it to all events (so you know they are somehow linked).

Advantages:

  • easy to do (you just calculate when the event should happen and create them all only once)
  • easy to change (you can save the recurrence perhaps on the first event, and then rebuild them all - remove and re-create)
  • easy to delete (they have a common unique ID)
  • easy to find (same as above)

Disadvantages:

  • You need a start and end date

-- appended from here --

Proposed Model:

Table Event

  • id big int (auto increment)
  • ref_id big int (this is kind of foreign key to the id)
  • date_start date
  • date_end date
  • title string
  • .. your custom fields ..
  • saved_recurrence text

Imagine you have an event repeating 4 weeks every Wednesday and Friday:

  • gather the recurrence stuff in an object and convert it to JSON (recurrence type, period, final date, ..)
  • calculate the dates of every single event
  • create the first event on the table Event with ref_id=0 and saved_recurrence=saved JSON object and get the id that was used (auto incremented)
  • update this first event record and set ref_id=id
  • create the next events always with this same ref_id (saved_recurrence can be empty here)

You should now have 8 events (2 every week for 4 weeks) that have the same ref_id. This way it's easy to fetch events in any date interval. When you need to edit an event, you just check for ref_id. If it's 0 it's a single isolated event. If not, you have a ref_id that you can search to get all event instances.

dresende
  • 2,211
  • 1
  • 16
  • 25
  • This is also easy to do stuff like `delete all ocurrences after this one`. – dresende Oct 14 '11 at 00:02
  • dresende, I'm not sure if I fully understand your solution. Can you show me a proposed model? – StackOverflowNewbie Oct 14 '11 at 03:07
  • @dresende: Can you please show the proposed model. I am also striving through the same problem. – Shantanu Gupta Mar 09 '13 at 04:46
  • The idea is not to save all the recurrence events in one row, but save them separately. To know that the events are related to each other, have an additional column in the database (for example "ref_id") that points to the base (or initial) event in the recurrence. – dresende Mar 11 '13 at 16:36
  • This way, all events are separated and you can save them individually. But you can also know if an event is related to another (by the ref_id not being null or zero) or if other events are related to one (by looking for events which have ref_id = id of the event). – dresende Mar 11 '13 at 16:37
  • @dresende: This post is quite old, but I would like to ask what is the field **saved_recurrence**? – drum Apr 14 '13 at 21:35
  • 1
    @drum it should store information about recurrence settings used to create all the instances, like recurrence period (monthly, weekly, every 2 days, ..) or date limit, number of generated events, etc.. this is just necessary in case you want to present an edit form to the user as you will need to show him the initial configuration – dresende Apr 18 '13 at 00:10