Today we are going to create a database schema for a movie catalog. It could be used for a web-site like IMDB or a desktop software to catalog movies.
Our main table to store information about movies is the Movie table.
This table holds the main aspects of the movie like the following:
- Title of the movie
- Release date
- Run time length of the movie as minutes
- IMDB id of the movie
- MPA Rating of the movie (G, PG, PG-13, R, NC-17)
This table has a reference to languages table which holds the language of the movie.
Next we have the plots, languages and genres tables. These are all colored with the same color because they hold the detail information about a movie.
The plots table has a many-to-one connection to the movies table and one-to-one connection to the languages table. This is because a movie can have multiple plots in different languages. The languages table is referenced by the movies table as we have mentioned above. This is for the language of the movie it self.
We have a many-to-many connection between movies and genres tables. This is because a movie could have multiple genres. There is a movie_genres table to be able to establish the many-to-many connection.
There are many ways to represent people associated with a movie. We could have seperate tables for directors, producers, actors and actresses and these tables could hold that persons details. However we chose to create a single people table to represent any person and have various join-tables to represent the association of the specific person to the movie.
Any person who is not an actor/actress is represented through a record in the people table and a connection through directors or producers to the movies table. Depending on our needs, we could add writers, executive_producers, production_managers and many other similar tables with the same structure as producers and directors tables. A foreign key for people and a foreign key for movies is all that is needed.
To represent the actors in the database, we will do something different than directors and producers. An actor plays a character in a movie. Sometimes a character is played by different actors. For example in X-Men: First Class (2011), James McAvoy plays the 30 year old Charles Xavier role and Laurence Belcher plays the young Xavier role. It is also possible that the same character is portreyed in different movies. Like in the other movies in the series, where Xavier is portraied by Patrick Stewart.
To be able to model this scenario where a character is not specifically tied to a movie, we have characters table which is associated with the movies table through a join-table.
Note: The correct plural form of person is people, although you can name the people table persons if you want to keep the naming consistent.
Finally we have the awards table. This table would hold the list of awards (94th Academy Awards, 79th Golden Globe Awards, Palme d'Or etc). It has name and year fields of the award, however we could add, ceremony_date, ceremony_location, presenter and many other fields if we need them.
The awards table is associated with the movies table through the movie_awards table. The category field would hold the name of the award (Best Picture, Best Actor, Best Supporting Actress etc). won_awards field is a boolean field that represents if the award is won or if it is just a nomination.
We came to the end of our tutorial. You can open this sample movie catalog schema in dbmodeller and use it in your own projects.
Top comments (0)