CODEWITHSUNDEEP
mysqlschema
Kebabman
Kebabman

Reputation: 823

MySQL schema for recurring events - Query by specific date

I'm just about to start putting together a schema for a system that will have recurring events and keep going through different options. None of which seem ideal.

The requirements are that I need to store the events and the pattern by which they occur I also need to be able to query this data with a specific date to determine if the date/time specified is covered by any of these events.

An example that demonstrates the functionality I am after works along the lines of a train time lookup system.

I'd like to be able to insert the time at which a train departs for a specific journey. Say 10am every day Monday - friday.

Then I would like to be able to query this data set to see if any trains are leaving at 10am (with 15 minutes leeway either side so 9:45 - 10:15) on Wednesday 30th April 2014.

I keep going backwards and forwards on this without making any real progress so any ideas would be much appreciated.

Upvotes: 2

Views: 732

Answers (2)

flaschenpost
flaschenpost

Reputation: 2235

Like @TMS, I also would use a Schedule table. The train tables which I know differ not only on the weekday, but also on holidays and on Bank Holidays (days like 25.12. or Easter Monday) and on the season.

So I would extend @TMS's table by a column Holiday and BankHoliday (or a better name, I'm only German) tinyint, 0 = false, 1=true, NULL = always. That means, if a train only comes at holidays, it gets a "1" in the column Holidays, if only at school time, it gets a "0", and if holidays don't matter, it gets NULL.

Then you would need a table CalendarDay with all days of the interesting years, where you have to mark holiday-days and bankholiday-days. If you are lucky, those depend not on the regions in your country. In Germany you have all that by region (Saxonia different from Bavaria etc.).

I am sure that there are different time schedules in Summer and in Winter, and they change those schedules from year to year (Summer 2014 != Winter 2013/2014 != Summer 2013). So I would add another table ScheduleTable with an ID, a name, a from-date and an until-date, and its ScheduleTableID is an extra field in table Schedule too. That means, you can already store the Schedules for next Summer Schedule Table. I would make "until" exclusive, so that 2014-04-01 means "valid until 2014-03-31". Easier to join chains when necessary.

That calendar-day table could also mark the DST-nightmare, for example with a -1 for the shorter and +1 for the longer day and 0 for normal days. This will also be relevant in Schedule, I think some trains behave differently on DST-switch-days, so same logic: tinyint with "-1" at the shorter day, "+1" at the longer day, "0" at the normal days and NULL if DST doesn't matter for that train.

The queries are, as TMS says, BETWEEN <usertime> - interval 15 minute and <usertime> + interval 15 minute, only extended for all the calendar_day values.

So, when user gives "2014-04-25 16:30", you should query like:

select Sc.* 
from Schedule sc
inner join CalendarDay cd on 
(
   cd.day = date(<userdate>) 
   and (cd.holiday = sc.holiday or sc.holiday is null) 
   and (cd.BankHoliday = sc.BankHoliday or sc.BankHoliday is null) 
   and (sc.DSTSwitch = cd.DSTSwitch or sc.DSTSwitch is null) 
   and sc.`weekday` = weekday(cd.day)
)
inner join ScheduleTable sct on( 
   sct.id = sc.ScheduleTableID 
   and sct.from <= cd.day and sct.until > cd.day
)
where sc.`Time` between 
      time(<userdate>) - interval 15 minute 
      and time(<userdate>) + interval 15 minute
;

It may sound like too many complications, but as long as you don't have ScheduleTables and Holidays and the like, the fields don't desturb you (set them to 0, the ScheduleTableID to 1, which date range goes from year 1000 to year 3000, NULL the restrictions in Schedule). But it is easier to react on those "problems" when they are in the table structure already.

Upvotes: 1

Tomas
Tomas

Reputation: 59475

If you need to represent the rules themselves... the best practice is to represent the data as they are by their own logic. So I would create table Schedule:

Id    EventId    WeekDay    Time
 1          1          0    10:00
 2          1          1    10:00
 3          1          2    10:00
 4          1          3    10:00
 5          1          4    10:00

So for each EventId (which could be something like train id in your case) you could have n rows of schedule. The trick is to use WeekDay as coding of weekday() function - Monday = 0, Tuesday = 1, ... Sunday = 6. Then, performing the query you need is very easy:

select distinct EventId
from Schedule
where `WeekDay` = weekday('2014-04-30') and `Time` > '9:45' and `Time` < '10:15'

Upvotes: 0

Related Questions