I'm looking at putting a calendar online, based on MySQL,
and am chasing my own tail trying to decide how to store
The most obvious method is simply :
CREATE TABLE individualevents (
That's fine for one-off events but not for repeated events,
such as "Every Tuesday at 6pm" or
"June 3rd to June 22nd, 9-11pm"
So, now I'm looking at having :
CREATE TABLE repeatedevents (
repeats ENUM ("Weekly", "Daily", "Monthly", "Daterange"),
occurs SET ("mon", "tues", "weds", "thurs", "fri", "sat", "sun"),
Where a repeated event either repeats "weekly", "daily", "monthly"
or between the specified start_date and end_date (ie. a "daterange").
eg. for "Every Tuesday at 6pm" we would use :
mysql> insert into repeatedevents(name, repeats, occurs, start_time) values
("Swap meet", "Weekly", "tues", '18:00:00');
eg. for "June 3rd to June 22nd, 9-11pm" we would use :
mysql> insert into repeatedevents(name, start_date, end_date, repeats,
start_time, end_time) values ('swap meet', '1999-06-03', '1999-06-22',
"daterange", '21:00:00', '23:00:00');
Has anyone else had any better ideas on implementing this ?
I'm sure many people must have been faced with this problem.
Would the second method be substantially slower for searches ?
eg. if I want to find the events going on today, I need to use :
a) any events where repeats = "daterange" and today is between
start_date and end_date
b) any events that repeat "weekly" on this day or that repeat
c) any individual events.
Even more irregular events timings (eg. first and third
wednesdays of the month) can be placed as one-off
events in the original individualevents table.
thank you in advance,