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]
