Database Modelling – Many to Many Relationship

Let’s start with an example to understand what is many to many relationship and how this can be represented in database.

 

If you have shopping experience with any online shopping websites like amazon.com, you could have noticed that the way they organized their products to display.

The online store may sell same type of products of different manufacturer and different type of products from same manufacturer. Example Mobile can be manufactured by Nokia, Apple and Samsung. Similarly Apple manufactures Mobile and Computer. I have represented the same scenario in the below diagram.

 

 

 

 

 

 

 

 

 

 

 

I hope, by now we know what is many to many relationships. Having this knowledge let’s design database table for this use case.

 

Department
department_id		department_name
1		Mobile Phone
2		Computer

 

Manufacturer
manufacturer _id	manufacturer _name
1	Apple
2	Dell
3	Nokia

 

The “Department” table can be used to store all the department of the online store and “Manufacturer” table can be used to store all the manufacturer information. So far everything looks good, but the store wants to display the menu in a way where user can browser product by manufacturers. In our example, computer by manufacturers.

 

The below given menu is what the online store need.

 

Browse By Department			Browse By Department
Mobile Phone ›	Apple		Mobile Phone
Computer	Samsung		Computer ›	Apple
				Dell
Browse By Manufacturer			Browse By Manufacturer
Apple	Mobile Phone		Apple
Dell	Computer		Dell	Computer
Samsung			Samsung

 

To achieve this, we create a new table to hold the department and manufacturer relation, this table is called junction table.   

 

Department_Manufacturer
department_id	manufacturer_id
1	1
1	3
2	1
2	2

 

Using this table, we will be able to answer the below questions

1. Which departments belong to manufacturer “Apple”
2. List of manufacturers who belong to department “Mobile”