Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 25

Modeling Many-to-Many Relationships with Composite Entities

Recommended resources

Hello! In our previous lesson, we established the process for converting a many-to-many (M:N) relationship into two one-to-many (1:M) relationships using a composite entity. You learned the "why" and the "how" of this fundamental transformation.

Today, we will focus on putting this knowledge into practice visually. The goal is to synthesize everything we've covered about composite entities into a final, complete diagram. By the end of this lesson, you will be able to draw an ERD that includes a composite entity to resolve an M:N relationship, correctly representing all the necessary components like keys and cardinalities.

1. From Theory to Diagram: The Final Picture

Let's revisit the classic M:N relationship: a STUDENT can enroll in many COURSEs, and a COURSE can have many STUDENTs.

In the last lesson, we determined this structure must be resolved by introducing a composite entity, which we might call ENROLLMENT. Now, let's see what the final ERD for this solution looks like.

Resolving Many-to-Many Relationships with a Join Table (Composite Entity)
This ERD shows the `student` and `course` entities linked through the `student_course` composite entity. This resolves the original M:N relationship.

Let's break down this diagram to understand how it represents the solution:

  • Entities: We have the two original entities, student and course, plus the new composite entity, student_course.
  • Relationships & Cardinality: The original M:N relationship is gone. It has been replaced by two 1:M relationships, shown with Crow's Foot notation:
    • One student is related to many student_course records.
    • One course is related to many student_course records.
      The "many" side (the crow's foot) of both relationships connects to the composite entity.
  • Keys:
    • student has its primary key (PK), student_id.
    • course has its primary key (PK), course_id.
    • The student_course entity contains student_id and course_id as foreign keys (FKs) that reference the original tables.
    • Crucially, the combination of student_id and course_id serves as the composite primary key for the student_course table, ensuring each enrollment record is unique.

This diagram is the standard way to visually represent the resolution of an M:N relationship.

2. Recognizing the Pattern in a Larger Design

Composite entities are a common feature in database designs. It's useful to be able to spot them within a more complex ERD.

Take a look at the ERD for a sales system below. Can you identify the composite entity and the M:N relationship it resolves?

Sales System ERD with Composite Entity
A more complex ERD for a sales system, demonstrating a composite entity in context.

In this diagram, the Sales_details entity is the composite entity. It resolves the M:N relationship between Products and Sales.

  • A single sale (one Sales record) can include many different products.
  • A single product (one Products record) can be part of many different sales.

Notice how Sales_details follows the exact same pattern as our student_course example:

  • It links Sales and Products.
  • Its primary key is a composite of foreign keys from the tables it links (sales_nrFK and product_codeFK).
  • It breaks the M:N relationship into two 1:M relationships.
  • It also holds an attribute specific to the relationship: quantity. You can't store the quantity in the Sales table (because a sale has multiple products) or the Products table (because the quantity varies per sale). It belongs on the composite entity.

3. Practice: Drawing the Solution

Now it's your turn to apply this pattern. The following video presents a scenario for a sports database. You will be asked to resolve an M:N relationship between a Player and a Game.

ERD: Associative Entities

This video provides a hands-on exercise to practice resolving an M:N relationship in a sports context.

Please watch the section covering the sports database example, starting from this timestamp. The presenter identifies an M:N relationship between Player and Game. At around 00:05:10, before the presenter starts drawing the solution, pause the video. On a piece of paper or in a simple drawing tool, sketch the ERD that resolves this relationship. Make sure your drawing includes: A new composite entity between Player and Game. Give it a meaningful name (e.g., PLAYER_GAME_STATS). The two new 1:M relationships with correct Crow's Foot cardinality. The primary keys for all three entities. Remember how the composite entity's PK is formed. The foreign keys in the new composite entity. Any other attributes that might describe the relationship between a player and a specific game (the video will give you some ideas). Once you have your sketch, continue watching the video to compare your solution with the one presented.

This exercise is a direct application of the skills for this lesson. Don't worry about making the drawing perfect; focus on getting all the components (entities, relationships, keys, cardinalities) correct.

Conclusion

You have now brought together several key concepts to create a complete and correct ERD for one of the most common design challenges.

Key Takeaways:

  • To draw the resolution of an M:N relationship, you replace it with a composite entity and two 1:M relationships.
  • The Crow's Foot symbol (the "many" side) for both new relationships always points towards the composite entity.
  • The primary keys of the original entities become foreign keys in the composite entity.
  • These foreign keys typically form a composite primary key for the new entity.
  • The composite entity is also the correct place to store any attributes that describe the relationship itself (like EnrollmentDate or Quantity).

In our next lesson, we will broaden our scope. You'll be given a multi-paragraph business scenario and tasked with identifying all the entities, attributes, and relationships (1:1, 1:M, and M:N) from scratch to build a complete ERD. This lesson has given you the final tool you need to tackle those M:N relationships when you find them.