Mysql – how to design movies with show times database structure

database-designMySQL

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

Best Answer

I would also suggest the following:

Add a movie genres table - as a movie can have one or more genres
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)

Related Solutions

Database Design – Optimum Structure for Product Data Database

All you need is two tables. 1 for Products and 1 for Attributes. You would then have a one to many relationship from the Products table to the Attributes table.

This means that each product can have as many attributes as it needs.

Table structure would look something like this.

Products
Product_Id INT PK Auto Increment
ProductCode VARCHAR(255)

Attriutes
Attribute_Id INT PK Auto Increment
Product_Id INT FK
AttributeName VARCHAR(255)

AttributeValues
AttributeValue_Id INT PK
Attribute_Id INT FK
AttributeValue VARCHAR(255)

Mysql – Need some expert eyes to look at the schema

Good start so far. Here are some notes:

a movie can be released on different dates in different cities
please add your primary keys, so we can see them
are images for movies? people? why no order column?
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)
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/

Best Answer

Related Solutions

Database Design – Optimum Structure for Product Data Database

Mysql – Need some expert eyes to look at the schema

Related Question