Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
marioletto
Starting Member
2 Posts |
 Posted - 2007-03-25 : 22:21:43
|
| hi all,i have what should be a simple problem for most of you.our film festival for the past 10 year has been a "single venue" festival.each film screened one time only in a one of 30 "film programs".life was easy: each movie had a field with the program_id and with constraints like: films.program_id = program.id i could get anything i wanted in a single ADODB.RecordSet.when showing the movie's detail page, i could easily pull the program info (venue, ticket link etc)when showing the showtimes page, i could easily list all movies playing in each program ... and the showtime page was done.now things are changing,each film will play twice (program1_id, program2_id), and the wenues will be 4.what's the most efficient way to do this?here are my (simplified) tables:films (id, title, program1_id, program2_id )programs (id, day_id, program_id, venue )i have a film detail page on which i need to display the two showtimes,i have list page with the list of all films, and their showtimesi have a showtimes page in which i need to list all films sorted by day and film program.i hope this is not too confusing.thanks in advance,regardsmario |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-03-25 : 23:21:59
|
You could have 3 tables: Film, Venue, ShowtimesFilm stores the properties of film. Venue stores the properties of venue. Showtimes stores what films are playing where, and when.Film has these columns: film_id, title, director, is_oscar_nominated, etc.Venue has these columns: venue_id, name, address, number_of_seats, etc.Showtimes has these columns: film_id, venue_id, and showtime (a datetime). This table has a foreign key to each of the other two.
www.elsasoft.org |
 |
|
|
marioletto
Starting Member
2 Posts |
Posted - 2007-03-26 : 14:14:07
|
| hi, thanks for your post. can you tell me what is the advantage in splitting venues and showtimes?thanksm. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-03-26 : 16:00:06
|
so that you don't duplicate the properties of a venue that has nothing to do with when it's showing a particular movie. like where it's located, how many seats it has, etc.
www.elsasoft.org |
|
|
|
|
|
|
|
|
DB for film festival with multiple venues