How to optimize the data model for an event calendar?

Question

I am creating an event calendar. The main functional function is as follows:

There are 3 conditions for each day, "available", "unavailable" and "confirmation is required." Each day can be set to one state (i.e. Event). Each event can be set as recurring weekly, monthly or not at all.

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

Calendars do not have an "end date": in the future there may be an event on any specific date.

I assumed that the data model is:

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 pretty elegant way to store data, but I'm worried about the search: it would be pretty hard to get status for this day.

?

Answer 1

, start_date ( ), . , , , , , .

, , :

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

A () , -

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() , , cday , :

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
;

, ,

Answer 2

iCalendar.

http://en.wikipedia.org/wiki/ICalendar .