What is Denormalization in DBMS?
When we do the normalization of tables then we split the tables into smaller tables. But if we want to get the data from more than one tables, then we have to perform some kind of join operation on those tables. In the normalization blog, we read that the join operation will lead to increased time. To overcome this disadvantage of normalization we do denormalization of tables.
Denormalization is a database optimization technique where we add redundant data in the database to get rid of the complex join operations. This is done to speed up database access speed. Denormalization is done after normalization for improving the performance of the database. The data from one table is included in another table to reduce the number of joins in the query and hence helps in speeding up the performance.
A denormalized database should never be confused by a database that has never been normalized.
Example: Suppose after normalization we have two tables first, Student table and second, Branch table. The student has the attributes as Roll_no, Student-name, Age, and Branch_id.
The branch table is related to the Student table with Branch_id as the foreign key in the Student table.
If we want the name of students along with the name of the branch name then we need to perform a join operation. The problem here is that if the table is large we need a lot of time to perform the join operations. So, we can add the data of Branch_name from Branch table to the Student table and this will help in reducing the time that would have been used in join operation and thus optimize the database.
Advantages of Denormalization
- Query execution is fast since we have to join fewer tables.
Disadvantages of Denormalization
- As data redundancy is there, update and insert operations are more expensive and take more time. Since we are not performing normalization, so this will result in redundant data.
- Data Integrity is not maintained in denormalization. As there is redundancy so data can be inconsistent.
This is all about denormalization. Hope you learned something new today.
Keep Learning :)