Difference between Normalization and De-normalization in Database (DBMS)

Normalization Vs De-normalization



Normalization is the process of dividing the data into multiple tables, so that data redundancy and data integrities are achieved.

De-Normalization is the opposite process of normalization where the data from multiple tables are combined into one table, so that data retrieval will be faster.

It removes data redundancy i.e.; it eliminates any duplicate data from the same table and puts into a separate new table.

It creates data redundancy i.e.; duplicate data may be found in the same table.

It maintains data integrity i.e.; any addition or deletion of data from the table will not create any mismatch in the relationship of the tables.

It may not retain the data integrity.

It increases the number of tables in the database and hence the joins to get the result.

It reduces the number of tables and hence reduces the number of joins. Hence the performance of the query is faster here compared to normalized tables.

Even though it creates multiple tables, inserts, updates and deletes are more efficient in this case. If we have to insert/update/delete any data, we have to perform the transaction in that particular table. Hence there is no fear of data loss or data integrity.

In this case all the duplicate data are at single table and care should be taken to insert/delete/update all the related data in that table. Failing to do so will create data integrity issues.

Use normalized tables where more number of insert/update/delete operations are performed and joins of those tables are not expensive.

Use de-normalization where joins are expensive and frequent query is executed on the tables.

Simple SQL Tutorial

SQL Interview Questions
Next > < Prev
Scroll to Top