Need some feedback on a database problem...?

Question

This is a refined version of a question I asked yesterday.

The database in question is for a movie theatre's web site. The company has multiple locations.

Movies table lists all of the movies with relevant data like cast, rating, run time, etc. Each location can play each movie.

Theatre table lists each movie theatre with info like address and phone number

Dates table lists the opening and ending date for each movie at each theatre. The dates are not necessarily the same for each theatre.

Times table lists movie id, theatre id, time start date, time end date, time, auditorium number. Each individual time is a single record, for example:
theatre 1, movie 1, friday, sunday, 1:00, aud 1
theatre 1, movie 1, friday, sunday, 3:00, aud 1
theatre 2, movie 2, friday, sunday, 1:00, aud 3

When dealing with multiple prints (copies) of a movie at the same theatre I just add additional records to the show times table with a different auditorium number. More in the added detail.

I want a better way to deal with multiple prints at the same location because the current method makes it difficult (or very complex) when trying to do some queries for reports.

I basically have two different ideas.

First, add a print (copy) number to the dates table and default to 1 since most movies only have one copy.

Second, add a print (copy) table which would include a start and end date for each print.

Currently when I generate schedules I query dates first so movies are listed newest to oldest, then run a sub-query for times. Although I just realized I can probably do the same thing by querying times with a join for dates and then use the script to process the results.

Answer 1

With the details you've given, it sounds like your first solution makes the most sense (add an aud field to each record that defaults to 1).

However, I'm not 100% what you're you're trying to fix. You said the current queries are too complex but can you give an example? Some questions I have are: What do you see now, How do you build the query for the results and What would you like to see instead?