-none- 2007-10-11 - By Erich C. Beyrent
Back
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 <robert-ramsey@(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=mysql@(protected)
|
|
