Summary of "Learn Database Denormalization"

Core concepts

Normalization (especially Third Normal Form, 3NF): prevents contradictory or inconsistent data by ensuring non-key attributes depend only on the primary key.
Denormalization: the deliberate introduction of redundancy (duplicate or derived attributes) that violates normalization rules. It is used for practical reasons such as availability, flexibility, or performance.

External data sources If you ingest data from another system that is itself not normalized, you may be unable to reconstruct normalized structures because the source does not provide the necessary pieces to populate them. Example: a game transaction table that stores Unit_Price on each transaction rather than using a separate Item_Type_Daily_Prices table.
Future-proofing / flexibility Keeping denormalized values (for example, recording unit price on each transaction) can avoid brittle designs when business rules change (volume discounts, premium pricing, per-user pricing). Normalizing too early can force expensive redesigns if dependencies disappear.

Two-layer model:

Logic layer: tables, keys, queries, and query results (what users see).
Processing layer: physical storage, files, indexes, sort order, memory/disk layout, and query execution.

Principles:

Solve performance problems primarily in the processing layer (indexes, partitioning/sharding, optimizer statistics, materialized/indexed views, vendor-specific features) so logical integrity is not compromised.
Joins are often blamed for slow queries, but the real need is for the processing layer to present data in a pre-joined or physically co-located way so joins execute efficiently.
If the database product cannot provide a processing-layer solution (materialized/indexed view, pre-joined storage) and all other tuning is exhausted, denormalizing by adding redundant columns to avoid joins may be the remaining option.

Trade-offs when denormalizing:

Read queries can become much faster (fewer joins).
Update/insert operations usually become slower and more complex because redundant values must be maintained (higher write cost, potential for stale or inconsistent data).
Increased risk of logical inconsistency; you need processes to maintain correctness (triggers, application logic, batch updates, reconciliation).

Game transactions Unit_Price depends on (Purchase_Date, Item_Type) — storing Unit_Price in the transaction table violates 3NF. Normalized design: Item_Type_Daily_Prices table. However, downstream constraints or future pricing rules can make denormalized transaction records preferable.
Task / Subtask example Subtask needs Task_Status_Code via a join to Task. Options:
1. Keep normalized and tune the processing layer (indexes, storage) so the join is fast.
2. Use a materialized/indexed view or another DB feature to store pre-joined data.
3. If impossible, denormalize by copying Task_Status_Code into Subtask — this yields faster reads but slower updates and added consistency maintenance. - Aggregates vs normalization Adding a Number_Of_Subtasks column on Task does not violate normalization if it still depends on the key, but it introduces risk of inconsistency between Task and Subtask data.
OLAP / Data mart scenario Read-oriented systems commonly use denormalized fact and dimension tables (e.g., Dim_Branch repeating Branch_Region_Name for Branch_Country_Name). Benefits: intuitive querying and efficient reads due to physically co-located attributes. Mitigations: build dimensions via ETL from normalized sources or implement as views; load routines must be robust and tested to avoid inconsistencies.

Prefer fixing performance in the processing layer:
- Use indexes, partitioning/sharding, optimizer statistics.
- Consider materialized or indexed views, columnstore indexes, or other DB-specific features.
Only denormalize when:
1. You cannot change the processing-layer behavior (no feature available), and
2. You’ve exhausted other optimization options, and
3. The read-performance benefit outweighs the update-cost and data-consistency risk.
When you denormalize:
- Accept and plan for slower writes and extra maintenance.
- Use strong processes to keep redundant data correct (transactions, triggers, batch reconciliation, or robust ETL).
- Prefer materialized/indexed views where supported instead of manual redundancy.
For read-only/reporting systems (data marts), deliberate denormalization is common and often appropriate; use ETL from normalized sources and test loads carefully.

Item_Purchase_Transaction table and Item_Type_Daily_Prices table (game example)
Task and Subtask tables (join vs denormalization tradeoff)
Dim_Branch in a data mart (dimension table example)
Processing-layer controls cited: indexes, partitioning/sharding, optimizer statistics, and materialized/indexed views