-none- 2007-10-11 - By Frederic Wenzel
Back
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)
|
|
