Normalization vs Denormalization

Trade-offs.

Normalisation strengths

Single source of truth for each fact. Update one place; consistency across reads.

Storage efficiency. No duplicated data. Smaller database; cheaper storage.

Transactional consistency. Schema enforces business rules; database constraints catch errors.

Denormalisation strengths

Read performance. Pre-joined data; queries don't need joins; reads are fast.

Predictable query plans. Less optimiser variance; simpler queries.

Best for analytics workloads where read patterns dominate writes.

When to choose which

Default to normalised. Fix on transaction-heavy systems with frequent updates.

Denormalise specific paths for read performance. Materialised views, summary tables.

Pure denormalised for analytics warehouses. Star and snowflake schemas optimise reads at the cost of writes.

Hybrid patterns

Normalised primary, denormalised replica. Writes maintain consistency; reads hit denormalised tables.

CQRS: separate write and read models entirely. Most extreme version of the hybrid.

ETL maintains the denormalised view from the normalised source. Eventual consistency at refresh interval.