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.
id | title | synopsis | release_date | run_time
id | first_name | middle_name | last_name | biography | dob | gender
id | type (cast/crew) | movie_id | people_id | role | lead_role (yes/no) | order
id | movie_id | desc | src | src_type | order
id | type | resource_id | src | desc
id | name | location | longitude | latitude | city | phone | email | website
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.
Get the DISTINCT IDs of all the movies from the
movies_showingtable as per a date constraint(Today, This week).
Loop through the result, and inside the loop do a query again on the
movies_showingtable where the
movie_idis 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.