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.

Comments and Discussions!

Copyright © 2023 www.includehelp.com. All rights reserved.