Database Normalization

Normalization is the process of organizing tables to reduce redundancy and prevent inconsistent data — it's the theory behind why our practice database is split into six separate tables instead of one giant spreadsheet.

The problem with one big table

Imagine storing every order with the customer's name, city, and country repeated on every single row for that customer. This creates three problems:

• Update anomaly — if a customer moves city, you must update every order row for them, or the data becomes inconsistent.
• Insertion anomaly — you can't record a new customer until they've placed an order (if customer details only exist inside the orders table).
• Deletion anomaly — deleting a customer's only order accidentally deletes all record of that customer too.

This is exactly why our practice database keeps customers separate from orders — a customer's city is stored once, in one row of customers, and every order just references it by customer_id.

First Normal Form (1NF)

Every column holds a single, atomic value — no comma-separated lists crammed into one field.

-- Violates 1NF: multiple values in one column
-- phone_numbers: '0300-1111111, 0321-2222222'

-- 1NF-compliant: one phone number per row, in a separate table
CREATE TABLE customer_phones (
  id INT PRIMARY KEY,
  customer_id INT REFERENCES customers(id),
  phone_number VARCHAR(20)
);

Second Normal Form (2NF)

Must already be in 1NF, and every non-key column must depend on the entire primary key — relevant when a table has a composite (multi-column) primary key. If order_items had a composite key of (order_id, product_id), a column like product_name stored directly on order_items would violate 2NF, because it depends only on product_id, not the whole key — which is exactly why product_name lives in products, not in order_items.

Third Normal Form (3NF)

Must already be in 2NF, and no non-key column should depend on another non-key column (only on the primary key). For example, if employees stored both department_id and department_location, that would violate 3NF — department_location depends on department_id, not directly on the employee. That's why location lives only in departments.

When to break the rules: denormalization

Highly normalized designs minimize redundancy but require more joins to answer questions. For reporting and analytics workloads, it's common to deliberately denormalize — duplicating some data — to make read-heavy queries faster. This is a deliberate tradeoff, not a mistake, and is exactly what many data warehouses do on purpose.

Rule of thumb

Design new applications in at least 3NF by default. Only denormalize deliberately, once you understand a specific real performance need — not as a shortcut to avoid learning joins.

-- A ready-to-query practice database is loaded automatically before every run —
-- no CREATE TABLE needed. Available tables:
--   departments  (id, name, location)
--   employees    (id, first_name, last_name, email, department_id, job_title, salary, hire_date, manager_id)
--   customers    (id, customer_name, city, country, signup_date)
--   products     (id, product_name, category, price, stock_quantity)
--   orders       (id, customer_id, employee_id, order_date, status)
--   order_items  (id, order_id, product_id, quantity, unit_price)

SELECT * FROM employees WHERE department_id = 1;