Calendar:Sql Calendar Schema

Calendar Storage Schema

Outstanding issues:

• Recurrence needs to be changed to support full iCalendar-style recurrence; we can't really get away with a subset
• Indexes need to be defined once we know what the most expensive queries

Data types

• datetimes will be stored in UTC
• how do we handle local datetimes? the iCalendar spec has provision for timezoneless-times
• times will be stored as a datetime with a zero date
• dates will be stored as a datetime with a zero time

`cal_events`

Core event

• `oid`: unique id
• `hashid`: string hash used as key in IICal interface
• `title`
• `description` -- allow markup?
• `location`
• `categories` -- whitespace separated?
• `time_start` - datetime
• `time_end` - datetime

How do we represent all-day events? Maybe `time_start` == `time_end` and both have 0 time components?_

• `flags` - private? allday? what else?

Alarms

• `alarm_data` - alarm data.
• `alarm_time` - datetime when alarm needs to go off

Recurrence

• `recur_type`
  • null if no recurrence
  • 1 - daily
  • 2 - weekly (recur_data determines day(s) of the week)
  • 3 - monthly (recur_data determines which day of the month)
  • 4 - yearly (recur_data indicates daymonth)
  • 5 - hourly (recur_data determines minute(s) of the hour)
  • 6 - minute (recur_data determines second(s) of the minute)
• `recur_interval` - every N (minutes/hours/days/weeks/months/years)
• `recur_data`
  • if type 2: bitmask, 0-6 are Su M Tu W Th F S
  • if type 3: month number, 1-12 (0-11?)
  • if type 4: month (1-12), day (1-7), as (month | (day << 8))
  • if type 5: minute number 0 - 59 (64 bitmask value would be best)
  • if type 6: second number 0 - 59 (64 bitmask value would be best)

• `recur_end`: NULL if forever, otherweise a datetime; we can calculate the remaining count from this
• `recur_flags`: 0x1: has exceptions (see `cal_event_recur_exceptions` table)

`cal_event_recur_exceptions`

• `event_id`: references event oid
• `skip_date`: date to skip

`cal_event_attendees`

• `event_id`: references event oid
• `attendee_email`: email address of attendee
• `attendee_status`: status code

`cal_event_attachments`

• `event_id`: references event oid
• `attachment_type`: mime type of attachment
• `attachment_data`: blob

Mostafa's comments:

• I suggest considering floating (timezoneless) datetimes a bit more seriously since storing all values in UTC conflicts with daylight savings in cases where times are absolute ( i.e. remain the same all year like the event to get up at 7:00 for example ). The UTC value will depend on when the event was created. In other words, if datetimes are stored in UTC, we need a way to define if that value follows daylight saving changes or not. Some events might want to (international meetings) and some events might not (getting up).

• The offset can be calculated retrospectivley, no? If this is not possible, why not store UTC and the offset, rather than the timezone's time at the moment of storage? I suppose one could store that with the timezone code, but that would seem at least as complicated as the first method. MrJones 00:48, 23 Feb 2005 (PST)

• Maybe categories to be comma separated instead of whitespace separated?
• I vote for descriptions supporting markup
• all-day representation by `time_start` == `time_end` == 0 sounds logical to me
• If considering all types of recurrences, would these to be covered with the current schema:
  • Durations
  • Repeating on last/first/... weekday of months

I hereby apologize if I can't put much time in more in-depth thinking and/or commenting therefore being quick and brief. The whole direction looks great though.