0

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.

Improve this question
2
  • Take a look at what's already been done in Open Source - this post will hopefully give you a good start.
    – Vérace
    Jun 9, 2014 at 11:59
  • Without knowing more detail about what you're planning to do, it's hard to say. The only thing that seems to be obviously missing is a link between 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).
    – FrustratedWithFormsDesigner
    Jun 9, 2014 at 15:26

1 Answer 1

Reset to default
2

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/

Improve this answer
1
  • Thank you for your advice. No need for cities, its based on theaters. Images are for both movies and people, so I have the resource_type(movie/people) and the ID(foreign key) of the resource. I just need a simple show time system with a start and an end date. Yes, location is the address. Anyway, since my question is vague, I have edited and added a specific query that I think will give me some problems. Could use all the help :)
    – WebNovice
    Jun 9, 2014 at 19:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.