Normalization & Schema Design

Normalize what you query, constrain, and join; put flexible, sparse, rarely-filtered attributes in JSON – and use the JSON functions to build and unnest.

Denormalization trades extra writes and controlled redundancy for fast, cacheable reads – normalize for writes, denormalize for reads.

Exactly one primary key per table, but it may be composite (several columns together); extra uniqueness needs unique constraints, not extra primary keys.

2NF removes partial dependencies on part of a composite key; 3NF removes transitive dependencies on other non-key columns.

Normalization splits data so each fact lives once (less redundancy, safe writes); denormalization deliberately duplicates data for faster reads, accepting write cost and consistency risk.

A foreign key makes a child row point only at an existing parent; `ON DELETE`/`ON UPDATE` decide whether removing or changing the parent is blocked, cascaded to children, or nulls the link.

One primary key per table, never null, the row's identity and foreign-key target; many unique keys allowed, one null typically permitted, enforcing no-duplicates on other columns.