- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Many-to-Many Relationship Model
102 Lectures 7.5 hours
152 Lectures 16 hours
In a "product sales" database, a customer's order may contain one or more products; and a product can appear in many orders. In a "bookstore" database, a book is written by one or more authors; while an author may write zero or more books. This kind of relationship is known as many-to-many.
Let's illustrate with a "product sales" database. We begin with two tables: Products and Orders. The table products contain information about the products (such as name, description and quantityInStock) with productID as its primary key. The table orders contain customer's orders (customerID, dateOrdered, dateRequired and status). Again, we cannot store the items ordered inside the Orders table, as we do not know how many columns to reserve for the items. We also cannot store the order information in the Products table.
To support many-to-many relationship, we need to create a third table (known as a junction table), say OrderDetails (or OrderLines), where each row represents an item of a particular order. For the OrderDetails table, the primary key consists of two columns: orderID and productID, that uniquely identify each row. The columns orderID and productID in OrderDetails table are used to reference Orders and Products tables, hence, they are also the foreign keys in the OrderDetails table.
The many-to-many relationship is, in fact, implemented as two one-to-many relationships, with the introduction of the junction table.
An order has many items in OrderDetails. An OrderDetails item belongs to one particular order.
A product may appear in many OrderDetails. Each OrderDetails item specified one product.
- One-to-Many Relationship Model
- Managing Many-to-Many relationship
- Many-to-Many Relationship in DBMS
- One-to-Many or Many-to-One Relationship in DBMS
- One-to-Many Unary Relationship in DBMS
- One-to-One Relationship Model
- Upsert many documents in MongoDB
- Extended Entity-Relationship (EE-R) Model
- MySQL query to select too many rows?
- MongoDB many insertsupdates without affecting performance?
- How many diseases are spread by mosquitoes?
- How many keywords are there in C++?
- A Problem in Many Binary Search Implementations?
- How many Rounds are there in DES?
- What is an Entity relationship model in DBMS?