I need some input on a MySQL database project...?

Question

This is for the database for a movie theatre's website. The company has multiple theatres.


Problem: handling dates/show times for multiple prints of the same movie.

Current set up:
-One table for movies includes all movie specific information like rating, cast, web sites, etc. One record per movie.

-One table for dates includes the start & end date for each theatre as well as the no pass period. One record per theatre per movie.

-One table for times. Includes the auditorium number, sound format, and time. One record for each time/date set since the theatres do not run the same schedule all week. For example Fri-Sun 1:00 is one record, Fri-Sun 3:00 is a second record, Mon-Tue 3:00 is a third record.

More in the additional detail.

Currently when I have multiple prints of a movie, I simply create a time set for a separate auditorium. However, I've found that limits functionality.

What I'm considering is adding a column for print number to the dates table (basically 1, 2, etc). By default all dates would be print 1. If a second print were desired then on the date input form you create an additional print.

I'm just looking for input as to whether this is the best way to do this or if I should go a different route.

quantumkev: print means a copy of the movie. With major blockbusters it's common to have more than one copy of the movie allowing it to play in multiple auditoriums to offer more showings/seats.

Answer 1

I follow everything except the "print" part - when you say "print", what exactly are you referring to? Another version of the same movie? A different set of times? I just drew an ERD out real quick - what about something like this :
Table - Movie
PK - movie title
Table - Theatre
PK - theater name
Table - Schedule
PK - date
FK - movie title
FK - theater

In this scheme, you have a many to many relationship between 'Theater' and 'Movie' that is resolved by an associative entity 'Schedule'. The 'Schedule' table has a record for each movie in each theater for each date. Again - I'm not sure what the whole "print" thing is, but this may work, eh?

Answer 2

You didnt pose a question or enough info about the setup of your tables. So I'm going to throw this suggestion out hoping it may help.

How about:
1 tbl for movies
1 tbl for theaters
1 (many-many) tbl linking movies to theaters and this linking table holds the movie times...