Lesson 11	Resolving many-to-many relationships
Objective	Convert many-to-many relationships into one-to-many relationships.

Converting Many-to-Many Relationships to One-to-Many Relationships in Data Modeling

Question: How does one convert many-to-many relationships into one-to-many relationships during the data modeling process?
In this article, we will discuss the process of converting many-to-many relationships into one-to-many relationships during the data modeling process. We'll delve into the importance of such conversions, illustrate the process with practical examples, and provide a step-by-step guide on how to accomplish this transformation efficiently.

Understanding Many-to-Many Relationships

Many-to-many relationships occur when multiple records in one table are associated with multiple records in another table. This relationship type can lead to redundancy and complications when querying the database. Therefore, it's essential to convert many-to-many relationships into one-to-many relationships to ensure data integrity and improve query performance.

The Conversion Process: Introducing an Association Table

The key to converting many-to-many relationships into one-to-many relationships lies in creating an association table (also called a junction table or linking table). This table stores the relationship between the two original tables by referencing their primary keys as foreign keys. The association table itself has a unique composite primary key, composed of the foreign keys from the original tables.

Step-by-Step Conversion Guide

Follow these steps to successfully convert many-to-many relationships into one-to-many relationships:

Identify the many-to-many relationship: Analyze your data model and identify the tables involved in the many-to-many relationship.
Create an association table: Define a new table with a unique name that represents the relationship between the original tables.
Add foreign keys: Reference the primary keys of the original tables as foreign keys in the association table.
Create a composite primary key: Define a unique composite primary key in the association table, which consists of the foreign keys from the original tables.
Move additional attributes (if applicable): If there are any attributes specific to the relationship, transfer them to the association table.

Example: Converting a Many-to-Many Relationship

Consider an e-commerce platform with two tables: Customers and Products. There is a many-to-many relationship between these tables, as customers can purchase multiple products, and products can be purchased by multiple customers.
To convert this relationship into one-to-many relationships, we'll follow the steps outlined above:

Identify the many-to-many relationship: Customers and Products
Create an association table: Order_Items
Add foreign keys: customer_id (from Customers) and product_id (from Products)
Create a composite primary key: (customer_id, product_id)
Move additional attributes: For example, the quantity attribute can be moved to the Order_Items table.

The resulting data model will consist of three tables: Customers, Products, and Order_Items. The many-to-many relationship between Customers and Products is now represented by two one-to-many relationships involving the Order_Items table.

Visualizing the Data Model with a Mermaid Diagram

Here is a suggested Mermaid diagram to help visualize the new data model:

erDiagram
  CUSTOMERS ||--|{ ORDER_ITEMS : "hasMany"
  PRODUCTS ||--|{ ORDER_ITEMS : "hasMany"
  CUSTOMERS {
    int customer_id
    string customer_name
    string email
  }
  PRODUCTS {
    int product_id
    string product_name
    float price
  }
  ORDER_ITEMS {
    int customer_id
    int product_id
    int quantity
  }

Converting many-to-many relationships into one-to-many relationships is a vital step in the data modeling process. By introducing an association table and following the outlined steps, you can ensure data integrity and optimize query performance in your database. Additionally

Entities in a many-to-many relationship must be linked in a special way, that is through a third entity, called a composite entity^[1] also known as an associative entity. A composite entity has only one function: to provide an indirect link between two entities in a M:N relationship.

In the language of tables, a composite entity is termed a linking table. A composite entity has no key attribute of its own; rather, it receives the key attributes from each of the two entities it links, and combines them to form a composite key attribute^[2]. In the language of tables, a composite key attribute is termed a composite primary key^[3].
The following graphic illustrates a composite entity that now indirectly links the STUDENT and CLASS entities:

Create a composite entity called STUDENT CLASSES from a STUDENT entity and CLASS entity

The M:N relationship between STUDENT and CLASS has been dissolved into two one-to-many relations:

The 1:N relationship between STUDENT and STUDENT CLASSES reads this way: for one instance of STUDENT, there exists zero, one, or many instances of STUDENT CLASSES; but for one instance of STUDENT CLASSES, there exists zero or one instance of STUDENT.
The 1:N relationship between CLASS and STUDENT CLASSES reads this way: For one instance of CLASS, there exists zero, one, or many instances of STUDENT CLASSES; but for one instance of STUDENT CLASSES, there exists zero or one instance of CLASS.

Sometimes, but by no means always, the composite entity will “swipe” attributes from one or both of the entities it links, because those attributes would be more logically placed in the composite entity. In the case of STUDENT CLASSES, however, none of the non-key attributes from STUDENT or CLASS should be removed to the composite entity. The designer makes this decision on a case-by-case basis. The next lesson describes types of participation in relationships.

Resolving many To Many Relationships - Exercise

Before moving on to the next lesson, click the Exercise link below to check your ability to resolve many-to-many relationships.
Resolving many To Many Relationships - Exercise

[1]composite entity: In relational database design, a composite entity is used to provide an indirect link between two entities in a M:N (many-to-many) relationship.Also called a linking table.

[2]composite key attribute: A key attribute belonging to a composite entity, comprising the key attributes from each of the two entities linked by the composite entity.Also called a composite primary key.

[3]composite primary key: Another name for a composite key attribute.

Ad Database Design