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.