This and Frederic Wenzel's suggestions were exactly what I was looking for.
Thanks! Bob > -----Original Message----- > From: Erich C. Beyrent [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 11, 2007 6:10 AM > To: mysql@lists.mysql.com > Subject: Re: need advice on how to design tables for recurring events > > I've been working with Drupal for some time, and there is a module that > allows you to create event-based content with a repeating schedule. The > schema that it uses may be of some help to you. > > CREATE TABLE IF NOT EXISTS event_repeat ( > rid int(10) unsigned NOT NULL default '0', > repeat_data longtext NOT NULL, > repeat_RRULE longtext NOT NULL, > repeat_COUNT_remaining int(4) NOT NULL default '-1', > repeat_start int(10) unsigned NOT NULL default '0', > repeat_end int(10) unsigned NOT NULL default '0', > repeat_last_rendered int(10) unsigned NOT NULL default '0', > PRIMARY KEY (rid) > ); > > CREATE TABLE IF NOT EXISTS event ( > rid int(10) unsigned NOT NULL default '0', > nid int(10) unsigned NOT NULL default '0', > repeat_edited int(2) unsigned NOT NULL default '0', > UNIQUE KEY nid (nid), > KEY rid (rid) > ); > > CREATE TABLE IF NOT EXISTS event_repeat_calendar_map ( > day_stamp char(15) NOT NULL default '', > date_stamp int(10) unsigned NOT NULL default '0', > day_of_week char(2) NOT NULL default '', > day_in_month char(3) NOT NULL default '', > day_in_month_R char(4) NOT NULL default '', > month_day char(2) NOT NULL default '', > month_day_R char(3) NOT NULL default '', > month char(2) NOT NULL default '', > year_day char(3) NOT NULL default '', > year_day_R char(4) NOT NULL default '', > week_number char(2) NOT NULL default '', > week_number_R char(3) NOT NULL default '', > PRIMARY KEY (date_stamp), > KEY day_of_week (day_of_week), > KEY day_in_month (day_in_month), > KEY day_in_month_R (day_in_month_R), > KEY month_day (month_day), > KEY month_day_R (month_day_R), > KEY month (month), > KEY year_day (year_day), > KEY year_day_R (year_day_R), > KEY week_number (week_number), > KEY week_number_R (week_number_R) > ); > > HTH > > -Erich- > > Frederic Wenzel wrote: > > On 10/10/07, Ramsey, Robert L <[EMAIL PROTECTED]> wrote: > >> I'm looking for a "best practices" way of creating tables to store both > >> one time and regularly repeating events. These are classes, so for the > >> most part the have a regularly recurring time, but we do have some one > >> off events. (...) > >> The only other way I could think of to do it would be to duplicate the > >> cron format and have a table like this: > >> > >> Name, start_day, start_datetime, stop_day, stop_datetime, > >> > >> 'Class-A', '1,3,5','2007-08-20 08:00:00', '2007-12-05 09:00:00' > >> 'Class-D', '3,4,5','2007-08-20 13:00:00', '2007-12-05 14:00:00' > >> > >> And then parse everything, but that seems resource intensive too. > > > > Well that doesn't seem to resource intensive to me, however it depends > > on what you are displaying later. Knowing the amount of times the > > class meet would be counting the Mondays (+Wednesdays+Fridays) between > > start_datetime and stop_datetime, but that shouldn't be too bad. > > > > What you should never do though is putting different values into the > > same field -- it defeats the purpose of a relational database. '1,3,5' > > is therefore a no-go... You ought to make a column for each day of the > > week and set it 0 for "no class" and "1" for "class", or something > > along the lines of that. > > > > That will also make it insanely easy to retrieve all classes that meet > > on any given day: SELECT * FROM classes WHERE monday = 1 AND > > start_datetime >= NOW() AND stop_datetime <= NOW(); > > > > > > Fred > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=robert- > [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
