Best way to store event times in (My)SQL database

Question

I'm trying to decide on the best way to store event times in a MySQL database. These should be as flexible as possible and be able to represent "single events" (starts at a certain time, does not necessarily need an end time), "all day" and "multi day" events, repeating events, repeating all day events, possibly "3rd Saturday of the month" type events etc.

Please suggest some tried and proven database schemes.

Answer 1

Table: Events

• StartTime (dateTime)
• EndTime (dateTime) null for no end time
• RepeatUnit (int) null = noRepeat, 1 = hour, 2 = day, 3 = week, 4 = dayOfMonth, 5 = month, 6 = year
• NthDayOfMonth (int)
• RepeatMultiple (int) eg, set RepeatUnit to 3, and this to 2 for every fortnight
• Id - if required, StartTime might be suitable for you to uniquely identify an event.
• Name (string) - name given to the event, if required

This might help. It would require a decent amount of code to interpret when the repeats are. Parts of the time fields that are at lower resolutions than the repeat unit would have to be ignored. Doing the 3rd saturday of the month woudln't be easy either... the NthDayOfMonth info would be required just for doing this kind of functionality.

The database schema required for this is simple in comparison with the code required to work out where repeats fall.

Answer 2

I worked on a planner application which loosely follows the iCalendar standard (to record events). You may want to read RFC 2445 or this schema published by Apple Inc. icalendar schema to see if they are relevant to the problem.

My database schema (recurring/whole-day event was not considered at the time)

event (event_id, # primary key
       dtstart,
       dtend,
       summary,
       categories,
       class,
       priority,
       summary,
       transp,
       created,
       calendar_id, # foreign key
       status,
       organizer_id, # foreign key
       comment,
       last_modified,
       location,
       uid);

the foreign key calendar_id in the previous table refers this

calendar(calendar_id, # primary key
         name);

while organizer_id refers this (with other properties like common name etc. missing)

organizer(organizer_id, # primary key
          name); 

Another documentation that you may find more readable is located here

hope this helps