Reoccurring calendar events

Question

I'm trying to build my own calendar and I have a form for entering events shown below.

I'm stuck as to how to design the MySQL table and how this information should be logged in such a way that it can be easily extracted?

The following is my attempt, and it works but is really ugly and I'm sure it can be improved:

CREATE TABLE events (
  event_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(100) NOT NULL,
  location VARCHAR(100) NOT NULL,
  body TEXT NOT NULL,
  start_ts DATETIME NOT NULL,
  end_ts DATETIME NOT NULL,
  valid ENUM('Y','N') DEFAULT 'Y',
  reoccurring ENUM('Y','N') DEFAULT 'N',
  every ENUM('day','week','month','year',''),
  bymonth ENUM('day','weekday',''),
  end_date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',

  PRIMARY KEY (event_id)
);

CREATE TABLE events_byweek (
  event_id INTEGER UNSIGNED NOT NULL,
  weekday TINYINT UNSIGNED NOT NULL,

  FOREIGN KEY (event_id)
    REFERENCES events(event_id)
);

-- returns all dates, reoccurring or otherwise within specified time range
-- by month
SELECT * FROM events WHERE (:year = YEAR(start_ts) AND :month = MONTH(start_ts))
OR (reoccurring = 'Y' AND ((YEAR(end_date) >= :year AND MONTH(end_date) >= :month) OR end_date = '0000-00-00 00:00:00')
AND (every != 'year' OR MONTH(start_ts) = :month)) AND valid = 'Y'

-- by day
SELECT * FROM events WHERE DATE(start_ts) = :date
OR (reoccurring = 'Y' AND (DATE(end_date) >= :date OR end_date = '0000-00-00 00:00:00')
AND (every != 'year' OR MONTH(start_ts) = :month)) AND valid = 'Y'

-- by week
SELECT * FROM events_byweek WHERE event_id = :event_id

I'd really appreciate any advice, please!

Answer 1

I think you don't need any relation array.

Your db table could be:

event_id UINT(10) auto_increment NOT NULL
/* not important fields omitted */
start DATETIME not null
end DATETIME not null
reoccurring ENUM('NO', 'WEEKDAY', 'MONTHDAY', 'MONTH_REL', 'YEARLY') DEFAULT 'NO';
weekdays UINT(1) DEFAULT 0;
until TIMESTAMP DEFAULT NULL

I used DATETIME as it is easier to query (see below), but in fact it does not matter. You can keep it TIMESTAMP if you like.

In weekdays one can keep a byte with bits that are 2^0: Sunday, 2^1: Monday and so on. So if event reoccurs every day you would put there 127.

If until is NULL, the event repeats forever.

Because it is very difficult to find by SQL whether "the 3rd Wednesday of the month" is within the specific range, I think without user-defined functions even if not impossible, but very hard and the code won't be clear, I suggest that you take all your events, fetch them in php and filter out there.

The query which will load only the necessary events (pre-filtering) would be:

SELECT ... FROM events
  WHERE 
     /* We take all non-reoccuring events in period */
    ((reoccurring = 'NO') AND (start >= :start) AND (end <= :end))
  OR
     /* We take some part of reoccurring events */
    ((reoccuring <> 'NO') AND ((start <= :end) OR (end >= :start)) AND ((until >= :start) OR until IS NULL)
  ORDER BY start ASC;

So, while fetching you can test whether a record meets the following criteria:

1. Is not reoccurring, so keep it
2. Is reoccurring by specific weekdays (eg. every Wednesday and every Friday) - check if there are such weekdays between the period, if at least one is, the event stays, otherwise, throw it away
3. If reoccurring is on month day (eg. every 21st of December), do the same.
4. If reoccurring is every month - relative day (like third Wednesday), do the same etc.

If your record does not meet the criteria, delete it, from array of course, not the db :-).

Some of the tasks would be easy also to put into the SQL query. For example, you could filter repeating events on specific month-day by SELECT ... WHERE ... OR ... (start LIKE '%-:month-:day %) (assuming that start and end of event are the same, as shown on your picture in the question). This is some advantage of the DATETIME field that you can easily search them like they were strings, so %-12-21 % finds all records that have month 12 and day 21 (they should be always two-digit numbers, of course). (The advantage of TIMESTAMP is that it is easy to calculate date differences etc.)

If events repeat with every monthday, use ... LIKE%-%-:day %` and so on.

So at the end you need two functions returning boolean that would check two cases:

1. is a weekday in specific period
2. is a nth weekday of month in the period (note that if the first one fails, you don't need to run the second)

You can code them even by brute-force by using foreach or something.

Also, you don't need to perform the weekday check if the field value is 127 - so it occurs every day.

EDIT on 2013-03-29 - Explanation how to use bits as weekdays

In the field weekdays one can keep days as bits because there are 7 days and 8 bits in one (unsigned) INT(1) number. So you don't have to add more columns like "occurs_monday", "occurs_tuesday" etc., nor you don't have to use any relations. I proposed it this way because I think it is possible that events can occur "every Monday" and "every Friday". If not, keep a number there (0=Sunday, 1=Monday, etc.).

Moreover, the event that occurs every day is also a special case of events that occur 7 days a week, so I don't need another ENUM value in the reoccurring column.

Now how to use it in PHP?

You just need to test if the bit of particular weekday is set. You can do this using bitwise AND operator. It would be even simpler, if you define some constants:

define("WEEKDAY_SUNDAY",1); // 2^0
define("WEEKDAY_MONDAY",2); // 2^1
define("WEEKDAY_TUESDAY",4); // 2^2
// ....
define("WEEKDAY_SATURDAY",64); // 2^6

// Does the event occur on Friday, maybe also other weekdays?
if($row['weekdays'] & WEEKDAY_FRIDAY){

// Does the event occurs only on Friday, and no other day?
if($row['weekdays'] == WEEKDAY_FRIDAY){

// Let's make the event occur on Friday and the day(s) that it already occurs
$row['weekdays'] = $row['weekdays'] | WEEKDAY_FRIDAY;

// Make the event occur only on Friday and no other weekday
$row['weekdays'] = WEEKDAY_FRIDAY;

// Let's check if the event occurs today:
if(pow(2,date('w')) & $row['weekdays']){ //...

The date function with "w" parameter returns the day-of-week number from 0 to 6, so that's why I used it so.

Answer 2

I have tackled this problem before and it is complicated. However, I did come up with a solution that I think makes sense (with the help of stack overflow). What I wound up doing is creating an events table and an event recurrence table. The event table is solely responsible for holding individual events. The event recurrence table is responsible for holding the recurrence instances.

What I do then is use the event recurrence table to generate events that go into the events table. What this allows is for is a simplified way to display events. This is because the calculation of recurrences has already been done for you. I generate two years worth of events out when the recurrence is created. If a user navigates outside of that two year range, then I generate more events as needed.

There is also a cron job that is ran every night to make sure that at least two years of events have been generated for every recurrence. Every recurrence then needs to have a generated until field, end recurrence, type of recurrence, and info like title, description, time, etc.

If you decide to go this route, and need code samples, definitely let me know.