Bring a flat orders table to third normal form — find 1NF, 2NF and 3NF violations and split the data into tables with keys — and tell duplication apart from a historical value.
Every fact in one place
Normalization to 3NF
The BA course covered entities, relationships and keys (PK, FK). Now: storing each fact once. Otherwise, anomalies: a courier's new phone means editing hundreds of rows, missing some; a new courier with no orders has nowhere to keep a phone; delete their last order and the phone is gone.
A flat export, one row per order: 9002 | Anna | +7 000 000-00-01 | milk ×2, bread ×1 | 12 | +7 000 000-00-12. Step by step:
• 1NF: one value per cell, no lists or repeating groups. Items go to order_items(order_id, product_id, quantity, price), a row per product.
• 2NF (composite key): a non-key field depends on the whole key, not part of it. In order_items with the key (order_id, product_id), the product name depends only on product_id: it moves to products.
• 3NF: no non-key field depends on another non-key field. The courier's phone depends on courier_id, not the order: it moves to couriers; the customer's name and phone, to customers.
In short: the key, the whole key, and nothing but the key.
Not every repeat is a duplicate: order_items.price is the price at purchase, products.price the current one. Drop the first and old receipts “get pricier” with the product. And the delivery address belongs to the order, not the customer: orders go to different addresses.
Denormalization is a deliberate violation for faster reads (e.g. reports); record the reason.
Violation → where to move it
| Symptom | Form | What to do |
|---|---|---|
| A list in a cell: “milk ×2, bread ×1” | 1NF | Items go to order_items, one row per product |
| product_name in order_items with the key (order_id, product_id) | 2NF | The name goes to products |
| courier_phone in orders next to courier_id | 3NF | The phone goes to couriers |
| price in both order_items and products | Not a violation | Keep it: price at purchase ≠ current price |
Lesson notes
Normalization to 3NF
The BA course covered entities, relationships and keys (PK, FK). Now: storing each fact once. Otherwise, anomalies: a courier's new phone means editing hundreds of rows, missing some; a new courier with no orders has nowhere to keep a phone; delete their last order and the phone is gone.
A flat export, one row per order: 9002 | Anna | +7 000 000-00-01 | milk ×2, bread ×1 | 12 | +7 000 000-00-12. Step by step:
• 1NF: one value per cell, no lists or repeating groups. Items go to order_items(order_id, product_id, quantity, price), a row per product.
• 2NF (composite key): a non-key field depends on the whole key, not part of it. In order_items with the key (order_id, product_id), the product name depends only on product_id: it moves to products.
• 3NF: no non-key field depends on another non-key field. The courier's phone depends on courier_id, not the order: it moves to couriers; the customer's name and phone, to customers.
In short: the key, the whole key, and nothing but the key.
Not every repeat is a duplicate: order_items.price is the price at purchase, products.price the current one. Drop the first and old receipts “get pricier” with the product. And the delivery address belongs to the order, not the customer: orders go to different addresses.
Denormalization is a deliberate violation for faster reads (e.g. reports); record the reason.