Open In App

Denormalization in Databases

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

Denormalization is a database optimization technique in which we add redundant data to one or more tables. This can help us avoid costly joins in a relational database. Note that denormalization does not mean ‘reversing normalization’ or ‘not to normalize’. It is an optimization technique that is applied after normalization.

Basically, The process of taking a normalized schema and making it non-normalized is called denormalization, and designers use it to tune the performance of systems to support time-critical operations.

In a traditional normalized database, we store data in separate logical tables and attempt to minimize redundant data. We may strive to have only one copy of each piece of data in a database.

For example, in a normalized database, we might have a Courses table and a Teachers table. Each entry in Courses would store the teacherID for a Course but not the teacherName. When we need to retrieve a list of all Courses with the Teacher’s name, we would do a join between these two tables. 

In some ways, this is great; if a teacher changes his or her name, we only have to update the name in one place. 
The drawback is that if tables are large, we may spend an unnecessarily long time doing joins on tables. 
Denormalization, then, strikes a different compromise. Under denormalization, we decide that we’re okay with some redundancy and some extra effort to update the database in order to get the efficiency advantages of fewer joins. 

Pros of Denormalization:

  1. Retrieving data is faster since we do fewer joins
  2. Queries to retrieve can be simpler(and therefore less likely to have bugs), 
    since we need to look at fewer tables.

Cons of Denormalization:

  1. Updates and inserts are more expensive.
  2. Denormalization can make update and insert code harder to write.
  3. Data may be inconsistent.
  4. Data redundancy necessitates more storage.

In a system that demands scalability, like that of any major tech company, we almost always use elements of both normalized and denormalized databases.

Advantages of Denormalization:

Improved Query Performance: Denormalization can improve query performance by reducing the number of joins required to retrieve data.

Reduced Complexity: By combining related data into fewer tables, denormalization can simplify the database schema and make it easier to manage.

Easier Maintenance and Updates: Denormalization can make it easier to update and maintain the database by reducing the number of tables.

Improved Read Performance: Denormalization can improve read performance by making it easier to access data.

Better Scalability: Denormalization can improve the scalability of a database system by reducing the number of tables and improving the overall performance.

Disadvantages of Denormalization:

Reduced Data Integrity: By adding redundant data, denormalization can reduce data integrity and increase the risk of inconsistencies.

Increased Complexity: While denormalization can simplify the database schema in some cases, it can also increase complexity by introducing redundant data.

Increased Storage Requirements: By adding redundant data, denormalization can increase storage requirements and increase the cost of maintaining the database.

Increased Update and Maintenance Complexity: Denormalization can increase the complexity of updating and maintaining the database by introducing redundant data.

Limited Flexibility: Denormalization can reduce the flexibility of a database system by introducing redundant data and making it harder to modify the schema.


Last Updated : 08 May, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads