CODEWITHSUNDEEP
phpmysqlschedulingscheduler
StackOverflowNewbie
StackOverflowNewbie

Reputation: 40643

Database Design: schedules, including recurrence

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

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:

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

Upvotes: 4

Views: 1270

Answers (1)

dresende
dresende

Reputation: 2230

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.

Upvotes: 3

Related Questions