Table of contents
1.
Introduction
2.
Denormalization
2.1.
Advantages of denormalization
2.2.
Disadvantages of denormalization
3.
Frequently Asked Questions
4.
Key Takeaways
Last Updated: Mar 27, 2024

Denormalization in Databases

Author Gaurish Anand
1 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

Before beginning, let's recap what does normalization means. 

Normalization is used to remove the redundancy and inconsistent dependence in the database. To normalize a dataset, we break down this large dataset into different smaller datasets depending upon the relationships among tables.

You can go through this article to learn more about normalization.

Denormalization

  1. We know to normalize a dataset, we divide it into smaller datasets. But if we want to fetch data, it will be from multiple tables, and then we will have to perform some join operations on those tables. Since join operations will increase the execution time of our queries, to avoid this drawback of a join operation, we use denormalization.

    A denormalized Database should never be mistaken with a database that is not normalized. Instead, denormalization is a technique performed after normalization of the database used to increase our queries' performance.
     
  2. Example: 


Suppose we have two tables after normalization where Branch_id is the foreign key in the student's table: 

Student’s Table: 
 

Branch Table

Now let's suppose we have a query to find the student's name along with their branch. We will have to perform a join operation on these two tables to fetch this, which is very time-consuming. 

So instead of this, if we can add an extra column Branch_name too, in the student's table, we can directly fetch that student's name along with the branch without a join operation. But again, this will lead to an increase in time for insert and update operations. So denormalization should be performed only depending on the needs of our operations.

Advantages of denormalization

  • Since we will do fewer joins to fetch data, Fetching of data will be faster.
  • While querying, we need to look at fewer tables; querying can be simpler to code.

Disadvantages of denormalization

  • Insert and update operations in the database will become more expensive.
  • Data may become inconsistent as data integrity is not maintained in denormalization.
  • Due to data redundancy, we will need more storage space.

Frequently Asked Questions

  1. What are the 4 types of database normalization?
    The 4 types of Normalization in DBMS are 1nF, 2nF, 3nF, BCNF.
     
  2. Why is normalization used in databases?
    Normalization is used in databases to reduce redundancy and improve overall data integrity.
     
  3. Why is denormalization used in databases?
    Denormalization is used to increase the performance of select queries where we may be performing multiple join operations.
     
  4. Are there Data Structures and Algorithms problems in Coding Ninjas Studio?
    Yes, Coding Ninjas Studio is a platform that provides both practice coding questions and commonly asked interview questions. The more we'll practice, the better our chances are of getting into a dream company of ours.

Key Takeaways

In this article, we learned about denormalization in databases.

Since SQL-related questions are frequently asked in interviews, we recommend you practice Top 100 SQL problems on Coding Ninjas Studio. 

Are you planning to ace the interviews of reputed product-based companies like Amazon, Google, Microsoft, and more? Attempt our Online Mock Test Series on Coding Ninjas Studio now!

Live masterclass