This tutorial explores the use of associative entities in an entity-relationship diagram (ERD) in two parts:
- Introduction
- Movie Ratings Example
1. Introduction
Associative entities can have many different names, such as composite entity, bridge entity, or linking tables. They all refer to the same thing: a solution for a many-to-many (M:N) relationship between two (or more in some cases) entities, by creating multiple one-to-many (1:M) relationships. Since this entity is used to link all of the tables that were originally related in an M:N relationship, this associative entity will at the very least contain the primary keys from those entities as foreign keys.
If you remember back in Unit 1 when an associative entity is created, the database designer can set the primary key as the combination of the foreign keys, which is called a composite key. Or, the designer can choose to create a new primary key.
-
In part, this decision depends one whether the table will be referenced from other tables. If it is, creating a new primary key makes more sense. Otherwise, the table that refers to the associative entity would need to link all of the foreign keys, which can be redundant. An associative entity models pure relationships rather than entities.
-
-
- Associative Entities
- Associative entities can have many different names, such as composite entity, bridge entity, or linking tables. They all refer to the same thing: a solution for a many-to-many (M:N) relationship between two (or more in some cases) entities, by creating multiple one-to-many (1:M) relationships.
2. Movie Ratings Example
At the conceptual level, which is where you currently are with your movie ratings database model, it is valid to have many-to-many relationships (M:N), and they are frequently used in the ER modeling process. During the implementation into a logical model, though, you are required to create your associative entities.
Note that you may also add associative relationship attributes in this table, which are attributes that only exists because of the many-to-many (M:N) relationship.
-
EXAMPLE
For example, if you look at the movie and actor relationship in your movie ratings database, you see that it is currently a many-to-many (M:N) relationship:
-
You could create an associative entity that would have the MovieID and ActorID (both of which do not currently exist in the conceptual model) as foreign keys. You could call this associative entity “Role” to indicate the role that the actor plays in the movie. As part of the Role entity, you could add in the RoleName as an attribute that only exists due to the many-to-many (M:N) relationship, as the RoleName is dependent on the actor and the movie. In this case, you could simply use the composite key of the MovieID and ActorID for the primary key, rather than create a new primary key, since no other entity is dependent on the Role entity.
In this tutorial in the introduction, you learned that associative entities break down many-to-many relationships into one-to-many relationships and can contain attributes that only exist due to the many-to-many relationship. You then saw examples of associative entities that could be created within the movie ratings example.
