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 22

Understanding Composite Entities

Recommended resources

Hello! In our last lesson, we established a crucial rule in database design: many-to-many (M:N) relationships cannot be implemented directly in a relational database. We explored how trying to do so violates fundamental principles like atomicity and leads to significant data management problems.

Now that we've fully diagnosed the problem, this lesson is all about the solution. We will introduce a special tool in the database designer's toolkit created specifically to handle this situation. Your learning outcome for today is to define what a composite (bridge/associative) entity is and explain its purpose. By the end of our session, you'll understand exactly how designers transform a problematic M:N relationship into a clean, stable, and workable structure.

1. Defining the Composite Entity

The solution to the many-to-many problem is to create a new entity that sits between the two original entities. This new entity is known by several names, which are used interchangeably:

  • Composite Entity
  • Associative Entity
  • Bridge Entity or Junction Table (more of an implementation term)
  • Linking Table

This entity's primary role is to act as a bridge, resolving the M:N relationship.

Composite Entities in Database Design
This diagram illustrates the core concept. The problematic M:N relationship between STUDENT and CLASS is resolved by introducing a new composite entity called ENROLL. This transforms the single M:N relationship into two 1:M relationships.

The video below provides a quick, clear introduction to this concept and its various names.

ERD: Associative Entities

This video, "ERD: Associative Entities," by Dustin Ormond, begins by defining what an associative (composite) entity is and the main situations where it is used.

Please watch the first part of the video, from the introduction. Pay attention to the different names used for this type of entity and the primary reason for its creation: to resolve many-to-many relationships.

As you can see, the core idea is to introduce a new entity that represents the relationship itself. For a more formal definition, it's helpful to see how textbooks categorize these entities. Some sources refer to them as dependent entities because their existence depends on the other entities they connect.

Kinds of Entities

This section from the "Chapter 8 The Entity Relationship Data Model" open textbook describes different kinds of entities.

Please read the description for Dependent entities. Notice how it describes them as connecting two "kernels" (independent entities) and being "existence dependent" on them. This is the formal classification for the composite entities we are discussing.

2. The Purpose: Decomposing M:N into 1:M

Now that we have a definition, let's focus on the primary purpose of a composite entity: to break a single M:N relationship into two 1:M relationships. Relational databases are built to handle 1:M relationships perfectly using primary and foreign keys, so this decomposition solves the implementation problem we discussed in the last lesson.

Let's revisit our STUDENT and COURSE example:

  • A STUDENT can enroll in many COURSEs.
  • A COURSE has many STUDENTs.

We create a composite entity, let's call it ENROLLMENT. Each time a specific student enrolls in a specific course, we create one new record in the ENROLLMENT table.

This results in the following structure:

  • STUDENT ---< (1:M) --- ENROLLMENT
  • ENROLLMENT --- >(M:1) --- COURSE

Let's analyze these new relationships:

  1. STUDENT to ENROLLMENT (1:M): One student can have many enrollment records (one for each course they take). But each enrollment record is for exactly one student.
  2. COURSE to ENROLLMENT (1:M): One course can have many enrollment records (one for each student in the class). But each enrollment record is for exactly one course.

This structure works perfectly with the foreign key mechanism. The ENROLLMENT table will contain a foreign key to STUDENT and a foreign key to COURSE.

The following article explains this process of "normalization" and why it is so crucial for good database design.

How to Handle Many-to-Many Relationships

This guide from Creately provides a step-by-step explanation of resolving M:N relationships and clarifies why this process is so important.

Please read the following three sections: Start with "What Are Many-to-Many Relationships?" and pay close attention to the sub-section "Normalization: The Key to Handling Many-to-Many Relationships." Next, read "How to Show Many-to-Many Relationships in ER Diagrams." This provides a clear, step-by-step guide to the decomposition process. Finally, read "Why Many-to-Many Relationships Matter in ER Modeling." This section explicitly states the purpose of using composite entities: to capture real-world complexity and prevent data redundancy and inconsistencies.

3. A Worked Example and Key Characteristics

Let's see this in action with a slightly different example to solidify your understanding. The video we watched earlier continues with a scenario involving employees and training courses.

ERD: Associative Entities

This segment continues the "ERD: Associative Entities" video, applying the concept to an Employee/Course relationship.

Watch the next segment from the example. Observe how the M:N relationship between Employee and Course is resolved by introducing a new entity called Certificate right in the middle.

This example helps highlight another powerful feature of composite entities: they can have their own descriptive attributes.

Notice in the diagram from the start of the lesson that the ENROLL entity has an attribute called ENROLL_GRADE. This ENROLL_GRADE doesn't describe the student (a student has many grades) and it doesn't describe the class (a class has many grades). It specifically describes the relationship between one student and one class. This is information that has no logical home in either the STUDENT or CLASS table but fits perfectly in the composite entity.

This leads us to the key characteristics of a composite entity:

  • Resolves M:N Relationships: Its primary purpose is to decompose one M:N relationship into two 1:M relationships.
  • Existence-Dependent: It depends on the entities it connects. An ENROLLMENT record cannot exist without the STUDENT and COURSE it links.
  • Contains Foreign Keys: It always contains the primary keys of the two entities it connects, which serve as foreign keys.
  • Can Have its Own Attributes: It can store attributes that describe the relationship itself (e.g., EnrollmentDate, Grade, CertificateDate).

Conclusion

In this lesson, we have introduced the standard and elegant solution to the many-to-many problem. You now know the terminology and the fundamental purpose of a composite entity.

Key Takeaways:

  • A composite entity (also known as an associative, bridge, or junction entity) is created to resolve a many-to-many relationship.
  • Its purpose is to decompose the M:N relationship into two 1:M relationships, which can be easily implemented in a relational database using foreign keys.
  • This process avoids the data anomalies (insertion, update, deletion) we discussed previously and allows for a clean, scalable database design.
  • A composite entity is existence-dependent and can hold its own attributes that describe the relationship it represents.

Now that you understand what a composite entity is and why it's used, our next lesson will focus on the practical details. We will examine how the primary key of a composite entity is constructed and begin the process of converting M:N relationships into resolved structures on an ER diagram.