Define Relationships and Cardinality between Entities in an ERD
We have already seen the different types of relationships but now it is the time to define those. The three types of relationships that we can run into are one-to-one (1:1), one-to-many (1:M), and many-to-many (M:N). The 1:1 relationship is quite rare in database design as this defines that a single row in one table is related to just one row in another table and vice versa. An example of this could be in a company, you could have an employee associated with one spouse and one spouse is associated with one employee. This may be needed for benefits but however, they are quite rare.
One-to-many relationships are the most common relational model relationship. This relationship type should be the norm for most entity relationships. In a company, you could have a department that consists of multiple employees but at any given time, an employee can only belong to one department.
The many-to-many relationship isn’t one that can be implemented in a relational model. If we do have such a relationship between two entities, it should generally be changed into two one-to-many relationships with a bridge/associative/composite table in between them. The linking table in between the two entities will have multiple occurrences of the foreign key values. The same linking table should at least contain the primary keys of the original tables but may have its own primary key as well. An employee could work on multiple projects and a project can have multiple employees involved. Although we have these two tables, we would have to create a linking table in between perhaps called “assignment” to contain the primary key of the employee table and the primary key of the project table. If this bridge entity is not referenced anywhere else, we may simply choose to use the combination of the foreign keys as the primary key. However, if the table is referenced from other tables, creating a whole new primary key will probably be the best choice so that we only have one value to track in those related tables rather than a combination of the other two (or more) keys.
Using Chen’s notation, we signify a relationship between two entities by drawing a diamond with the action listed in the diamond. Alongside each entity on the relationship line, we’ll define the cardinality to show which side is the one and which side is the many if applicable. Let us go back to our movie ratings ERD from the prior tutorial:
In going back to the relationships, we had identified specific relationships. A movie can belong to multiple genres and a genre can have multiple movies.
A user can submit multiple ratings and a rating can only belong to a single user.
A rating is only about a single movie and a movie can be rated multiple times.
Likewise, a movie can have many actors and an actor can act in many movies.
Now bringing that all together, we’ll have the following ERD which now consists of the entities, attributes, and relationships.