Click here to Skip to main content
13,667,391 members
Click here to Skip to main content
Add your own
alternative version

Tagged as



2 bookmarked
Posted 25 Oct 2013
Licenced CPOL

When do we need junction table for a one to many relationship?

, 25 Oct 2013
Rate this:
Please Sign up or sign in to vote.
When do we need junction table for a one to many relationship?

For one to many relationships we don’t need to use junction table in relational database. As logic says, we can easily make one to many relationship between two tables via primary key and foreign key. Then why junction table for one to many relationship? Obviously there have some reason why we will use junction table to keep us as well as database safe for near future.

I will explain about it with an example. Mr. Scott is an database developer. His boss gives him a document and tells him to design ER diagram of the system. Mr. Scott starts to design the ER diagram. Somewhere in documentation he found the below lines:

“Till now each department is supervised by one employee. If management approve then may be near future more than one employee can supervised more than one department.”

Mr. Scott thinks that, as per current situation the relationship between Department and Employee will be one to many relationship. But near future it could be many to many relationship. Mr. Scott takes the current situation and designs the ER diagram of Department and Employee table by following ways.


Mr. Scott prepares the ER diagram and sends this to his boss. The system is developed by following the ER diagram. System is working smoothly. But problem arise after one year later, when a new decision is taken by customer and that is –

“From now more than one employee can run more than one department.”

Mr. Scott now changes the ER diagram and creates a junction table between Department and Employee. When database & frontend team got the change details, they informed that they have to change the system overall. And it takes a long time to change the system.

But question is, did Mr. Scott do the right things initially?

From my point of view it was wrong. He should design the system initially by following ways


By creating a junction table called DepartmentEmployee, he could easily develop the ER diagram as per requirment. Keep in mind that at DepartmentEmployee table, DepartmentId should be primary key, then more then one department cannot be insert into Department table.

After that when new decision / changes comes after one year, then he just need to change few things at DepartmentEmployee table. First remove the primary key from DepartmentEmployee table and create a new composite key with DepartmentId and EmployeeId. New changes will be like below


Now think with this small changes Mr. Scott’s company can saves the time and cost of the project from any aspect.

The post When we need junction table for one to many relationship? appeared first on crea8ivecode.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Sadeque Sharif
Founder Codexplorer Technologies
Bangladesh Bangladesh
I am:
Founder & Technical Head at Codexplorer Technologies.
IT Consultant at Meridian Group.

I was:
Manager (IT) at Meridian Group.
Assistant Manager (Software Division) at KDS Garment Industries Limited.
Assistant Manager (Software Division) at E-Vision Software Limited.

My blog:

My preferred work area:

My email:

Follow me:
twitter | facebook | linkedin

You may also be interested in...

Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01-2016 | 2.8.180820.1 | Last Updated 25 Oct 2013
Article Copyright 2013 by Sadeque Sharif
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid