How to set up multiple dates for schedule?

shonuff

Hi. I have a question about how to set up dates in my mysql database. I am setting up a show schedule database for a friend at a local college tv station. This would normally be pretty straight forward and easy. The problem lies in the fact that their show lineup is not huge, therefore they air most of their shows that the students produce up to 5 times a day and also some of these shows -not all- come on all week long (Sunday thru Saturday) and some only come on one or two days out of the week.

My database (so far) contains the following tables:

wutv_shows

show_id
show_name
show_desc

wutv_times

time_id
show_time

So my question is, how to set up the rest of the database for multiple dates per show. I have worked with dates and times before in php/mysql, just not for an erratic show schedule such as this. Is it possible and what would be the best method?

Here is the look I am trying to achieve: http://www.wbtv.com/whatson/

Here's a screencap of what I have achieved so far...
http://img441.imageshack.us/img441/1030/screencapture10162005112153pm3.jpg

As you can see, shows can come on multiple times per day and multiple days per week. This shouldn't be that hard but I can't seem to wrap my thick head around a solution. :-/

wreed

I would do two more tables...

wutv_shows

show_id
show_name
show_desc

wutv_times

time_id
show_time

wutv_dates

dates_id
dates_date

LINKED TABLE
linked_id
show_id
dates_id
time_id

This way you can have multiple dates, times for a show.

shonuff

I don't understand could you elaborate on that a last table a little more?

Keep in mind that not only do these shows come on more than once a week, they also come on up to 5 times a day, and sometimes these shows only air once per week at one set time. Confusing, huh?

This schedule of his is a fill in until he gets satellite programming this winter so any ideas to make this as flexible as possible would be great.

My head hurts... :-/

wreed

I understand what you are saying...

basically you have a one to many link.

the first three tables will have only unique entries, where the last table can have many.

think about it in data view...

wutv_shows

show_id 1 2
show_name Lost Sopranos
show_desc Survivors stranded on a lost island NJ Mafia.

wutv_times

time_id 1 2 3
show_time 9:00 9:30 10:00

wutv_dates

dates_id 1 2
dates_date 10/18 10/19

LINKED TABLE
linked_id 1 1 1

show_id 1 1 1
dates_id 1 1 2
time_id 1 2 1

Do you understand?
In linked table that first column is Show Lost on 10/18 at 9 am, then the next one is same show at 9:30 am. Third column is Lost on 10/19 at 9 am....get the idea???

Thats the best way I can explain it.

shonuff

Ah, I understand now. Thanks for taking the time to explain. I'm pretty sure I know how to set up this linked table with TINYINT's and I guess linked_id is the primary key?

Looks like I'm gonna have some complex queries to build. :queasy:

Can anybody give me some pointers with working with linked tables in mysql/php? :-)

Thanks, wreed for the help.

wreed

No problem, post here when you get to the queries, just use inner joins....I had a project just like this but with audition times, where times are linked to a date.

This way that I explained is SQL normalization. You could do it in a few tables but you would be duplicating a lot of data. This way you are not. I am not sure you want to use TinyINT cause those Ids could get larger one day...maybe just safely use INTs.

Post query help here when you get to it and I will try my best to help.

In the long run this way will make it more object oriented 🙂

shonuff

Another question. In these tables..

wutv_times

time_id 1 2 3
show_time 9:00 9:30 10:00

wutv_dates

dates_id 1 2
dates_date 10/18 10/19

The example values you provided, for example, in wutv_dates, dates_date are those values just input into the database and referenced by a corresponding ID or did you somehow manage to get 2 different dates in one row? Just curious, yet kind of important. 😃

Sxooter

I don't think you need time AND dates in separate tables. Couldn't all your dates / times be in one table as a timestamp type and work just as well here?

shows

show_id int PK, show_name text, show_length interval, show_info text, ..., unique (show_id, show_length)

schedules

sched_id int PK, show_id int, start_time timestamp, length interval, FK (show_id,length) references (shows.show_id, shows.show_length)

With a database with constraints, you could then create a constraint on schedule that wouldn't allow you to schedule two shows at the same time. If you're using MySQL, then you might be able to use some new trigger feature or something to do it, but I wouldn't know, I've never used MySQL's triggers / rules or whatnot.

In PostgreSQL, Oracle, DB2, or Firebird it would be quite easy to build such a constraint. And with proper indexing it would still be pretty fast for checks on insert.

I do think you could use a time slots table and hosts table to keep track of who would be playing what and such:

hosts

host_id int PK, hostname text

time_slots

timeslot_id int PK, host_id int FK on host.host_idtime_start timestamp, time_length interval

That's all I can come up with. Does your version of MySQL support intervals and operations to detect overlap and such? If so, intervals can be a great way of handling this data, otherwise you might have to work it out in epoch (seconds since Jan 1 1972 or something like that) which is workable, but a bit harder to debug when something goes wrong on a switch to DST or stuff like that that date / time types handle automagically.