Denormalization

Denormalization is a data modeling technique that performs duplication, integration, or separation on normalized entities, attributes, and relationships to improve system performance and simplify development and operations.

Denormalization can be defined as a technique to improve performance by duplicating data. In a broader sense, denormalization refers to the entire process of performing duplication, integration, or separation on a normalized data model to enhance performance.

The reason for applying denormalization by duplicating data, despite the risk of compromising data integrity, is to improve performance when querying data. This is done when a large amount of disk I/O causes performance degradation, or when the path is too long, leading to anticipated performance degradation due to joins, or when performance is expected to degrade when calculating and reading columns.

Table Denormalization #

• Table Merging
  • Merge 1:1 relationship tables
  • Merge 1:M relationship tables
• Table Splitting
  • Vertical splitting
  • Horizontal splitting
• Table Addition
  • Add redundant tables
  • Add statistical tables
  • Add historical tables
  • Add partial tables

Column Denormalization Techniques #

• Add redundant columns
• Add derived columns
• Add historical table columns
• Add columns based on PK
• Add columns for application system malfunction

Denormalization Procedure #

1. Investigate denormalization targets
   • Investigate frequency of range processing
   • Investigate large-scale range processing
   • Investigate statistical processes
   • Number of table joins
2. Review alternative approaches
   • View tables
   • Apply clustering
   • Adjust indexes
   • Application applications
3. Apply denormalization
   • Table denormalization
   • Attribute denormalization
   • Relationship denormalization

Alternative Approaches for Denormalization Targets #

If querying data becomes technically difficult due to an excessive number of joins, using views can resolve this.

When performance degrades due to large-scale data processing or partial processing, performance can be improved by applying clustering or adjusting indexes.

Large amounts of data can be separated into partial tables based on the nature of their Primary Key. That is, partitioning techniques can be applied to prevent performance degradation.

Performance can be improved by changing the way logic is implemented in the application.