Denormalization in Database | DBMS

DBMS | Denormalization: In this tutorial, we are going to learn about the denormalization in Database Management System.
Submitted by Anushree Goswami, on August 07, 2019

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, the 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 require 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.

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.

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.

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.

Related Tutorials


Comments and Discussions!

Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing
» Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates

© some rights reserved.