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
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
genres tables. These are all colored with the same color because they hold the detail information about a movie.
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
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 separate 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
production_managers and many other similar tables with the same structure as
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
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 portrayed in different movies. Like in the other movies in the series, where Xavier is portrayed 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
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,
presenter and many other fields if we need them.
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.
Thanks a lot for reading 👋👋