Need some expert eyes to look at my schema

Question

I am trying to make an online site for local movies. Will be written in PHP and will be using MySQL as the database.

What I want is to create a mini IMDB type site, with movies, people(casts/crew), and show which movies are showing at theaters. (Genres not required).

Have learned from experience that getting the database schema wrong can cause a lot of headache and time later on, especially when it comes to complex queries. So, would appreciate if I could get some advice from DBA experts.

So far, this is what I have come up with.

Database Schema

movies:

id | title | synopsis | release_date | run_time

people:

id | first_name | middle_name | last_name | biography | dob | gender

movies_people:

id | type (cast/crew) | movie_id | people_id | role | lead_role (yes/no) | order

trailers:

id | movie_id | desc | src | src_type | order

images:

id | type | resource_id | src | desc

theaters:

id | name | location | longitude | latitude | city | phone | email | website

movies_showing:

id | movie_id | theater_id | start_date | end_date | description (time, price, etc.)

Example Query: Retrieve all currently showing movies along with all the theaters they are showing at.

I have the following steps in mind.

1. Get the DISTINCT IDs of all the movies from the movies_showing table as per a date constraint(Today, This week).

2. Loop through the result, and inside the loop do a query again on the movies_showing table where the movie_id is the ID in the loop, to get all the theaters for that movie.

But this would result in a lot of queries hitting the DB.

Answer 1

Good start so far. Here are some notes:

1. a movie can be released on different dates in different cities
2. please add your primary keys, so we can see them
3. are images for movies? people? why no order column?
4. movies schedules are more complex than you've got so far. You will want to calculate show times based on some recurrence rules (see stackoverflow for recurrence posts)
5. what is 'location' in theatres? the address?

Regarding your questions at the bottom, don't loop. You can almost always do everything using SQL and its set logic. Make sure you understand joins:

http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/