Skip to content

MS SQL

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 info@neogroups.com 987345234
2 Singh Bros Al ferrules info@singhbros 127345222
3 Axtech Systems T. Oil contact@axtech.net 457345223
4 Amtronicas Ltd. Cu sheet, Screw, MS sheet, Al sheet, Al ferrules, Air vent plug amtronicas@gmail.com 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.

Solution

Design Supplier table

SupplierId SupplierName Email ContactNo
1 NeoQuirks info@neogroups.com 987345234
2 Singh Bros info@singhbros 127345222
3 Axtech Systems contact@axtech.net 457345223
4 Amtronicas Ltd. amtronicas@gmail.com 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

SupplierProductLink

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 codingthis.com 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)