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:
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.
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.
Source: Authored by Vincent Tran