Before we can begin to create tables as entities in an ERD, we need to start to work with some of the basic criteria and business rules to define a specific scenario. Let us look at a movie rating scenario where a new database will have to be created. We will explore some of those details of the final results as we progress in the tutorials.
An organization would like to create a movie rating application where users would be able to rate movies on a numeric scale as well as provide textual feedback about the movie. The date that the review was submitted should also be stored. As part of the movie, there are some details about it including the actors, movie title, description, and release date. A movie can belong to multiple genres while a genre can also have multiple movies. The user should log into the system using a username/password combination but also store their first name, last name, and date of birth.
This scenario gives us some information to first define what some of the entities are for us to create in an ERD. It is important that we stick with the details in the scenario rather than adding in every detail that the organization may not need. For example, a movie has many other attributes like the producers, directors, and other individuals involved. However, the organization is not asking for those details to be tracked. Some of the details may need some of our domain knowledge to help define some of the relationships as well. Let’s take a look at what some of these entities would be by identifying the business rules from the scenario:
The next step once we have the entities is to display the direct relationships between the tables. Here, we will have a relationship between the user and rating, rating and movie, movie and actor, and then movie and genre:
This gets us to the first step by identifying the entities for our database.