Use Sophia to knock out your gen-ed requirements quickly and affordably. Learn more
×

ERD Example: Movie Ratings

Author: Sophia

what's covered
This tutorial explores a completed ERD for a Movie Rating database structure in two parts:
  1. Introduction
  2. Improving the Design

1. Introduction

Recall our database design for a movie rating database. It fits the needs of the criteria that you had defined, and the resulting ERD looked like the following:

Movie Rating Database Design Example

2. Improving the Design

This database design works well, but there are some potential issues that could arise. You may want to have a better understanding of some of the criteria, or be able to track individuals that are part of the movie beyond actors. You may also include producers, directors, and so forth. Rather than create a separate table for each, it makes more sense to generalize the actor table to an individual that works on the movie and add a lookup table for the role type. This way, you could also have an individual play multiple roles within the same movie. For example, an actor may also be a producer in the movie.

Another issue that you had discussed was if individuals could add more than one rating to the same movie. If they did, you may want to identify when the rating was submitted to see what direction the rating was going in. Even if the individual submitted multiple ratings, you may only capture the latest rating of the movie from the user in the calculation. As such, you have also added an average rating column in the movie table.

Full Cast Database Design Example

You may notice now that the role type has been added as the 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, you have some added columns that you would typically see in the movie. The user table has the zip code split off. As you discussed, this would be in third normal form (3NF) but it would not be crucial to have implemented. Some of that decision-making is dependent on a balance of redundancy over performance.


summary
In the introduction of this tutorial, you recalled the current design of the movie ratings database created over past tutorials. Then you improved the design by changing some tables to be more generic, adding a few tables, and adding values so the ERD is a more complete movie rating database design than defined earlier.

Source: Authored by Vincent Tran