Database normalization: 1NF, 2NF, 3NF with examples

#Why it matters

Database normalization is how you split data into tables so that every fact is stored once. Store a product name on half a million sales lines and renaming it means updating 2,313 rows, and missing one leaves the database disagreeing with itself. Store it once in a products table and the rename is one row. Backend engineers design these tables, data engineers load them, and analysts query them every day, so "explain 1NF, 2NF and 3NF" is a standard interview question for all three. SQL appears in 88% of Data Analyst and 80% of Data Engineer job descriptions we studied.

This lesson teaches normalization the way you use it at work: write down the functional dependencies, check them against real data (they are often false), split a flat table into 3NF, and prove nothing was lost.

#The concept

#The three anomalies of a flat table

Picture one big sheet: invoice number, date, customer, country, product code, product name, quantity, price, one row per line sold. It has three problems:

• Update anomaly: a fact repeated on many rows (a product name) must be changed on all of them. Miss one and the data contradicts itself.

• Insert anomaly: you cannot record a fact without an unrelated one. A new product cannot exist until someone buys it, because every row is a sale.

• Delete anomaly: deleting rows deletes facts you wanted to keep. Remove the only sale of a product and the product's name is gone.

Normalization removes these by giving each kind of fact its own table.

#Functional dependencies

A -> B ("A determines B") means: every time two rows have the same A, they have the same B. invoice_no -> customer_id says an invoice belongs to one customer. stock_code -> description says a product code has one name.

Dependencies come from business rules, but data often breaks the rules you assume, so check them:

-- keys that break "stock_code -> description"
SELECT stock_code
FROM sales
GROUP BY stock_code
HAVING count(DISTINCT description) > 1;

Zero rows means the dependency holds in this data (it may still fail tomorrow). Any rows mean it does not, and the design must cope.

#The normal forms, in plain words

The usual summary: every non-key column depends on the key, the whole key, and nothing but the key.

#Crow's foot notation

Entity-relationship diagrams draw tables as boxes and relationships as lines. The line ends say how many: a single bar means one, a circle means zero (optional), and a three-pronged "crow's foot" means many. customers ||--o{ invoices reads "one customer has zero or many invoices; each invoice has exactly one customer". When you finish a design, draw it this way; reviewers read it faster than SQL.

#When to denormalise

3NF is right for OLTP systems: apps that insert and update rows all day, where consistency matters most. For analytics, data is written once in bulk and read many times, so warehouses deliberately repeat attributes (a sales fact table joined to wide dimension tables, or one flat table for a dashboard) to avoid joins. Denormalising is fine when it is a copy built from the normalised source, refreshed by a job, never edited by hand. The cost: more storage, refresh logic, and a window where the copy is stale. Dimensional modelling (facts, dimensions, star schemas) is its own skill, covered in data modeling.

#Worked example: normalising Online Retail

The UCI Online Retail dataset (541,909 lines, CC BY 4.0) is exactly the flat sheet described above. We use PostgreSQL 16; every output below is from a real run.

CREATE TABLE raw_retail (
  invoice_no   text,
  stock_code   text,
  description  text,
  quantity     int,
  invoice_date text,
  unit_price   numeric(10,3),
  customer_id  int,
  country      text
);
\copy raw_retail FROM 'online-retail-dataset.csv' WITH (FORMAT csv, HEADER true)

CREATE TABLE
COPY 541909

#Step 1: measure the anomalies

SELECT count(*) AS lines, count(DISTINCT description) AS spellings
FROM raw_retail
WHERE stock_code = '85123A';

SELECT count(*) AS products,
       count(*) FILTER (WHERE invoices = 1) AS on_one_invoice
FROM (SELECT stock_code, count(DISTINCT invoice_no) AS invoices
      FROM raw_retail GROUP BY stock_code) AS t;

 lines | spellings 
-------+-----------
  2313 |         4
(1 row)
 
 products | on_one_invoice 
----------+----------------
     4070 |            233
(1 row)

Product 85123A's name is stored 2,313 times and already has 4 different spellings: the update anomaly has happened. And 233 of 4,070 products appear on a single invoice, so deleting one invoice (a cancelled test order, a GDPR erasure) would erase that product from the database entirely: the delete anomaly.

#Step 2: what breaking 1NF costs

Suppose someone "simplifies" the data into one row per invoice with the products as a comma-separated list:

CREATE TABLE invoice_baskets AS
SELECT invoice_no, string_agg(DISTINCT stock_code, ',' ORDER BY stock_code) AS stock_codes
FROM raw_retail
GROUP BY invoice_no;

SELECT * FROM invoice_baskets WHERE invoice_no = '536365';

SELECT count(*) FILTER (WHERE stock_codes LIKE '%47566%') AS like_matches,
       count(*) FILTER (WHERE '47566' = ANY (string_to_array(stock_codes, ','))) AS exact_matches,
       (SELECT count(DISTINCT invoice_no) FROM raw_retail WHERE stock_code = '47566') AS from_rows
FROM invoice_baskets;

SELECT 25900
 invoice_no |                  stock_codes                  
------------+-----------------------------------------------
 536365     | 21730,22752,71053,84029E,84029G,84406B,85123A
(1 row)
 
 like_matches | exact_matches | from_rows 
--------------+---------------+-----------
         1977 |          1706 |      1706
(1 row)

"Which invoices contain product 47566 (party bunting)?" The obvious LIKE '%47566%' returns 1,977 invoices, 271 too many, because it also matches 47566B (tea time party bunting). Getting the right 1,706 needs string splitting on every query, and no ordinary index can help. With one row per line (1NF), it is a plain WHERE stock_code = '47566'.

#Step 3: write down the dependencies and test them

Here are the rules you would assume for this business, each checked against the data:

SELECT n, dependency, keys_that_break_it FROM (
SELECT 1 AS n, 'invoice_no -> customer_id' AS dependency, count(*) AS keys_that_break_it
FROM (SELECT invoice_no FROM raw_retail GROUP BY 1
      HAVING count(DISTINCT coalesce(customer_id, -1)) > 1) AS t
UNION ALL
SELECT 2, 'invoice_no -> country', count(*)
FROM (SELECT invoice_no FROM raw_retail GROUP BY 1 HAVING count(DISTINCT country) > 1) AS t
UNION ALL
SELECT 3, 'invoice_no -> invoice_date', count(*)
FROM (SELECT invoice_no FROM raw_retail GROUP BY 1 HAVING count(DISTINCT invoice_date) > 1) AS t
UNION ALL
SELECT 4, 'customer_id -> country', count(*)
FROM (SELECT customer_id FROM raw_retail WHERE customer_id IS NOT NULL GROUP BY 1
      HAVING count(DISTINCT country) > 1) AS t
UNION ALL
SELECT 5, 'stock_code -> description', count(*)
FROM (SELECT stock_code FROM raw_retail GROUP BY 1 HAVING count(DISTINCT description) > 1) AS t
UNION ALL
SELECT 6, 'stock_code -> unit_price', count(*)
FROM (SELECT stock_code FROM raw_retail GROUP BY 1 HAVING count(DISTINCT unit_price) > 1) AS t
UNION ALL
SELECT 7, '(invoice_no, stock_code) is unique', count(*)
FROM (SELECT invoice_no, stock_code FROM raw_retail GROUP BY 1, 2 HAVING count(*) > 1) AS t
) AS checks
ORDER BY n;

 n |             dependency             | keys_that_break_it 
---+------------------------------------+--------------------
 1 | invoice_no -> customer_id          |                  0
 2 | invoice_no -> country              |                  0
 3 | invoice_no -> invoice_date         |                 43
 4 | customer_id -> country             |                  8
 5 | stock_code -> description          |                650
 6 | stock_code -> unit_price           |               3603
 7 | (invoice_no, stock_code) is unique |               9694
(7 rows)

Each line is a design decision:

1. Invoice determines customer and country (0 violations). Both belong in an invoices table.

2. Invoice determines date fails for 43 invoices. Their lines were stamped a minute apart while being keyed in (below). The rule is right and the data is noisy, so store one timestamp per invoice: the first.

3. Customer determines country fails for 8 customers. If we put country on customers, those 8 lose information. The data says country is a fact about the invoice (where that order went), which is also why rule 2 holds. Textbook designs often get this wrong: "customer lives in one country" is an assumption, not a rule.

4. Product determines description fails for 650 codes: a mix of blank descriptions, notes like "damaged", spacing and case variants, and real renames (SQL string functions: TRIM, SUBSTRING, REPLACE and regex digs into them). A product still has one name, so we keep one per code (the most common) in products and accept that the variants are lost, after checking what they are.

5. Product determines price fails for 3,603 codes: prices change and differ by order size. So unit_price is not a product attribute; it belongs on the invoice line, at the price actually charged.

6. (invoice_no, stock_code) is not a key: 9,694 combinations appear more than once (repeat scans of the same item). The line table needs its own surrogate key.

SELECT invoice_no, min(invoice_date) AS first_stamp, max(invoice_date) AS last_stamp
FROM raw_retail
GROUP BY invoice_no
HAVING count(DISTINCT invoice_date) > 1
ORDER BY invoice_no
LIMIT 3;

 invoice_no |   first_stamp   |   last_stamp    
------------+-----------------+-----------------
 536591     | 12/1/2010 16:57 | 12/1/2010 16:58
 540185     | 1/5/2011 13:40  | 1/5/2011 13:41
 541596     | 1/19/2011 16:18 | 1/19/2011 16:19
(3 rows)

One minute apart, as expected.

#Step 4: build the 3NF tables

CREATE TABLE customers (
  customer_id int PRIMARY KEY
);

CREATE TABLE products (
  stock_code  text PRIMARY KEY,
  description text
);

CREATE TABLE invoices (
  invoice_no  text PRIMARY KEY,
  customer_id int REFERENCES customers (customer_id),
  invoiced_at timestamp NOT NULL,
  country     text NOT NULL
);

CREATE TABLE invoice_lines (
  line_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  invoice_no text NOT NULL REFERENCES invoices (invoice_no),
  stock_code text NOT NULL REFERENCES products (stock_code),
  quantity   int NOT NULL,
  unit_price numeric(10,3) NOT NULL
);

INSERT INTO customers (customer_id)
SELECT DISTINCT customer_id FROM raw_retail WHERE customer_id IS NOT NULL;

INSERT INTO products (stock_code, description)
SELECT DISTINCT ON (stock_code) stock_code, description
FROM raw_retail
GROUP BY stock_code, description
ORDER BY stock_code, count(*) DESC, description;

INSERT INTO invoices (invoice_no, customer_id, invoiced_at, country)
SELECT invoice_no,
       min(customer_id),
       min(to_timestamp(invoice_date, 'MM/DD/YYYY HH24:MI')::timestamp),
       min(country)
FROM raw_retail
GROUP BY invoice_no;

INSERT INTO invoice_lines (invoice_no, stock_code, quantity, unit_price)
SELECT invoice_no, stock_code, quantity, unit_price
FROM raw_retail;

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 4372
INSERT 0 4070
INSERT 0 25900
INSERT 0 541909

Check each table against the forms. Every column of invoices depends on invoice_no alone. products has one non-key column that depends on the key. invoice_lines has a single-column key, so a partial dependency (2NF) cannot happen, and description is not repeated on it. customers holds only the ID because this dataset knows nothing else about a customer; in a real system name, email and phone would live there. min(customer_id) and min(country) are safe only because step 3 proved there is exactly one value per invoice. customer_id stays nullable because 135,080 lines (the anonymous ones) have no customer, which is a business fact, not a design flaw. The keys and REFERENCES constraints are covered in SQL CREATE TABLE with primary keys and foreign keys.

#Step 5: prove nothing was lost

Joining the tables back must reproduce every line's facts:

SELECT (SELECT count(*) FROM raw_retail) AS raw_lines,
       (SELECT count(*) FROM invoice_lines) AS lines,
       (SELECT sum(quantity * unit_price) FROM raw_retail) AS raw_value,
       (SELECT sum(quantity * unit_price) FROM invoice_lines) AS lines_value;

SELECT count(*) AS rows_that_differ
FROM (
  SELECT invoice_no, stock_code, quantity, unit_price, customer_id, country
  FROM raw_retail
  EXCEPT ALL
  SELECT l.invoice_no, l.stock_code, l.quantity, l.unit_price, i.customer_id, i.country
  FROM invoice_lines AS l
  JOIN invoices AS i USING (invoice_no)
) AS d;

SELECT count(*) AS lines,
       count(*) FILTER (WHERE r.description IS DISTINCT FROM p.description) AS description_differs
FROM raw_retail AS r
JOIN products AS p USING (stock_code);

 raw_lines | lines  |  raw_value  | lines_value 
-----------+--------+-------------+-------------
    541909 | 541909 | 9747747.934 | 9747747.934
(1 row)
 
 rows_that_differ 
------------------
                0
(1 row)
 
 lines  | description_differs 
--------+---------------------
 541909 |                 9183
(1 row)

Same line count, same total value, and EXCEPT ALL (which respects duplicates) finds no line whose invoice, product, quantity, price, customer or country changed. The only information that changed is what we decided to change: 9,183 lines now show their product's standard name instead of a variant or a blank. Write that decision down; someone will ask.

The update anomaly is gone too. Renaming product 85123A, run in a transaction and rolled back:

BEGIN;
UPDATE raw_retail SET description = 'WHITE HANGING HEART TEALIGHT HOLDER' WHERE stock_code = '85123A';
UPDATE products SET description = 'WHITE HANGING HEART TEALIGHT HOLDER' WHERE stock_code = '85123A';
ROLLBACK;

BEGIN
UPDATE 2313
UPDATE 1
ROLLBACK

2,313 rows in the flat table, one row in the normalised one.

#Common mistakes

• Assuming dependencies instead of testing them. "Customer has one country" was false for 8 customers here. One GROUP BY ... HAVING count(DISTINCT ...) > 1 per rule settles it.

• Putting changing values on the wrong entity. Price varies per sale, so it lives on the line, not the product. The same goes for addresses on orders.

• Natural keys that are not unique. (invoice_no, stock_code) looked like a key and had 9,694 duplicates. Check before you declare a primary key, or add a surrogate.

• Lists in a column. Comma-separated values break 1NF and give wrong answers with LIKE.

• Normalising analytics tables into dozens of joins. For reporting, a refreshed denormalised copy is often the right choice.

• Denormalising by hand-editing copies. A denormalised table must be rebuilt from the source, or it drifts.

• Skipping the round-trip check. Compare counts, totals and an EXCEPT ALL after every redesign.

• Over-normalising lookups. A table for "country names" with one column adds a join and protects nothing unless you need the constraint.

#Interview angle

#Practice task

Olist (CC BY-NC-SA 4.0) ships its data already split into tables. Join them back into one flat table, then normalise it yourself and see whether you arrive at the same design.

1. Load orders, customers and sellers as in earlier lessons (SQL cohort analysis and customer retention and SQL string functions: TRIM, SUBSTRING, REPLACE and regex), plus olist_order_items_dataset.csv and olist_products_dataset.csv.

2. Build flat_sales: one row per order item with order, customer, product category, seller and price columns.

3. Test these dependencies: order_id -> customer_id, order_id -> seller_id, customer_id -> customer_unique_id, customer_unique_id -> customer_city, product_id -> category, seller_id -> seller_city, seller_zip_code_prefix -> seller_city, (order_id, order_item_id) unique, product_id -> price.

4. Design 3NF tables based on what held, create them with primary keys, and prove the join reproduces flat_sales exactly.

CREATE TABLE order_items (
  order_id text, order_item_id int, product_id text, seller_id text,
  shipping_limit_date timestamp, price numeric(10,2), freight_value numeric(10,2)
);
\copy order_items FROM 'olist_order_items_dataset.csv' WITH (FORMAT csv, HEADER true)

CREATE TABLE products (
  product_id text PRIMARY KEY, product_category_name text,
  product_name_lenght int, product_description_lenght int, product_photos_qty int,
  product_weight_g int, product_length_cm int, product_height_cm int, product_width_cm int
);
\copy products FROM 'olist_products_dataset.csv' WITH (FORMAT csv, HEADER true)

CREATE TABLE flat_sales AS
SELECT i.order_id, i.order_item_id, o.order_purchase_timestamp, o.customer_id, c.customer_unique_id, c.customer_city, c.customer_state,
       i.product_id, p.product_category_name, i.seller_id, s.seller_zip_code_prefix, s.seller_city, s.seller_state, i.price, i.freight_value
FROM order_items i JOIN orders o USING (order_id) JOIN customers c USING (customer_id)
JOIN products p USING (product_id) JOIN sellers s USING (seller_id);

CREATE TABLE
COPY 112650
CREATE TABLE
COPY 32951
SELECT 112650

SELECT 1 AS n, 'order_id -> customer_id' AS dependency, count(*) AS keys_that_break_it FROM (SELECT order_id FROM flat_sales GROUP BY 1 HAVING count(DISTINCT customer_id) > 1) t
UNION ALL SELECT 2, 'order_id -> seller_id', count(*) FROM (SELECT order_id FROM flat_sales GROUP BY 1 HAVING count(DISTINCT seller_id) > 1) t
UNION ALL SELECT 3, 'customer_id -> customer_unique_id', count(*) FROM (SELECT customer_id FROM flat_sales GROUP BY 1 HAVING count(DISTINCT customer_unique_id) > 1) t
UNION ALL SELECT 4, 'customer_unique_id -> customer_city', count(*) FROM (SELECT customer_unique_id FROM flat_sales GROUP BY 1 HAVING count(DISTINCT customer_city) > 1) t
UNION ALL SELECT 5, 'product_id -> category', count(*) FROM (SELECT product_id FROM flat_sales GROUP BY 1 HAVING count(DISTINCT coalesce(product_category_name,'?')) > 1) t
UNION ALL SELECT 6, 'seller_id -> seller_city', count(*) FROM (SELECT seller_id FROM flat_sales GROUP BY 1 HAVING count(DISTINCT seller_city) > 1) t
UNION ALL SELECT 7, 'seller_zip_code_prefix -> seller_city', count(*) FROM (SELECT seller_zip_code_prefix FROM flat_sales GROUP BY 1 HAVING count(DISTINCT seller_city) > 1) t
UNION ALL SELECT 8, '(order_id, order_item_id) is unique', count(*) FROM (SELECT order_id, order_item_id FROM flat_sales GROUP BY 1,2 HAVING count(*) > 1) t
UNION ALL SELECT 9, 'product_id -> price', count(*) FROM (SELECT product_id FROM flat_sales GROUP BY 1 HAVING count(DISTINCT price) > 1) t
ORDER BY n;

 n |              dependency               | keys_that_break_it 
---+---------------------------------------+--------------------
 1 | order_id -> customer_id               |                  0
 2 | order_id -> seller_id                 |               1278
 3 | customer_id -> customer_unique_id     |                  0
 4 | customer_unique_id -> customer_city   |                118
 5 | product_id -> category                |                  0
 6 | seller_id -> seller_city              |                  0
 7 | seller_zip_code_prefix -> seller_city |                 34
 8 | (order_id, order_item_id) is unique   |                  0
 9 | product_id -> price                   |               5900
(9 rows)

CREATE SCHEMA shop3nf;

CREATE TABLE shop3nf.customers AS
SELECT DISTINCT customer_unique_id FROM flat_sales;

CREATE TABLE shop3nf.customer_addresses AS
SELECT DISTINCT customer_id, customer_unique_id, customer_city, customer_state FROM flat_sales;

CREATE TABLE shop3nf.sellers AS
SELECT DISTINCT seller_id, seller_zip_code_prefix, seller_city, seller_state FROM flat_sales;

CREATE TABLE shop3nf.products AS
SELECT DISTINCT product_id, product_category_name FROM flat_sales;

CREATE TABLE shop3nf.orders AS
SELECT DISTINCT order_id, customer_id, order_purchase_timestamp FROM flat_sales;

CREATE TABLE shop3nf.order_items AS
SELECT order_id, order_item_id, product_id, seller_id, price, freight_value FROM flat_sales;

ALTER TABLE shop3nf.customers ADD PRIMARY KEY (customer_unique_id);
ALTER TABLE shop3nf.customer_addresses ADD PRIMARY KEY (customer_id);
ALTER TABLE shop3nf.sellers ADD PRIMARY KEY (seller_id);
ALTER TABLE shop3nf.products ADD PRIMARY KEY (product_id);
ALTER TABLE shop3nf.orders ADD PRIMARY KEY (order_id);
ALTER TABLE shop3nf.order_items ADD PRIMARY KEY (order_id, order_item_id);

SELECT count(*) AS rows_that_differ FROM (
  SELECT * FROM flat_sales
  EXCEPT ALL
  SELECT i.order_id, i.order_item_id, o.order_purchase_timestamp, o.customer_id, a.customer_unique_id,
         a.customer_city, a.customer_state, i.product_id, p.product_category_name, i.seller_id,
         s.seller_zip_code_prefix, s.seller_city, s.seller_state, i.price, i.freight_value
  FROM shop3nf.order_items AS i
  JOIN shop3nf.orders AS o USING (order_id)
  JOIN shop3nf.customer_addresses AS a USING (customer_id)
  JOIN shop3nf.products AS p USING (product_id)
  JOIN shop3nf.sellers AS s USING (seller_id)
) AS d;

CREATE SCHEMA
SELECT 95420
SELECT 98666
SELECT 3095
SELECT 32951
SELECT 98666
SELECT 112650
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
 rows_that_differ 
------------------
                0
(1 row)

#Further reading

• PostgreSQL: constraints: primary keys, foreign keys and unique constraints that enforce a design.

• Database normalization (Wikipedia): the formal definitions up to 6NF, with examples.

• Entity-relationship model (Wikipedia): crow's foot and other notations.

• UCI Online Retail dataset (CC BY 4.0), CSV copy: online-retail-dataset.csv.

• Olist datasets (CC BY-NC-SA 4.0).