Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

how to design movies with show times database structure

I'm building simple local cinemas guide website using Yii PHP framework.

I need Correct and improve this database, specially for showtimes

Cinema / done
City / done
Country / done

Movie
  id
  title
  year
  imdb_rate
  cast
  director
  summary
  views
  featured
  available_status

Genre
  id
  name
  movie_id

movie_showing
  id
  available_status
  show_time <li>20:00</li><li>22:00</li>
  movie_id  
  cinema_id

movie_video
  id
  youtube_uri
  movie_id
  trailer

movie_photo
  id
  photo_uri
  poster 0 1
  movie_id
share|improve this question
    
I'm not sure what value is added by breaking out the video and photo information, since they will ALWAYS pertain only to a specific movie, and not be reused –  JNK Mar 21 '12 at 19:35
    
Also I would make Genre_ID a field in the movie table, not vice versa –  JNK Mar 21 '12 at 19:36
    
@JNK you are right –  Abudayah Mar 21 '12 at 19:38
    
@JNK: It might be useful if some movies have more than one photo. –  FrustratedWithFormsDesigner Mar 22 '12 at 15:10
add comment

2 Answers

I would also suggest the following:

  1. Add a movie genres table - as a movie can have one or more genres

  2. Split up the movie_showing table as follows:

    • movie_showing - id, movie_id, cinema_id, details and restrictions at cinema level (this means the movie is showing at a specific cinema)

    • movie_showing_times - movie_showing_id, availability_status, showdate, showtime, show details (prices, cinema showing, 3D or not, other offers for example half price etc)

share|improve this answer
add comment

Why does Genre have a movie_id field? I think it would make more sense for Movie to have a genre_id field. That way you don't have to duplicate the same genre for every movie that is of that genre.

Also, movie_showing tells me what time the movie is at, but not on what day. I would suggest adding a movie_date field, unless you want to assume that it is always sysdate (but that would make it hard to know what days a movie showed on in the past, if you ever want to look up historical data and do reports).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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