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.
Get the DISTINCT IDs of all the movies from the
movies_showing
table as per a date constraint(Today, This week).Loop through the result, and inside the loop do a query again on the
movies_showing
table where themovie_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.
images
and any other table. You might want to have some*_images
tables to store relations to images that any entity might have images associated with it (people, movies, trailers, maybe even theatres).