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/[EMAIL PROTECTED]
