What is database denormalization?

Denormalization is the intentional addition of redundancy to a normalized database.

Main goals of denormalization:

• Improving performance: Reducing the number of table joins, which speeds up read query execution.
• Simplifying queries: Fewer tables to retrieve data from.

Disadvantages of denormalization:

• Data redundancy: The same data may be stored in different places, taking up more space.
• Update complexity: Changing data requires updates in multiple places, increasing the risk of inconsistency.
• Integrity violation: Lack of centralized data storage complicates maintaining data integrity.

Typical scenarios for use:

• Reporting: When summarized data is needed, combining information from different tables.
• Frequently read data: For data that is queried very often and rarely changed.
• Hierarchical data: Flat representation of hierarchy.

Denormalization techniques:

• Column duplication: Copying columns from one table to another.
• Pre-aggregation: Storing pre-calculated aggregated values.
• Creating auxiliary tables: Tables that store data obtained from joins or calculations.
• Storing history: Keeping historical data in the same table as current data, without time normalization.

Denormalization is applied when read performance is critical, even at the cost of complicating write operations and increasing redundancy risk. It is important to carefully analyze application needs and trade-offs before applying denormalization.