What is normalization and denormalization in the context of working with databases?

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. Denormalization is the process of adding redundant data to a database to enhance query performance.

Normalization:

• Principles: Eliminating repeating groups, dependencies between unrelated attributes.
• Normal forms (main):
  • 1NF (First Normal Form): Atomic values in columns, no repeating groups.
  • 2NF (Second Normal Form): In 1NF + no partial dependencies.
  • 3NF (Third Normal Form): In 2NF + no transitive dependencies.
  • Boyce-Codd Normal Form (BCNF): A stricter version of 3NF.
• Advantages:
  • Reduces data redundancy (saves space).
  • Improves data consistency.
  • Simplifies data modification (insert, update, delete).
• Disadvantages:
  • Increases the number of tables.
  • Requires joining tables to get complete information (JOINs), which can reduce query performance.

Denormalization:

• Principles: Conscious violation of normalization rules to speed up data reading.
• Typical techniques:
  • Data duplication (e.g., storing product name in orders table).
  • Joining tables.
  • Pre-calculating aggregated values.
• Advantages:
  • Increases query performance (especially SELECT).
  • Reduces the number of JOINs.
  • Can be useful for reports and OLAP systems.
• Disadvantages:
  • Increases data redundancy (takes more space).
  • Risk of data inconsistency.
  • Complicates data modification (needs to update data in multiple places).

The choice between normalization and denormalization depends on the application's specifics, priorities (data integrity or performance), and query nature. Usually, normalization is used for OLTP systems (online transactions), and denormalization for OLAP systems (analytical processing) or in performance-critical OLTP areas.