Normalization Vs De-normalization
Normalization | 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.
|
