Use Sophia to knock out your gen-ed requirements quickly and affordably. Learn more
Become a Member Try for Free
×

ERD Example: Movie Ratings

Author: Sophia
PDF Version

what's covered
In this lesson, you will explore a completed ERD for a movie ratings database structure, in two parts.

Table of Contents

1. Introduction

Recall our database design for a movie ratings database from earlier challenges. While the ERD looked complex, in the end, the design fit the needs of the criteria that had been defined, and the resulting design looked like the following:

An E R D model showing seven tables named User, Rating, Movie, MovieGenre, Genre, Role, and Actor. The attributes listed under the User table are UserId, primary and foreign key; UserFirstName; UserLastName; Phone; Email; Address; City; State; and Zip. The attributes listed under the Rating table are RatingId, primary key; UserId, foreign key; MovieId, foreign key; NumericRating; and TextualRating. The attributes listed under the Movie table are MovieId, primary key; MovieTitle; and ReleaseDate. The attributes under the MovieGenre table are MovieId and GenreId, both the primary and foreign keys. The attributes under the Genre table are GenreId, which is the primary key, and GenreName. The attributes under the Role table are MovieId and ActorId, both the primary and foreign keys. The attributes under the Actor table are ActorId, primary key; ActorFirstName; and ActorLastName. There are lines connecting the User table to the Rating table, the Rating table to the Movie table, the Movie table to the MovieGenre and Role tables, the MovieGenre to the Genre table, and the Role table to the Actor table. Each line has a crow’s foot symbol and two perpendicular lines on either side.

2. Improving the Design

This database design works well, but some potential issues could arise. Understanding the business logic and processes better might lead us to expand the schema so it could track non-actor individuals who worked on each movie, such as producers, directors, and costume designers. With each type of work, it might make more sense to generalize the actor table to hold all the personnel on the movie and then add a role_type attribute and a lookup table for its possible values. This way, you could also have an individual play multiple roles within the same movie.

You might also want to reconsider the question of whether one user can review one movie multiple times and give it different ratings and reviews each time. If that is desirable, you might want to add a date and time of submission for each review. You might further want to add an average rating column to the movie table and have it be a derived value from data across all ratings for that movie. Further, in cases where one person rates a movie multiple times, you might want to include only their latest review of the movie when calculating the average; you could determine which is the latest by checking the submission date and time.

An E R D model showing nine tables named User, ZipCode, Rating, Movie, MovieGenre, Genre, RoleType, Role, and Person. The attributes listed under the User table are UserId, primary and foreign key; UserFirstName; UserLastName; Phone; Email; Address; UserName; Password; and Zip, foreign key. The attributes listed under the Rating table are RatingId, primary key; UserId, foreign key; MovieId, foreign key; NumericRating; TextualRating; and ReviewDate. The attributes listed under the Movie table are MovieId, primary key; MovieTitle; ReleaseDate; Length; Description; and AverageRating. The attributes under the ZipCode table are Zip, primary key; City; and State. The attributes under the MovieGenre table are MovieId and GenreId, both the primary and foreign keys. The attributes under the Genre table are GenreId, which is the primary key, and GenreName. The attributes under the RoleType table are RoleTypeId, which is the primary and foreign key, and RoleTypeName. The attributes under the Role table are MovieId, RoleTypeId, and PersonId, which are all primary and foreign keys, and RoleName. The attributes under the Person table are PersonId, primary key; PersonFirstName; and PersonLastName. There are lines connecting the User table to the Rating and ZipCode tables, the Rating table to the Movie table, the Movie table to the MovieGenre and Role tables, the MovieGenre to the Genre table, and the Role table to the Person and RoleType tables. Each line has a crow’s foot symbol and two perpendicular lines on either side.

In the revised schema shown above, the role type is a lookup table, which will include all of the types of roles that an individual can play in a movie, allowing a lot more flexibility. In the movie table, there are some added columns that you would typically see in the movie. The user table has the ZIP code split off. As we discussed, this would be in third normal form (3NF), but it would not be crucial to have it implemented. Some of that decision making is dependent on a balance of redundancy over performance. The classic trade-off is between performance and data complexity.

summary
In this lesson's introduction, you recalled the current design of the movie ratings database created over past lessons. Then, you improved the design by changing some tables to be more generic, adding a few tables and values so the ERD is a more complete movie ratings database design than defined earlier. The final design represented the classic trade-off between performance and complexity when working with a business process flow.

Source: THIS TUTORIAL WAS AUTHORED BY DR. VINCENT TRAN, PHD (2020) AND Faithe Wempen (2024) FOR SOPHIA LEARNING. PLEASE SEE OUR TERMS OF USE.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC.