how to design movies with show times database structure

Question

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

Answer 1

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)

Answer 2

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).