10.2 Normalization: 1NF → BCNF — The Complete SQL Course

Normalization is the habit of putting each fact in the place where it belongs. It sounds academic until the cafe's pickup spreadsheet says Ada has two names, Latte has two prices, and nobody knows which facts are current rules versus order history.

In 10.1 you turned requirements into an ERD. Now you will take a messy spreadsheet shape and normalize it step by step: first enough to query honestly, then enough to stop repeated facts from drifting.

The three anomalies

An anomaly is a bug caused by the table's shape, not by one bad query. The classic three are:

Update anomaly: one fact is stored in several places, so changing it in only one place creates disagreement.
Insert anomaly: you cannot store a fact until some unrelated fact exists too.
Delete anomaly: deleting one row accidentally deletes the only copy of another fact.

Here is a pickup-order spreadsheet table. It is not evil; it is the shape people naturally make when they start from a form instead of a model.

CREATE TABLE pickup_sheet (
  spreadsheet_row integer PRIMARY KEY,
  order_label text NOT NULL,
  customer_email text NOT NULL,
  customer_name text NOT NULL,
  loyalty_tier text NOT NULL,
  tier_discount numeric(3,2) NOT NULL,
  pickup_day date NOT NULL,
  drink_1_name text NOT NULL,
  drink_1_qty integer NOT NULL,
  drink_1_price numeric(5,2) NOT NULL,
  drink_2_name text,
  drink_2_qty integer,
  drink_2_price numeric(5,2)
);
 
INSERT INTO pickup_sheet (
  spreadsheet_row, order_label, customer_email, customer_name,
  loyalty_tier, tier_discount, pickup_day,
  drink_1_name, drink_1_qty, drink_1_price,
  drink_2_name, drink_2_qty, drink_2_price
)
VALUES
  (1, 'A100', 'ada@cafe.test', 'Ada', 'gold', 0.10, DATE '2026-06-04',
   'Latte', 2, 4.50, 'Cookie', 1, 3.00),
  (2, 'A101', 'ada@cafe.test', 'Ada', 'gold', 0.10, DATE '2026-06-04',
   'Cold Brew', 1, 4.75, NULL, NULL, NULL),
  (3, 'K200', 'ken@cafe.test', 'Ken', 'standard', 0.00, DATE '2026-06-04',
   'Sencha', 1, 3.40, 'Latte', 1, 4.50),
  (4, 'A102', 'ada@cafe.test', 'Ada Lovelace', 'gold', 0.10, DATE '2026-06-05',
   'Latte', 1, 4.75, NULL, NULL, NULL);
 
SELECT count(*) AS order_rows,
       count(drink_1_name) + count(drink_2_name) AS drink_lines
FROM pickup_sheet;

CREATE TABLE
INSERT 0 4
 order_rows | drink_lines
------------+-------------
          4 |           6
(1 row)

Four rows are trying to hold six drink lines. That is the first smell: repeated column groups.

Now ask the table where repeated facts disagree, or where the model needs a sharper question:

SELECT customer_email,
       count(*) AS rows_seen,
       count(DISTINCT customer_name) AS names_seen
FROM pickup_sheet
GROUP BY customer_email
HAVING count(DISTINCT customer_name) > 1;
 
WITH drink_cells AS (
  SELECT drink_1_name AS drink_name, drink_1_price AS unit_price
  FROM pickup_sheet
  UNION ALL
  SELECT drink_2_name AS drink_name, drink_2_price AS unit_price
  FROM pickup_sheet
  WHERE drink_2_name IS NOT NULL
)
SELECT drink_name,
       count(*) AS cells_seen,
       count(DISTINCT unit_price) AS prices_seen
FROM drink_cells
GROUP BY drink_name
HAVING count(DISTINCT unit_price) > 1
ORDER BY drink_name;

 customer_email | rows_seen | names_seen
----------------+-----------+------------
 ada@cafe.test  |         3 |          2
(1 row)
 drink_name | cells_seen | prices_seen
------------+------------+-------------
 Latte      |          3 |           2
(1 row)

The customer result is the update anomaly without running an update: one customer fact has multiple copies, and the copies already disagree. The Latte result is a modeling question: if the spreadsheet price means "current menu price," it is drifting; if it means "what the customer was charged at order time," then both values are real history and must be preserved on the line. The insert anomaly is hiding too. You cannot add a new menu drink unless there is an order row to carry it. The delete anomaly is the mirror image: remove the last Sencha order row, and this table has forgotten Sencha exists.

1NF: one value per cell, no repeating groups

First normal form, or 1NF, says each column holds atomic values and the table has no repeating groups like drink_1_name, drink_2_name, drink_3_name.

For this cafe, 1NF means "drink lines are rows, not numbered columns." The row grain changes:

pickup_orders: one row per order.
order_lines: one row per drink line inside an order.

This is not theory decoration. Once drink lines are rows, six drink lines are six rows, whether tomorrow's order has one drink or twelve.

2NF and 3NF: the key, the whole key, nothing but the key

Second normal form, or 2NF, matters when a table has a composite key. Every non-key fact must depend on the whole key, not just part of it. In order_lines, the key is (order_id, line_no). quantity, drink_id, and the charged price for that line depend on the whole line identity. But pickup_day depends only on order_id, so it belongs on pickup_orders, not repeated on every line.

Third normal form, or 3NF, says non-key facts should not depend on other non-key facts. If loyalty_tier decides tier_discount, then tier_discount does not belong in the customer row. Put tiers in their own table and let customers point at the tier. The same idea separates reusable drink attributes from order history: the current menu name and current menu price belong with the drink, while the charged-at-order price belongs on the order line.

The old mnemonic is useful if you keep it practical: every non-key fact depends on the key, the whole key, and nothing but the key.

Normalize the spreadsheet

Here is the same story as normalized tables. Notice that the cleanup decisions are explicit: one current customer name for Ada, one current menu row for Latte, tier discounts stored once, and the charged price preserved on each order line.

CREATE TABLE loyalty_tiers (
  tier_name text PRIMARY KEY,
  discount_rate numeric(3,2) NOT NULL CHECK (discount_rate BETWEEN 0 AND 1)
);
 
CREATE TABLE cafe_customers (
  customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  display_name text NOT NULL,
  tier_name text NOT NULL REFERENCES loyalty_tiers(tier_name)
);
 
CREATE TABLE menu_drinks (
  drink_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  drink_name text NOT NULL UNIQUE,
  current_unit_price numeric(5,2) NOT NULL CHECK (current_unit_price >= 0)
);
 
CREATE TABLE pickup_orders (
  order_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_label text NOT NULL UNIQUE,
  customer_id integer NOT NULL REFERENCES cafe_customers(customer_id),
  pickup_day date NOT NULL
);
 
CREATE TABLE order_lines (
  order_id integer NOT NULL REFERENCES pickup_orders(order_id),
  line_no integer NOT NULL,
  drink_id integer NOT NULL REFERENCES menu_drinks(drink_id),
  quantity integer NOT NULL CHECK (quantity > 0),
  charged_unit_price numeric(5,2) NOT NULL CHECK (charged_unit_price >= 0),
  PRIMARY KEY (order_id, line_no)
);
 
INSERT INTO loyalty_tiers (tier_name, discount_rate)
VALUES
  ('gold', 0.10),
  ('standard', 0.00);
 
INSERT INTO cafe_customers (email, display_name, tier_name)
VALUES
  ('ada@cafe.test', 'Ada', 'gold'),
  ('ken@cafe.test', 'Ken', 'standard');
 
INSERT INTO menu_drinks (drink_name, current_unit_price)
VALUES
  ('Latte', 4.75),
  ('Cold Brew', 4.75),
  ('Cookie', 3.00),
  ('Sencha', 3.40);
 
INSERT INTO pickup_orders (order_label, customer_id, pickup_day)
VALUES
  ('A100', 1, DATE '2026-06-04'),
  ('A101', 1, DATE '2026-06-04'),
  ('K200', 2, DATE '2026-06-04'),
  ('A102', 1, DATE '2026-06-05');
 
INSERT INTO order_lines (order_id, line_no, drink_id, quantity, charged_unit_price)
VALUES
  (1, 1, 1, 2, 4.50),
  (1, 2, 3, 1, 3.00),
  (2, 1, 2, 1, 4.75),
  (3, 1, 4, 1, 3.40),
  (3, 2, 1, 1, 4.50),
  (4, 1, 1, 1, 4.75);
 
SELECT 'cafe_customers' AS table_name, count(*) AS row_count
FROM cafe_customers
UNION ALL
SELECT 'loyalty_tiers', count(*)
FROM loyalty_tiers
UNION ALL
SELECT 'menu_drinks', count(*)
FROM menu_drinks
UNION ALL
SELECT 'order_lines', count(*)
FROM order_lines
UNION ALL
SELECT 'pickup_orders', count(*)
FROM pickup_orders
ORDER BY table_name;

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 4
INSERT 0 4
INSERT 0 6
   table_name   | row_count
----------------+-----------
 cafe_customers |         2
 loyalty_tiers  |         2
 menu_drinks    |         4
 order_lines    |         6
 pickup_orders  |         4
(5 rows)

The verification query can now rebuild the useful spreadsheet view without storing the repeated facts in the spreadsheet shape:

SELECT po.order_label,
       cc.display_name,
       lt.discount_rate,
       ol.line_no,
       md.drink_name,
       ol.quantity,
       ol.charged_unit_price
FROM pickup_orders AS po
JOIN cafe_customers AS cc
  ON cc.customer_id = po.customer_id
JOIN loyalty_tiers AS lt
  ON lt.tier_name = cc.tier_name
JOIN order_lines AS ol
  ON ol.order_id = po.order_id
JOIN menu_drinks AS md
  ON md.drink_id = ol.drink_id
ORDER BY po.order_label, ol.line_no;

 order_label | display_name | discount_rate | line_no | drink_name | quantity | charged_unit_price
-------------+--------------+---------------+---------+------------+----------+--------------------
 A100        | Ada          |          0.10 |       1 | Latte      |        2 |               4.50
 A100        | Ada          |          0.10 |       2 | Cookie     |        1 |               3.00
 A101        | Ada          |          0.10 |       1 | Cold Brew  |        1 |               4.75
 A102        | Ada          |          0.10 |       1 | Latte      |        1 |               4.75
 K200        | Ken          |          0.00 |       1 | Sencha     |        1 |               3.40
 K200        | Ken          |          0.00 |       2 | Latte      |        1 |               4.50
(6 rows)

Normalization did not destroy the report. It made the report a query over cleaner facts.

sql — playgroundlive

CREATE TABLE pickup_sheet (
spreadsheet_row integer PRIMARY KEY,
order_label text NOT NULL,
customer_email text NOT NULL,
customer_name text NOT NULL,
loyalty_tier text NOT NULL,
tier_discount numeric(3,2) NOT NULL,
pickup_day date NOT NULL,
drink_1_name text NOT NULL,
drink_1_qty integer NOT NULL,
drink_1_price numeric(5,2) NOT NULL,
drink_2_name text,
drink_2_qty integer,
drink_2_price numeric(5,2)
);

INSERT INTO pickup_sheet (
spreadsheet_row, order_label, customer_email, customer_name,
loyalty_tier, tier_discount, pickup_day,
drink_1_name, drink_1_qty, drink_1_price,
drink_2_name, drink_2_qty, drink_2_price
)
VALUES
(1, 'A100', 'ada@cafe.test', 'Ada', 'gold', 0.10, DATE '2026-06-04',
 'Latte', 2, 4.50, 'Cookie', 1, 3.00),
(2, 'A101', 'ada@cafe.test', 'Ada', 'gold', 0.10, DATE '2026-06-04',
 'Cold Brew', 1, 4.75, NULL, NULL, NULL),
(3, 'K200', 'ken@cafe.test', 'Ken', 'standard', 0.00, DATE '2026-06-04',
 'Sencha', 1, 3.40, 'Latte', 1, 4.50),
(4, 'A102', 'ada@cafe.test', 'Ada Lovelace', 'gold', 0.10, DATE '2026-06-05',
 'Latte', 1, 4.75, NULL, NULL, NULL);

WITH drink_cells AS (
SELECT drink_1_name AS drink_name, drink_1_price AS unit_price
FROM pickup_sheet
UNION ALL
SELECT drink_2_name AS drink_name, drink_2_price AS unit_price
FROM pickup_sheet
WHERE drink_2_name IS NOT NULL
)
SELECT drink_name,
     count(*) AS cells_seen,
     count(DISTINCT unit_price) AS prices_seen
FROM drink_cells
GROUP BY drink_name
ORDER BY drink_name;

CREATE TABLE loyalty_tiers (
tier_name text PRIMARY KEY,
discount_rate numeric(3,2) NOT NULL CHECK (discount_rate BETWEEN 0 AND 1)
);

CREATE TABLE cafe_customers (
customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
display_name text NOT NULL,
tier_name text NOT NULL REFERENCES loyalty_tiers(tier_name)
);

CREATE TABLE menu_drinks (
drink_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
drink_name text NOT NULL UNIQUE,
current_unit_price numeric(5,2) NOT NULL CHECK (current_unit_price >= 0)
);

CREATE TABLE pickup_orders (
order_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_label text NOT NULL UNIQUE,
customer_id integer NOT NULL REFERENCES cafe_customers(customer_id),
pickup_day date NOT NULL
);

CREATE TABLE order_lines (
order_id integer NOT NULL REFERENCES pickup_orders(order_id),
line_no integer NOT NULL,
drink_id integer NOT NULL REFERENCES menu_drinks(drink_id),
quantity integer NOT NULL CHECK (quantity > 0),
charged_unit_price numeric(5,2) NOT NULL CHECK (charged_unit_price >= 0),
PRIMARY KEY (order_id, line_no)
);

-- break it: uncomment — an order cannot point at a customer before customers exist
-- INSERT INTO pickup_orders (order_label, customer_id, pickup_day)
-- VALUES ('BAD1', 99, DATE '2026-06-06');

⌘/Ctrl + Enter to run

Normalization is the default shape for transactional data because it makes writes honest. Next, 10.3 shows the controlled exception: denormalization on purpose, where you duplicate facts deliberately and accept the consistency contract that comes with it.

Checkpoint

Answer all three to mark this lesson complete