How to optimize the data model for an event calendar?

I开发者_开发技巧'm building an event calendar. The basic functionnality is like this:

There are 3 states for each day, "available", "unavailable" and "confirmation needed". Each day can be set to a single state (i.e., an event). Each event can be set to be recurring either weekly or monthly, or not at all.

Each calendar is specific for an object (each object has its own calendar).

The calendars don't have an "end date": there can be an event at any given date in the future.

The data model I imagined was this:

Table: Calendar
id
user_id

Table: Status
id
label

Table: Event
id
calendar_id
start_date
status_id
recurring -- enum type: NULL, W, or M for weekly or monthly

This seems to be a fairly elegant way to store the data, but I'm worried about retrieval: it would be fairly complicated to get the status for a given day.

Is there a better or standard way to do this ?

Assuming that day is a start_date (otherwise I misunderstood), the format seems not bad to me. Later on maybe you will need to have start/end dates and maybe start/end time, in that case you will have to put timestamps instead.

In order to retrieve the data, I'd have a dates table created like:

+------------+
| cday (PK)  |
+------------+
| ...        |
| 2011-01-01 |
| 2011-01-02 |
| 2011-01-03 |
| 2011-01-04 |
| 2011-01-05 |
| 2011-01-06 |
| ...        |
+------------+

If you need to get the appointments with the status A (available) for a given period, you can do something like

SELECT ev.*
FROM cdays AS cd
JOIN event AS ev ON (
  CHECK_RECCUR(cd.cday, ev.day, cd.recurring)
)
WHERE TRUE
  AND cd.cday BETWEEN "given_start" AND "given_end"
  AND ev.day < "given_end"
;

CHECK_RECCUR() would be a function that checks if cday is in the scope of reccurence, like that:

CREATE FUNCTION CHECK_RECCUR(cday DATE, start_date DATE, recurring CHAR(1))
BEGIN
  IF cday < start_date
  THEN RETURN FALSE
  END IF;
  SET dformat = CASE recurring
    WHEN 'W' THEN '%W'
    WHEN 'M' THEN '%d'
    WHEN 'Y' THEN '%m-%d'
    ELSE ''
  END;
  RETURN (DATE_FORMAT(cday, dformat) == DATE_FORMAT(start_date, dformat));
END
;

Not tested but this is what I'd do

Check the iCalendar format.

http://en.wikipedia.org/wiki/ICalendar for standard format.