You may remember our database design for movie rating database design. It fit the needs of the criteria that we had defined and the resulting ERD looked like the following:
This database design works well but we had brought up some potential issues that could arise. In part, we want to have a better understanding of some of the criteria as well as be able to track individuals that are part of the movie beyond actors. We want to consider the entire cast rather than actors and actresses. We 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 we had discussed was with the ratings and if individuals could add a rating to the same movie. If they did, we 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, we may only capture the latest rating of the movie from the user in the calculation. As such, we 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, we have 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 but it would not be crucial to have implemented. Some of that decision-making is dependent on a balance of redundancy over performance.