-none- 2007-10-11 - By Ramsey, Robert L
Back
This and Frederic Wenzel's suggestions were exactly what I was looking
for.
Thanks!
Bob
> -- --Original Message-- --
> From: Erich C. Beyrent [mailto:erich.beyrent@(protected)]
> Sent: Thursday, October 11, 2007 6:10 AM
> To: mysql@(protected)
> 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 <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=robert-
> ramsey@(protected)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
