Hello! In our last lesson, we focused on the structure of a composite entity, specifically how its primary key is formed. You learned that it's typically a composite key made from the foreign keys of the parent entities it links.
Now, we'll take a step back to look at the bigger picture. This lesson is all about the practical process of using that composite entity to fix a common problem in database design. Your goal for this lesson is to learn how to convert an M:N relationship into two 1:M relationships using a composite entity. We'll move from understanding the components of a composite entity to mastering the procedure for implementing it.
1. The Problem with Many-to-Many (M:N) Relationships
As a brief refresher, a many-to-many (M:N) relationship exists when one record in an entity can be related to many records in another entity, and vice-versa. A classic example is STUDENT and COURSE: a student can enroll in many courses, and a course can have many students.
The fundamental issue is that relational databases cannot directly implement this kind of relationship. Trying to do so by, for instance, putting a course_id column in the STUDENT table would require storing a list of IDs in a single field, which violates the principle of atomicity (First Normal Form). This leads to significant problems with data updates, insertions, and deletions.
The following video provides a concise and clear explanation of this problem and introduces the standard solution.
This segment from the "Entity Relationship Diagrams" video by Decomplexify explains why M:N relationships are problematic and how they are resolved.
Please watch the section from this timestamp. Focus on the core argument: you can't express an M:N relationship with a single foreign key, so a new entity must be introduced to break it down into two manageable 1:M relationships.
2. The Conversion Process: A Step-by-Step Guide
The solution, as the video highlighted, is to introduce an intermediary table—our composite entity (also known as an associative entity or junction table). This entity transforms the single M:N relationship into two 1:M relationships.
Let's formalize the conversion process. The following article provides an excellent, structured guide.
How to Show Many-to-Many Relationships in ER Diagrams
This article from Creately provides a clear, step-by-step method for resolving M:N relationships.
Please read the section titled "How to Show Many-to-Many Relationships in ER Diagrams". It outlines five steps, but we will focus on the core process in steps 1 through 4. Pay close attention to how the new associative entity is created and how the relationships are redrawn with new cardinalities.
To summarize and reinforce the steps from the article:
- Identify the two entities involved in the M:N relationship (e.g.,
STUDENTandCOURSE). - Create a new composite entity between them (e.g.,
ENROLLMENT). - Break the original M:N relationship line.
- Draw two new relationship lines:
- One from the first original entity to the new composite entity.
- One from the second original entity to the new composite entity.
- Assign cardinalities. Both of these new relationships will be one-to-many (1:M). The "one" side is at the original entity, and the "many" side is at the new composite entity.
Conceptually, the transformation looks like this:
STUDENT (M) --- (N) COURSE
becomes
STUDENT (1) --- (M) ENROLLMENT (N) --- (1) COURSE
3. Visualizing the Result
Let's look at the result of this conversion in a complete ERD. The diagram below shows the "after" state for the STUDENT and CLASS relationship.
This diagram neatly brings together the concepts from our last two lessons:
- The M:N relationship is broken into two 1:M relationships.
- A new composite entity (
ENROLL) is created. - The primary key of the composite entity is a combination of the foreign keys referencing the parent entities (
STUDENTandCLASS).
4. Practice the Conversion
Now it's time to see this process applied in a couple of different scenarios. The following video walks through two examples. For the second one, I encourage you to pause and try to solve it yourself before watching the solution.
This video by Dustin Ormond provides clear, worked-out examples of converting M:N relationships.
First, watch the demonstration of converting the relationship between Employee and Course using a Certificate entity (from here). Pay close attention to how the cardinalities from the original M:N relationship are moved to the new relationships with the composite entity. Next, the video presents an exercise involving an Order and Product relationship. When the presenter says to pause the video at 00:03:44, please do so. Try to sketch out the solution yourself based on the steps we've covered. Then, continue watching the solution to compare your answer.
Conclusion
In this lesson, you've learned the essential technique for handling many-to-many relationships in a relational database model. By converting them into two one-to-many relationships, you create a structure that is logical, implementable, and follows normalization principles.
Key Takeaways:
- Many-to-many (M:N) relationships cannot be directly implemented in relational databases and must be resolved.
- The resolution process involves creating a composite (or associative) entity that sits between the two original entities.
- The single M:N relationship is replaced by two 1:M relationships, with the "many" side of each relationship pointing to the new composite entity.
- This new structure allows the database to accurately and uniquely track each instance of the association between the two original entities.
You now have a solid understanding of both the structure of a composite entity and the process for using it to resolve M:N relationships. In the next lesson, we will put this knowledge into practice by drawing a complete ERD that includes a composite entity to resolve a given scenario.