Part 1 of this series covered joins. Part 2 covered indexing. Part 3 covered stored procedures, views, and transactions. This closing part covers normalization - and rather than abstract rule definitions, it takes one genuinely messy sample table and fixes it one normal form at a time, so each rule is visible in an actual before and after rather than just described.
The Messy Starting Table
| OrderId | CustomerName | CustomerPhones | ProductNames | CityId | CityName |
|---|---|---|---|---|---|
| 1 | Alex Kim | 555-1234, 555-5678 | Mouse, Keyboard | 10 | Chicago |
| 2 | Priya Shah | 555-9999 | Monitor | 20 | Denver |
| 3 | Alex Kim | 555-1234, 555-5678 | Webcam | 10 | Chicago |
Three real problems are already visible here: CustomerPhones holds multiple values crammed into one field, ProductNames holds multiple values crammed into one field, and CityName is fully determined by CityId, repeating identically on every row that shares the same CityId.
First Normal Form: Every Column Holds Exactly One Value
The rule for First Normal Form is that every column must hold exactly one atomic value per row - no repeating groups, no multi-valued fields.
Think of a form with a single Phone Number field, not one field where someone crammed three phone numbers separated by commas. If a person can have multiple phone numbers, that's a sign a separate table is needed, not a wider single field.
Orders (1NF)
| OrderId | CustomerName | CityId | CityName |
|---|---|---|---|
| 1 | Alex Kim | 10 | Chicago |
| 2 | Priya Shah | 20 | Denver |
| 3 | Alex Kim | 10 | Chicago |
OrderProducts (1NF)
| OrderId | ProductName |
|---|---|
| 1 | Mouse |
| 1 | Keyboard |
| 2 | Monitor |
| 3 | Webcam |
CustomerPhones (1NF)
| CustomerName | Phone |
|---|---|
| Alex Kim | 555-1234 |
| Alex Kim | 555-5678 |
| Priya Shah | 555-9999 |
Every column in every table now holds exactly one value per row - this is 1NF.
Second Normal Form: No Partial Dependency on Part of a Composite Key
Second Normal Form only becomes relevant when a table has a composite primary key - two or more columns together forming the key. The rule states every other column must depend on the entire key, not just part of it.
Think of the OrderProducts table having a composite key of OrderId plus ProductName, with someone adding a ProductCategory column to that same table. ProductCategory depends only on ProductName - it has nothing to do with OrderId at all.
OrderProducts — 2NF violation (key: OrderId + ProductName)
| OrderId | ProductName | ProductCategory |
|---|---|---|
| 1 | Mouse | Peripherals |
| 1 | Keyboard | Peripherals |
| 2 | Monitor | Displays |
| 3 | Webcam | Peripherals |
"Peripherals" is duplicated three times. If the category name ever changes, every single row with that product needs to be updated individually.
Fix — split into two tables:
OrderProducts (2NF)
| OrderId | ProductName |
|---|---|
| 1 | Mouse |
| 1 | Keyboard |
| 2 | Monitor |
| 3 | Webcam |
Products (2NF)
| ProductName | ProductCategory |
|---|---|
| Mouse | Peripherals |
| Keyboard | Peripherals |
| Monitor | Displays |
| Webcam | Peripherals |
Now ProductCategory is stored exactly once per product, not once per order line.
Third Normal Form: No Transitive Dependency Between Non-Key Columns
The rule for Third Normal Form is that every non-key column must depend only on the primary key, not on another non-key column in the same table.
CityId depends directly on OrderId, and CityName depends directly on CityId - so CityName depends on OrderId only indirectly, through CityId. This is a transitive dependency.
Orders — 3NF violation
| OrderId | CustomerName | CityId | CityName |
|---|---|---|---|
| 1 | Alex Kim | 10 | Chicago |
| 2 | Priya Shah | 20 | Denver |
| 3 | Alex Kim | 10 | Chicago |
"Chicago" is duplicated every time CityId equals 10 appears. If Chicago were ever renamed in the system, every duplicate needs updating.
Fix — split City into its own table:
Orders (3NF)
| OrderId | CustomerName | CityId |
|---|---|---|
| 1 | Alex Kim | 10 |
| 2 | Priya Shah | 20 |
| 3 | Alex Kim | 10 |
Cities (3NF)
| CityId | CityName |
|---|---|
| 10 | Chicago |
| 20 | Denver |
CityName now exists exactly once per city, referenced by Id rather than duplicated by name.
The Fully Normalized Result
| Table | Columns |
|---|---|
| Orders | OrderId, CustomerName, CityId |
| OrderProducts | OrderId, ProductName |
| Products | ProductName, ProductCategory |
| CustomerPhones | CustomerName, Phone |
| Cities | CityId, CityName |
Five focused tables instead of one wide, repetitive one. Every piece of information now exists in exactly one place - updating a city name, a product category, or a phone number means updating exactly one row, not hunting down every duplicate.
Denormalization
Denormalization means deliberately duplicating data across tables, or keeping wider tables than strict normalization would produce, specifically to avoid the cost of joining multiple tables together on a read path that needs to be fast.
-- A fully normalized "get order summary" query
-- needs several joins to reconstruct one readable row
SELECT o.OrderId, o.CustomerName, c.CityName,
p.ProductName, pr.ProductCategory
FROM Orders o
JOIN Cities c ON o.CityId = c.CityId
JOIN OrderProducts p ON o.OrderId = p.OrderId
JOIN Products pr ON p.ProductName = pr.ProductName;
Normalized, this costs three joins per read but keeps exactly one row to update, always in sync. Denormalized, this costs one read with no joins, but the duplicate copy needs explicit sync, or it goes stale.
A deliberately denormalized version might store CityName directly on the Orders table again:
-- ORDERS (deliberately denormalized)
-- OrderId | CustomerName | CityId | CityName
-- 1 | Alex Kim | 10 | Chicago
-- One less join, faster read. The tradeoff: if
-- Chicago is ever renamed, this duplicated copy
-- needs an explicit update too, or it silently
-- goes stale.
This is a legitimate choice, not a mistake. Normalization optimizes for data integrity and avoiding update anomalies. Denormalization optimizes for read speed, at the acknowledged cost of needing to keep duplicated data in sync deliberately - whether through application code, database triggers, or a scheduled process. The real mistake is denormalizing by accident, without realizing the sync responsibility now exists - not the deliberate choice to denormalize a specific, measured hot path.
Key Lessons
1NF requires every column to hold exactly one atomic value - no repeating groups or multi-valued fields crammed into one column.
2NF only applies to tables with a composite key, and requires every other column to depend on the whole key, not just part of it.
3NF requires every non-key column to depend only on the primary key, not transitively through another non-key column.
Each normal form removes one specific kind of data duplication, which in turn removes one specific kind of update anomaly.
Denormalization is a deliberate, legitimate tradeoff - duplicating data on purpose to avoid join cost on a measured, high-traffic read path, accepting the responsibility to keep that duplicate in sync.
The real mistake is accidental denormalization - duplicating data without realizing it now needs active synchronization, rather than choosing to denormalize deliberately for a specific, identified reason.
The Series, Complete
This closes out the four-part database interview series. Part 1 covered joins - Inner, Left, Right, Full Outer, Cross, Self. Part 2 covered indexing - clustered, non-clustered, composite, and covering indexes. Part 3 covered stored procedures, views, ACID, isolation levels, and deadlocks. Part 4 covered normalization - 1NF through 3NF, and the honest case for denormalizing on purpose.
Summary
Normalization is a step-by-step process for removing duplicated data and the update anomalies that duplication causes. First Normal Form removes multi-valued fields. Second Normal Form removes dependencies on only part of a composite key. Third Normal Form removes dependencies that flow indirectly through another non-key column. Each step trades some storage efficiency and update simplicity for the cost of needing more joins to reconstruct a full picture - which is exactly why denormalization exists as its deliberate, honest counterpart, trading that join cost back for read speed on the specific paths that genuinely need it. Knowing both directions - when to normalize and when to deliberately step back from it - is what separates textbook schema design from schema design that holds up in a real, high-traffic system.
Originally published at TechStack Blog: https://www.techstackblog.com/post.html?slug=database-normalization-schema-design
Part 1 of this series (Joins): https://www.techstackblog.com/post.html?slug=database-joins-explained
Part 2 of this series (Indexing): https://www.techstackblog.com/post.html?slug=database-indexing-explained
Part 3 of this series (Transactions): https://www.techstackblog.com/post.html?slug=database-stored-procedures-transactions
More from TechStack Blog: Database: https://www.techstackblog.com/category.html?cat=database
CS Fundamentals: https://www.techstackblog.com/category.html?cat=cs-fundamentals
Top comments (0)