I'm building a tv series scheduling app in Ruby on Rails which performs certain actions based on what's currently on tv. Users can create a series, which has a start and end date, and can create broadcasts for those series. Broadcasts can be on one of three channels, and are in the form of "every Tuesday, on channel 2, from 10:00:00 until 14:00:00".
Currently, I'm storing these broadcasts in MySQL with the following columns: a wday
integer, a starts_at
and ends_at
time field, and a channel_id
integer. So the aforementioned broadcast would be stored with wday
2, channel_id
2, starts_at
"10:00:00" and ends_at
"14:00:00", and a reference to the event it belongs to. Broadcasts can "cross" midnight, so the app should allow broadcasts with start "23:00:00" and end "02:00:00", being 2 in the morning the next day.
I'm having some serious trouble with the following two issues:
Only one broadcast can be scheduled on a specific time on a specific channel. The app needs to validate user input, and check whether a broadcast exists for the specific channel and times.
The app needs to show the currently shown broadcast, if any, for each of the three channels. Basically, for each channel, find a broadcast with starts_at < current time < ends_at. Obviously, this needs to work with broadcasts crossing midnight as well.
I'm not sure at all whether using time fields for start/end times is the best solution. Bear in mind, I need to store time values, not dates, since a broadcast can occur every Tuesday for the duration of the series.
How would you store these broadcasts, allowing validation and allowing querying around a specific time, allowing broadcasts to "cross" midnight?