Normalization

Normalization decomposes tables to eliminate redundancy and update anomalies. Each normal form adds rules about functional dependencies — which non-key attributes depend on which keys.

| Form | Rule (simplified) | |------|-------------------| | 1NF | Atomic values; no repeating groups | | 2NF | No partial dependency on part of a composite PK | | 3NF | No transitive dependency: non-key → non-key | | BCNF | Every determinant is a candidate key |

Example anomaly without normalization: storing `customer_email` on every `order_items` row — a email change requires updating many rows; partial updates leave inconsistent copies.

					-- Before: orders(customer_email, ...) duplicates user facts
-- After: orders(user_id) REFERENCES users(email)
				

Decompose by ownership: facts about a user belong in `users`; facts about a line item belong in `order_items`. Join at read time unless a measured hot path justifies duplication (see denormalization).

On interviews: given a denormalized spreadsheet schema, identify the anomaly (insert/update/delete) and propose 3NF tables.

Common pitfalls: over-normalizing into dozens of tiny tables for simple CRUD; splitting entities that always load together without measuring join cost; normalizing event/log data that is append-only and never updated.

The trade-off is balancing update safety and single source of truth against read join complexity — normalize until anomalies hurt, not until ER diagrams look academic.

Checklist:

• Name the redundancy or anomaly you're removing.
• Identify functional dependencies and ownership.
• Target 3NF/BCNF unless a measured read path says otherwise.
• Preserve candidate keys and FK relationships after split.
• Explain which queries gain joins and which gain safer updates.