Data modelling · Medium
What is normalisation, and why are orders and order_items separate tables?
Short answer
Normalisation organises data so each fact is stored once, reducing duplication and update anomalies. Orders and order items are separate because one order contains many products: order-level facts such as date and status live once in orders, while each product line lives in order_items with a foreign key back to its order. Analytics models sometimes denormalise deliberately for speed.
Detailed answer
Keys first:
- A primary key uniquely identifies a row (
orders.order_id). - A foreign key points to a primary key in another table (
order_items.order_id → orders.order_id), enforcing that every line belongs to a real order.
Why not one wide table? If you stored the order date and status on every item row, a three-item order would repeat them three times. Changing the status means updating three rows — miss one and the data contradicts itself (an update anomaly).
The normal forms in one line each:
- 1NF – one value per cell; no lists like "Yoga Mat, Fountain Pen" in a column.
- 2NF – every non-key column depends on the whole key (product name should not sit in order_items, whose key is the line).
- 3NF – no column depends on another non-key column (store
cityon the customer, not on each order).
-- Grain check: one row per order in orders, many rows per order in order_items
SELECT 'orders' AS table_name, COUNT(*) AS row_count, COUNT(DISTINCT order_id) AS distinct_orders FROM orders
UNION ALL
SELECT 'order_items', COUNT(*), COUNT(DISTINCT order_id) FROM order_items;
Nuance for analyst roles: transactional databases are normalised for safe writes; reporting models are often denormalised (star schemas, wide tables) to make reads simple and fast. Knowing both, and when each fits, is the real answer.