What is Denormalization in DBMS and How Does It Work?

DBMS | Denormalization: In this tutorial, we will learn about DBMS, how it works, advantages and disadvantages of using denormalization in the Database Management System. By Anushree Goswami Last updated : May 31, 2023

What is Denormalization in DBMS?

Denormalization is a performance enhancement technique through database optimization by adding redundant data in a normalized database. The requirement is to enhance the performance of a database by adding more redundant data and not reducing it by normalization because of slow processing in data retrieval, as queries written by developers take more time to address different types of tables which contain normalized data.

The process to be followed, first thing a developer needs to do is normalize the data through normalization by reducing the redundant data and by increasing its data integrity and updating. The purpose of normalization is to structure, each piece of data in different types of tables. This process slows down the queries performances, as each query requires to address each piece of data in different tables. The normalization of data works better in case of fewer amounts of data, as the queries have to address fewer numbers of tables with specific pieces of data.

Denormalization Example

A set of structured data is given in which a tables of,

user_details
client_details
customer_details
task
meeting
production
supply
order
outcome
product_proposed
product_sold

are given. Then these kinds of small amounts of data which also include each piece of data in each table as per requirement, example,

id
first_name
last_name
password
user_account_id
outcome_data
start_time
end_time
Client_name
Client_id
Client_address
Phone
Email
Contact
Product_id
Task_id
Meeting_outcome
User_assigned
Insert_user
Update_user
Manager_account
Supply_order_id
Units
Price_per_unit
Order_details
Delievery_details
Outcome_text
Completion_success

are given for the retrieval of data.

The requirement of Denormalization of database comes, when there is a huge amount of normalized data, which because of the amount, more tables, less redundancy, multiple joins and too many queries to address all the different tables which contains a different pieces of data makes the speed of performance very less, takes time to address each specific data.

Advantages of Using Denormalization

The advantages of opting for Denormalization are:

The data retrieval becomes easy and faster in comparison to normalization.
Queries to address all the data become simple as there is less number of tables.
No requirement of Multiple joins.
Enhancement in query performance.
No Requirement of a real-time generation of common values in the computation.

Disadvantages of Using Denormalization

The disadvantages of opting for Denormalization are:

Data redundancy requires more storage for addition of redundant data.
Updation and insertion becomes difficult.
Data management and adjustment becomes trickier.
Requirement of detailed documentation.

Redundancy in DBMS

Tuple Relational Calculus (TRC) in DBMS