Why store the same customer in every single row?
THE PROBLEM
order_id | customer | phone | product
101 | John | 555-0101 | Laptop
102 | John | 555-0101 | Mouse
103 | John | 555-0101 | Keyboard
John's details repeat on every order. Now he changes his phone number. You
update one row and miss the others — and your database holds two different
answers to the same question, with no way to tell you which is right.
That's an update anomaly, and it's the real cost of duplication. Wasted
storage is the least of it.
THE FIX — SPLIT INTO RELATED TABLES
customers
customer_id | name | phone
1 | John | 555-0101
orders
order_id | customer_id | product
101 | 1 | Laptop
102 | 1 | Mouse
103 | 1 | Keyboard
The phone number now lives in exactly one place. Orders reference John
through `customer_id`, a foreign key. Change the number once and every
order sees it immediately, because there's nothing else to update.
One customer → many orders.
THE NORMAL FORMS
• 1NF — atomic columns, no repeating groups, no comma-separated lists
• 2NF — no partial dependencies on part of a composite key
• 3NF — no transitive dependencies (non-key columns depending on other
non-key columns)
Start at 1NF. Most schemas that reach 3NF are in good shape. Going further
(BCNF, 4NF) is usually a theoretical exercise rather than a practical need.
THE TRADE-OFF
Normalized data means more joins. For most applications that's the right
default — correctness first, and a well-indexed join is cheap. But
deliberate denormalization is a legitimate choice for read-heavy workloads,
reporting tables and caches. The key word is *deliberate*: denormalizing on
purpose, with a plan for keeping copies in sync, is engineering. Duplicating
data by accident is the thing this video is about.
—
#Astmation — #software #systems, #automation, and #AI-powered #applications .
Smart Technology. Smarter Solutions.
https://astmation.com/
#Shorts #Database #SQL #Normalization #DatabaseDesign #PostgreSQL #BackendDevelopment #DataModeling #SoftwareEngineering #LearnToCode
Watch on YouTube