Skip to content


Database Modeling with Real Requirement – Part 2

My last article was about a database modeling scenario that is frequently encountered in software development. Theoretically that scenario is called one-to-many relationship. The current article is a sequel to the last article and seeks to model a different but a little complicated scenario that is encountered in most of the projects. It may not be as common as the one-to-many relationship scenario but is definitely encountered every now and then and needs a different approach to model it.

In requirement gathering phase, have you encountered something like the following table?

The Requirement

S.No. Supplier Name Products Supplied Email Contact No
1 NeoQuirks MS sheet, Cu Sheet, Screw, Air vent plug, Al ferrules 987345234
2 Singh Bros Al ferrules info@singhbros 127345222
3 Axtech Systems T. Oil 457345223
4 Amtronicas Ltd. Cu sheet, Screw, MS sheet, Al sheet, Al ferrules, Air vent plug 337345290

The above table translates to following technical requirement:

  • One Supplier can supply one or more than one product.
  • One Product can be supplied by one or more than one supplier.

There can be other similar scenario like the one mentioned above. Check the following:

In a SaaS which offers multiple B2B services

  • One service can be offered by one or many clients
  • One client can offer one or more than one services.

Another example could be:

  • One book can be written by one or many authors
  • One author can write one or many books

How are you going to model tables for these kind of requirement? Theoretically this scenario is called many-to-many relationship.

Here we go to design tables for the first requirement. The other two example would follow the same line of action.


Design Supplier table

SupplierId SupplierName Email ContactNo
1 NeoQuirks 987345234
2 Singh Bros info@singhbros 127345222
3 Axtech Systems 457345223
4 Amtronicas Ltd. 337345290

Design Product table

ProductId ProductName Specification
1 MS sheet Dimension 5’x3’
2 Al sheet Dimension 7’x5’
3 Cu sheet Dimension 4’x1’
4 Screw 4-point 1’’
5 T. Oil A grade
6 Air vent plug
7 Al ferrules
8 Cu strip Dimension 7’’x2’’

 Junction Table


SupplierProductLinkId SupplierId ProductId
1 1 1
2 2 7
3 1 3
4 1 4
5 3 5
6 1 6
7 1 7


The third table (SupplierProductLink) which we call Junction table (or Link table or Intermediate table) might look a bit strange to a beginner. This table is required to address any kind of many-to-many relationship. As you can see this table has no visible data as such, it only stores the keys from both the tables that serves as a reference in linking the two main tables that actually contain data.

How do you fetch data from many-to-many modeled tables?

Suppose you want to fetch which products are getting supplied by supplier named NeoQuirks?
SupplierId of NeoQuirks is 1. You can use the following query:
Select p.ProductName, p.Specification from Product p
   inner join SupplierProductLink sp on sp.ProductId = p.ProductId
      inner join Supplier s on sp.SupplierId = s.SupplierId
      where s.SupplierId = 1
Tip: The above query can be written in 2 more ways. Just give it a try … or look for the solution here:
1st way
Select p.ProductName, p.Specification from Supplier s
   inner join SupplierProductLink sp on sp.SupplierId = p.SupplierId
      inner join Product p on sp.ProductId  = p.ProductId
      where s.SupplierId = 1
2nd way
Select p.ProductName, p.Specification from SupplierProductLink sp
          inner join  Supplier s on sp.SupplierId = s.SupplierId
      inner join Product p on sp.ProductId  = p.ProductId
               where s.SupplierId = 1
Hope this article helped you to practically understand the nitty-gritty of many-to-many relationship. Please share your views in the comment section.
Be Sociable, Share!
    The following two tabs change content below.

    Basant Singh

    Basant is a talented software developer who is very engaged in web & cloud platforms on a daily basis. He is a contributor to reporting on various news and troubleshooting on various platforms. Basant is very strong in SQL and a great asset to the CodingThis community.

    Latest posts by Basant Singh (see all)