[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: DB schema for storing repeated events ?
From:       "Christian Mack" <Mack () compal ! de>
Date:       1999-05-31 16:45:55
[Download RAW message or body]

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


---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail mysql-thread4224@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail mysql-unsubscribe@lists.mysql.com instead.

[prev in list] [next in list] [prev in thread] [next in thread]