Calendar:Sql Calendar Schema

From MozillaWiki
Jump to: navigation, search

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


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?


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


  • `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)


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


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


  • `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.