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.

 

 
  clip_image001[6]
 

 

 

 

 

 

 

 

 

 


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”

1 comment: