Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 45 of 49

Normalization: splitting data into well-designed tables

Learn why repeating data in one wide table causes errors, and how to split it into related tables using the first three normal forms, with a worked example.

16 minDesigning databasesUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonSpot repeated and multi-valued data in a table, split it into related tables, and join the pieces back together.

One fact, one place

Imagine a spreadsheet of orders where every row repeats the customer's name and city. It feels convenient until the customer moves house. Now you must find every row and correct it, and if you miss one, your data says the customer lives in two cities at once.

Normalization is the habit of designing tables so that this cannot happen. Each fact is stored once, in the table that it describes, and other tables point to it with keys. After this lesson you will be able to look at a flat table, see where it repeats itself, and split it into tables that are linked by the primary and foreign keys from the last lesson.

This is a design topic rather than a syntax topic, and databases do not treat it differently, so everything here applies to any of them. The examples are new tables in a scratch SQLite 3.54 database.

A badly designed table

Here is an orders table built the way people often start out:

sql
CREATE TABLE orders_flat (
  order_id       INTEGER,
  order_date     DATE,
  customer_name  VARCHAR(50),
  customer_city  VARCHAR(50),
  product_names  VARCHAR(200),
  product_prices VARCHAR(100)
);

INSERT INTO orders_flat VALUES
  (1, '2026-03-01', 'Anita Joshi', 'Pune', 'Pen, Notebook', '10, 50'),
  (2, '2026-03-04', 'Anita Joshi', 'Pune', 'Stapler', '120'),
  (3, '2026-03-09', 'Bharat Rao', 'Delhi', 'Pen', '10');

SELECT * FROM orders_flat;
result
+----------+------------+---------------+---------------+---------------+----------------+
| order_id | order_date | customer_name | customer_city | product_names | product_prices |
+----------+------------+---------------+---------------+---------------+----------------+
|        1 | 2026-03-01 | Anita Joshi   | Pune          | Pen, Notebook | 10, 50         |
|        2 | 2026-03-04 | Anita Joshi   | Pune          | Stapler       | 120            |
|        3 | 2026-03-09 | Bharat Rao    | Delhi         | Pen           | 10             |
+----------+------------+---------------+---------------+---------------+----------------+

Several things are wrong, and each one has a name.

Lists in a cell. Order 1 packs two products and two prices into single cells. You cannot easily total the prices, count orders per product, or stop a typo in a product name, because the database sees only a string. Rule one, first normal form, says that every cell holds one value, and that a table has no repeating groups of columns either, such as product1, product2 and product3.

Repeated facts. Anita's name and city appear on two rows. Suppose she moves and you update only the first order:

sql
UPDATE orders_flat SET customer_city = 'Mumbai' WHERE order_id = 1;

SELECT DISTINCT customer_name, customer_city
FROM orders_flat
ORDER BY customer_name, customer_city;
result
+---------------+---------------+
| customer_name | customer_city |
+---------------+---------------+
| Anita Joshi   | Mumbai        |
| Anita Joshi   | Pune          |
| Bharat Rao    | Delhi         |
+---------------+---------------+

Anita now lives in two cities. This is an update anomaly. Related problems are the insert anomaly, where you cannot record a customer who has not ordered yet because every row is an order, and the delete anomaly, where deleting a customer's only order also erases the fact that the customer exists.

The three normal forms in plain words

The formal definitions are strict, but the working rules are short:

  1. First normal form (1NF). One value per cell, no repeating groups. Each row is identified by a key.
  2. Second normal form (2NF). Every non-key column depends on the whole key. This matters when the key has several columns. In an order lines table keyed by order and product, the product's price depends on the product alone, so it belongs in the products table.
  3. Third normal form (3NF). Non-key columns depend on the key and nothing else. A customer's city depends on the customer, not on the order, so it belongs in the customers table.

A handy way to remember all three: every column should describe "the key, the whole key, and nothing but the key".

Four tables remove every problem above. Customers and products each get their own table. Orders refer to a customer. And because an order can have many products while a product appears on many orders, a linking table connects them, just as assignments connects employees and projects in the sample database:

sql
PRAGMA foreign_keys = ON;

CREATE TABLE customers (
  id   INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  city VARCHAR(50) NOT NULL
);

CREATE TABLE products (
  id    INTEGER PRIMARY KEY,
  name  VARCHAR(50) NOT NULL UNIQUE,
  price INTEGER NOT NULL
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (id),
  order_date  DATE NOT NULL
);

CREATE TABLE order_items (
  order_id   INTEGER NOT NULL REFERENCES orders (id),
  product_id INTEGER NOT NULL REFERENCES products (id),
  quantity   INTEGER NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);

INSERT INTO customers VALUES (1, 'Anita Joshi', 'Pune'), (2, 'Bharat Rao', 'Delhi');
INSERT INTO products VALUES (1, 'Pen', 10), (2, 'Notebook', 50), (3, 'Stapler', 120);
INSERT INTO orders VALUES (1, 1, '2026-03-01'), (2, 1, '2026-03-04'), (3, 2, '2026-03-09');
INSERT INTO order_items VALUES (1, 1, 1), (1, 2, 1), (2, 3, 1), (3, 1, 4);

Now Anita's move is a single change to a single row:

sql
UPDATE customers SET city = 'Mumbai' WHERE id = 1;

Every order that belongs to her reflects it immediately, because the city is read from one place. You get the flat view back with joins, which is the reason joins exist:

sql
SELECT o.id AS order_id, o.order_date, c.name AS customer, c.city,
       p.name AS product, i.quantity, p.price * i.quantity AS line_total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id
ORDER BY o.id, p.id;
result
+----------+------------+-------------+--------+----------+----------+------------+
| order_id | order_date | customer    | city   | product  | quantity | line_total |
+----------+------------+-------------+--------+----------+----------+------------+
|        1 | 2026-03-01 | Anita Joshi | Mumbai | Pen      |        1 |         10 |
|        1 | 2026-03-01 | Anita Joshi | Mumbai | Notebook |        1 |         50 |
|        2 | 2026-03-04 | Anita Joshi | Mumbai | Stapler  |        1 |        120 |
|        3 | 2026-03-09 | Bharat Rao  | Delhi  | Pen      |        4 |         40 |
+----------+------------+-------------+--------+----------+----------+------------+

You can now count orders per product, total the money, and add a customer who has no orders yet, which the flat table could not do. The foreign keys and the CHECK constraint stop bad rows from getting in.

There is a design question hidden in this example. The price lives on the product, so if the price changes, old orders would show the new price when you recalculate them. Real shops copy the price into the order line at the time of sale, which is deliberate duplication, because the price on an invoice is a historical fact. Normalization tells you to avoid accidental copies. It does not forbid recording a fact that is genuinely different, such as the price that was charged.

When to break the rules

Normalized designs are the right starting point for data that gets written often, because they keep the facts consistent. Data that is mostly read, such as a nightly reporting table or a search index, is sometimes denormalized on purpose: joined together and copied, so queries are simpler and faster. The price is that you must keep the copy in step, usually by rebuilding it on a schedule. Choose that deliberately, after normalizing first.

Some other design habits help. Name tables for what they hold, keep one naming style, use singular or plural consistently, and give every table a primary key. The MySQL table design lesson covers the same ideas with a shop example.

Common mistakes

  • Packing lists into one column. Comma-separated ids and names cannot be joined or constrained. Use a child table with one row per item.
  • Repeating column groups such as phone1, phone2, phone3. Use a separate table.
  • Copying a fact into many tables without a plan to keep the copies consistent.
  • Over-normalizing. Splitting a street address into five tables can cost more in joins than it saves in tidiness. Normalize until each fact is stored once, and then stop.
  • No keys. A table without a primary key has no way to say which row you mean.

Exercise

A library keeps a single table with one row per loan, holding the member's name and email, the book's title and author, and the loan date. Split it into three tables, members, books and loans, and write a query listing each loan with the member's name, the book's title and author, and the date.

Show one possible answer
sql
PRAGMA foreign_keys = ON;

CREATE TABLE members (
  id    INTEGER PRIMARY KEY,
  name  VARCHAR(60) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE books (
  id     INTEGER PRIMARY KEY,
  title  VARCHAR(100) NOT NULL,
  author VARCHAR(60) NOT NULL
);
CREATE TABLE loans (
  id        INTEGER PRIMARY KEY,
  member_id INTEGER NOT NULL REFERENCES members (id),
  book_id   INTEGER NOT NULL REFERENCES books (id),
  loan_date DATE NOT NULL
);

INSERT INTO members VALUES (1, 'Sana Iqbal', 'sana@example.com');
INSERT INTO books VALUES (1, 'Quiet Rivers', 'Leela Menon'), (2, 'Salt Roads', 'Leela Menon');
INSERT INTO loans VALUES (1, 1, 1, '2026-04-02'), (2, 1, 2, '2026-04-09');

SELECT l.id, m.name, b.title, b.author, l.loan_date
FROM loans l
JOIN members m ON m.id = l.member_id
JOIN books b ON b.id = l.book_id
ORDER BY l.id;
result
+----+------------+--------------+-------------+------------+
| id | name       | title        | author      | loan_date  |
+----+------------+--------------+-------------+------------+
|  1 | Sana Iqbal | Quiet Rivers | Leela Menon | 2026-04-02 |
|  2 | Sana Iqbal | Salt Roads   | Leela Menon | 2026-04-09 |
+----+------------+--------------+-------------+------------+

The member's email and each book's author are stored once, however many loans exist. The author is still a plain column on books. If authors had their own details, you would split them into an authors table too.

Key points

  • Normalization means storing each fact once, in the table where it belongs, and linking tables with keys.
  • First normal form: one value per cell, no lists packed into a column. Second and third: every column describes the key and nothing else.
  • Repeated data causes update, insert and delete anomalies, where the same fact can disagree with itself.
  • Normalization is a default, not a religion. Reporting tables are sometimes denormalized on purpose, and that choice should be deliberate.

Check yourself

1. A column product_names holds the text 'Pen, Notebook'. Which normal form does that break?
2. A customer's city is stored on every order row, and the customer moves. What problem appears?
3. Which design splits a many-to-many relationship, such as orders and products, correctly?
4. When might you deliberately denormalize?