Denormalization in Database:
Denormalization is a process of Transforming normalized relations into unnormalized physical record specifications.
A good relational DBMS excels at managing normalized relations; that is, relations void of any unnecessary redundancies that might cause data anomalies. Although the creation of normalized relations is an important database design goal, it is only one of many such goals. Good database design also considers processing (or reporting) requirements and processing speed. The problem with normalization is that as tables are decomposed to conform to normalization requirements, the number of database tables expands.
Therefore, in order to generate information, data must be put together from various tables. Joining a large number of tables takes additional input/output (I/O) operations and processing logic, thereby reducing system speed. Most relational database systems are able to handle joins very efficiently.
You May Also Like:
Normalization and its advantages
1st Normal Form
2nd Normal Form
However, rare and occasional circumstances may allow some degree of denormalization so processing speed can be increased. The conflicts between design efficiency, information requirements, and performance are often resolved through compromises that may include denormalization.
In this case, and assuming there is enough storage space, the designer’s choices could be narrowed down to:
Store the data in a permanent denormalized table. This is not the recommended solution, because the denormalized table is subject to data anomalies (insert, update, and delete). This solution is viable only if performance is an issue.
Create a temporary denormalized table from the permanent normalized table(s). Because the denormalized table exists only as long as it takes to generate the report, it disappears after the report is produced. Therefore, there are no data anomaly problems. This solution is practical only if performance is not an issue and there are no other viable processing options. For example consider the following figure.
To retrieve the data, we have to access the data from two relations by using joins. To avoid the joins we can denormalize the relations by merging the both relations.
Disadvantages of Denormalization:
• Wasted storage space
• Data integrity/consistency threats
You May Also Like:
