List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:May 31 1999 4:45pm
Subject:Re: DB schema for storing repeated events ?
View as plain text  
chas wrote:
> 
> I'm looking at putting a calendar online, based on MySQL,
> and am chasing my own tail trying to decide how to store
> the events.
> 
> The most obvious method is simply :
> 
> CREATE TABLE individualevents (
> name        CHAR(32),
> details     CHAR(128),
> date        DATE,
> start_time  TIME,
> end_time    TIME
> )
> 
> 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 (
> name        CHAR(32),
> details     CHAR(128),
> start_date  DATE,
> end_date    DATE,
> repeats     ENUM ("Weekly", "Daily", "Monthly", "Daterange"),
> occurs SET  ("mon", "tues", "weds", "thurs", "fri", "sat", "sun"),
> start_time  TIME,
> end_time    TIME
> )
> 
> 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
>    daily.
> 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,
> 
> chas

Hi Chas

Yes, the second method will be slower, but it also will be more universal.
I would suggest to use the method best matching your needs in term of usability not speed
alone.
You will not search very often during the day, so to me speed seems not the primary issue
here.

Tschau
Christian

Thread
DB schema for storing repeated events ?chas30 May
  • Re: DB schema for storing repeated events ?Christian Mack1 Jun
  • Re: connectorJ and ResultSetMetadataMark Matthews24 Aug