Chapter 08

Data Modelling and Normalisation

How to decide which tables exist, how they connect and where each fact lives — keys, relationships, ER diagrams, the normal forms with real before-and-after tables, and when to break the rules on purpose.

9 topics

Parts marked Advanced are extra depth. Skip them on a first read or a quick revision.

#Keys: candidate, primary, composite, surrogate and natural

Added

A key is a set of columns whose values identify exactly one row. The vocabulary sounds academic, but every term answers a practical question:

A super key is any set of columns that is unique — (id), (id, name), (email, city)… Adding columns to a unique set keeps it unique, so most super keys are bloated.
A candidate key is a minimal super key: unique, and you can't drop any column without losing uniqueness. In customers, both id and email are candidate keys.
The primary key is the one candidate key you pick as the row's official identity. Foreign keys point at it, and in InnoDB the table is physically stored in its order. The other candidate keys become UNIQUE constraints (sometimes called alternate keys).
A composite key has more than one column, like order_items (order_id, product_id).
A natural key already means something in the real world (an email, an ISBN, a country code); a surrogate key is invented by the database purely to identify rows (AUTO_INCREMENT, a UUID).

The analogy: a person has a passport number, a national ID number and an email address — three candidate keys. A company's HR system picks one as the official identity (the primary key) and usually invents its own employee number (a surrogate) so nothing breaks when a passport is renewed.

SQL

SELECT COUNT(*) AS total_rows,
       COUNT(DISTINCT id)    AS distinct_ids,
       COUNT(DISTINCT email) AS distinct_emails,
       COUNT(DISTINCT name)  AS distinct_names,
       COUNT(DISTINCT city)  AS distinct_cities,
       COUNT(city)           AS non_null_cities
FROM customers;
SELECT table_name, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS key_columns, non_unique
FROM information_schema.statistics
WHERE table_schema = 'ch07' AND table_name IN ('customers', 'order_items')
GROUP BY table_name, index_name, non_unique ORDER BY table_name, index_name;
INSERT INTO order_items VALUES (1, 1, 5, 899.00);

Result

+------------+--------------+-----------------+----------------+-----------------+-----------------+
| total_rows | distinct_ids | distinct_emails | distinct_names | distinct_cities | non_null_cities |
+------------+--------------+-----------------+----------------+-----------------+-----------------+
|          6 |            6 |               6 |              6 |               4 |               5 |
+------------+--------------+-----------------+----------------+-----------------+-----------------+
+-------------+------------+---------------------+------------+
| TABLE_NAME  | INDEX_NAME | key_columns         | NON_UNIQUE |
+-------------+------------+---------------------+------------+
| customers   | email      | email               |          0 |
| customers   | PRIMARY    | id                  |          0 |
| order_items | PRIMARY    | order_id,product_id |          0 |
| order_items | product_id | product_id          |          1 |
+-------------+------------+---------------------+------------+
ERROR 1062 (23000): Duplicate entry '1-1' for key 'order_items.PRIMARY'

What’s happening

Six customers, six distinct ids and six distinct emails: both are unique. city has only 4 distinct values and one NULL, so it can't identify a row.
name is also unique in today's six rows — but that's an accident of the data. A candidate key is a rule about all possible data ("two customers can never share an email"), not an observation about current rows. Two people called Maya will sign up eventually. Data can disprove a key; it can never prove one.
The schema declares the rules: PRIMARY (id) and UNIQUE (email) on customers — two candidate keys, one chosen as primary. On order_items the primary key is composite: (order_id, product_id), "a product appears at most once per order".
Inserting (1, 1) again fails with ERROR 1062 … '1-1' for key 'order_items.PRIMARY': order 1 already has product 1. Neither column is unique on its own (order 1 has two products, product 1 is in two orders); only the pair is.
The extra non-unique index product_id was created automatically for the foreign key to products, because the primary key starts with order_id and can't serve lookups by product alone.

AdvancedWhy surrogate keys win: natural keys change

Suppose email were the primary key and orders referenced it. The schema copies the natural key into every child row:

SQL

CREATE TABLE customers_nat (
  email VARCHAR(100) PRIMARY KEY,
  name  VARCHAR(50) NOT NULL
);
CREATE TABLE orders_nat (
  id             INT PRIMARY KEY,
  customer_email VARCHAR(100) NOT NULL,
  FOREIGN KEY (customer_email) REFERENCES customers_nat(email) ON UPDATE CASCADE
);
INSERT INTO customers_nat SELECT email, name FROM customers;
INSERT INTO orders_nat SELECT o.id, c.email FROM orders o JOIN customers c ON c.id = o.customer_id;
UPDATE customers_nat SET email = 'rohit.varma@example.com' WHERE email = 'rohit@example.com';
SELECT id, customer_email FROM orders_nat ORDER BY id;
UPDATE customers_nat SET email = 'maya@example.com' WHERE email = 'liam@example.com';

Result

+----+-------------------------+
| id | customer_email          |
+----+-------------------------+
|  1 | rohit.varma@example.com |
|  2 | rohit.varma@example.com |
|  3 | maya@example.com        |
|  4 | liam@example.com        |
|  5 | liam@example.com        |
|  6 | sofia@example.com       |
|  7 | noah@example.com        |
|  8 | maya@example.com        |
+----+-------------------------+
ERROR 1062 (23000): Duplicate entry 'maya@example.com' for key 'customers_nat.PRIMARY'

What’s happening

Rohit changes his email. Because the email is the key, every order that references him must change too; ON UPDATE CASCADE rewrites orders 1 and 2. With a million orders, one email change rewrites a million rows (and their index entries), inside one transaction.
Changing a key is also a uniqueness question: Liam can't take maya@example.com, so the update fails with ERROR 1062 — correct, but it shows the key carries business meaning that can collide.
With the surrogate customers.id, an email change touches one row; orders store customer_id = 1, which never changes.
Surrogate keys are also small (4–8 bytes, versus up to 400 bytes for a VARCHAR(100) in utf8mb4), and every secondary index and every child table carries a copy of the primary key.
Natural keys still make sense when they're truly stable and short: ISO country codes (CA, IN), currency codes (USD), and junction tables, whose natural composite key (order_id, product_id) is made of surrogates anyway.

AdvancedEdge case: cascaded rows aren't counted

The client's "rows affected" counts only the rows the statement changed directly:

SQL

UPDATE customers_nat SET email = 'rohit@example.com' WHERE email = 'rohit.varma@example.com';

Result

Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

What’s happening

The update changed one row in customers_nat and, through the cascade, two rows in orders_nat — but the reported count is 1.
If an application uses the affected-rows count to verify a migration, cascades make the number misleading. Check the child tables separately.

AdvancedPractice: keep the natural key UNIQUE

#Relationships: one-to-one, one-to-many and many-to-many

Added

Every relationship between two kinds of thing has a cardinality: how many of B can belong to one A, and vice versa. In a relational database each cardinality has one standard shape:

One-to-many (1:N) — a customer has many orders, an order has one customer. The foreign key goes on the many side: orders.customer_id. This is by far the most common.
One-to-one (1:1) — a customer has at most one profile. The child's foreign key is also its primary key (or has a UNIQUE constraint), so it can't repeat.
Many-to-many (M:N) — an order has many products, a product is in many orders. Neither side can hold a single foreign key, so a junction table (also called a join, link or associative table) holds one row per pair: order_items (order_id, product_id).

A way to remember where the foreign key goes: the foreign key lives with the thing that belongs. An order belongs to a customer, so the order carries the customer's id — like a parcel carrying its recipient's address, not the recipient carrying a list of parcels.

SQL

CREATE TABLE customer_profiles (
  customer_id      INT PRIMARY KEY,
  date_of_birth    DATE NULL,
  marketing_opt_in BOOLEAN NOT NULL DEFAULT FALSE,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);
INSERT INTO customer_profiles VALUES (1, '1990-04-12', TRUE), (2, NULL, FALSE);
INSERT INTO customer_profiles VALUES (1, '1991-01-01', FALSE);
SELECT c.name, COUNT(o.id) AS orders
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name ORDER BY c.id;
SELECT o.id AS order_id, GROUP_CONCAT(p.name ORDER BY p.name SEPARATOR ', ') AS products
FROM orders o JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id
GROUP BY o.id ORDER BY o.id;

Result

ERROR 1062 (23000): Duplicate entry '1' for key 'customer_profiles.PRIMARY'
+-------+--------+
| name  | orders |
+-------+--------+
| Rohit |      2 |
| Maya  |      2 |
| Liam  |      2 |
| Sofia |      1 |
| Noah  |      1 |
| Zara  |      0 |
+-------+--------+
+----------+------------------------------------------------------------------+
| order_id | products                                                         |
+----------+------------------------------------------------------------------+
|        1 | ThinkBook 14, USB-C Charger                                      |
|        2 | Designing Data-Intensive Applications, SQL Performance Explained |
|        3 | Noise-cancelling Headphones, Pixel 10                            |
|        4 | MacBook Air                                                      |
|        5 | Designing Data-Intensive Applications, USB-C Charger             |
|        6 | Noise-cancelling Headphones                                      |
|        7 | SQL Performance Explained, ThinkBook 14                          |
|        8 | USB-C Charger                                                    |
+----------+------------------------------------------------------------------+

What’s happening

One-to-one: customer_profiles.customer_id is both the primary key and the foreign key. A second profile for customer 1 breaks the primary key (ERROR 1062), so "at most one profile per customer" is enforced by the schema. ON DELETE CASCADE fits: a profile means nothing without its customer.
One-to-many: each order has one customer_id, and a customer can appear in any number of orders. The LEFT JOIN + COUNT(o.id) counts per customer: Rohit, Maya and Liam have 2, Zara has 0 (the "many" includes zero).
Many-to-many: order_items has one row per (order, product) pair. Following it from orders gives each order's products; following it the other way gives each product's orders. Order 1 has two products, and the USB-C Charger appears in orders 1, 5 and 8.
The junction table isn't just glue: it holds facts about the pair — quantity and unit_price belong to "this product in this order", not to either side alone. That's the usual sign that a junction table deserves to be a real entity (here: an order line).

AdvancedBoth directions of a many-to-many

The same junction table, read from the products side:

SQL

SELECT p.name, COUNT(oi.order_id) AS times_ordered
FROM products p LEFT JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.name ORDER BY times_ordered DESC, p.name;

Result

+---------------------------------------+---------------+
| name                                  | times_ordered |
+---------------------------------------+---------------+
| USB-C Charger                         |             3 |
| Designing Data-Intensive Applications |             2 |
| Noise-cancelling Headphones           |             2 |
| SQL Performance Explained             |             2 |
| ThinkBook 14                          |             2 |
| MacBook Air                           |             1 |
| Pixel 10                              |             1 |
| iPhone 17                             |             0 |
+---------------------------------------+---------------+

What’s happening

Starting from products and going through order_items answers "in how many orders does each product appear?".
The LEFT JOIN keeps the iPhone 17, which has never been ordered — zero is a valid "many".
The junction table needs an index for each direction it's searched in: the primary key (order_id, product_id) serves "products of an order", and the separate index on product_id serves this query. A junction table with only one of the two is a common performance bug.

AdvancedA simple junction table: product tags

When the pair has no data of its own, the junction table is just the two keys:

SQL

CREATE TABLE product_tags (
  product_id INT NOT NULL,
  tag        VARCHAR(30) NOT NULL,
  PRIMARY KEY (product_id, tag),
  INDEX idx_tag (tag),
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);
INSERT INTO product_tags VALUES (1,'laptop'),(2,'laptop'),(1,'work'),(5,'work'),(6,'work'),(3,'phone'),(4,'phone'),(8,'travel'),(7,'travel'),(3,'travel');
SELECT tag, GROUP_CONCAT(product_id ORDER BY product_id) AS product_ids FROM product_tags GROUP BY tag ORDER BY tag;

Result

+--------+-------------+
| tag    | product_ids |
+--------+-------------+
| laptop | 1,2         |
| phone  | 3,4         |
| travel | 3,7,8       |
| work   | 1,5,6       |
+--------+-------------+

What’s happening

The composite primary key (product_id, tag) stops the same tag being added twice to one product and serves "tags of a product".
idx_tag serves the other direction, "products with this tag".
Here tag is a plain string rather than a foreign key to a tags table — fine when tags have no attributes. If tags get a description, an owner or a colour, promote them to their own table and store tag_id.
The alternative many people reach for — a comma-separated tags column on products — breaks first normal form; see First, second and third normal form for why.

AdvancedSelf-referencing relationships

A relationship can point back at the same table: an employee's manager is another employee, a category's parent is another category.

SQL

SELECT m.name AS manager, GROUP_CONCAT(e.name ORDER BY e.name SEPARATOR ', ') AS reports
FROM employees e JOIN employees m ON m.id = e.manager_id
GROUP BY m.id, m.name ORDER BY m.id;

Result

+---------+--------------------------------+
| manager | reports                        |
+---------+--------------------------------+
| Asha    | Ben, Chloe, Elena, Hiro, Jonas |
| Ben     | Dev                            |
| Elena   | Farid, Grace                   |
| Hiro    | Isla                           |
+---------+--------------------------------+

What’s happening

employees.manager_id is a foreign key to employees.id: a one-to-many from the table to itself (one manager, many reports).
Joining the table to itself with two aliases, e (the report) and m (the manager), turns each pointer into a pair of names — see Self joins.
Asha has manager_id = NULL: she's the root of the tree, which is why the column is nullable.
Walking more than one level (all of Asha's indirect reports) needs a recursive query — see Recursive CTEs.

AdvancedPitfall: needless one-to-one splits

#Entity-relationship diagrams

Added

An entity-relationship diagram (ERD) is the map of a schema: boxes for entities (tables) with their attributes (columns), and lines for relationships, with symbols at each end showing cardinality. You draw one before building a schema, to agree on what the data means, and you generate one from an existing schema to understand it.

The most common notation is crow's foot. Each end of a line answers two questions — at most how many? (the symbol nearest the box) and at least how many? (the symbol further out):

Symbol at the end	Reads as
`\|\|` two bars	exactly one (one and only one)
`o\|` circle + bar	zero or one
`\|<` bar + crow's foot	one or many
`o<` circle + crow's foot	zero or many

Here is the site's sample schema drawn that way:

The sample schema in crow's foot notation. Read each line from one box to the other: "a customer places zero or many orders; an order is placed by exactly one customer".

The lines in a diagram are just the foreign keys, so you can read them straight out of the database. This query lists every relationship in the schema, with the information you need to draw each end:

SQL

SELECT k.table_name AS child, k.column_name AS fk_column,
       k.referenced_table_name AS parent, k.referenced_column_name AS parent_column,
       c.is_nullable AS optional
FROM information_schema.key_column_usage k
JOIN information_schema.columns c
  ON c.table_schema = k.table_schema AND c.table_name = k.table_name AND c.column_name = k.column_name
WHERE k.table_schema = 'ch07' AND k.referenced_table_name IS NOT NULL
ORDER BY parent, child;

Result

+-------------+---------------+-------------+---------------+----------+
| child       | fk_column     | parent      | parent_column | optional |
+-------------+---------------+-------------+---------------+----------+
| categories  | parent_id     | categories  | id            | YES      |
| products    | category_id   | categories  | id            | NO       |
| orders      | customer_id   | customers   | id            | NO       |
| employees   | department_id | departments | id            | YES      |
| employees   | manager_id    | employees   | id            | YES      |
| order_items | order_id      | orders      | id            | NO       |
| order_items | product_id    | products    | id            | NO       |
+-------------+---------------+-------------+---------------+----------+

What’s happening

information_schema.key_column_usage lists every key column; rows with a referenced_table_name are foreign keys. Each row is one line in the diagram, drawn from parent (the "one" end) to child (the "many" end).
Joining information_schema.columns adds is_nullable. A NOT NULL foreign key means every child must have a parent, so the parent end is exactly one (||): every order has exactly one customer.
A nullable foreign key makes the parent end zero or one (o|): an employee may have no department (Jonas) and the root employee has no manager, so those lines start with a circle.
Two rows point a table at itself (categories.parent_id, employees.manager_id) — the loops in the diagram.
What the schema can't tell you is the minimum on the "many" side. Nothing stops an order from having zero items, so the database says o<. The diagram above draws orders → order_items as |< (one or many) because that's the business rule — an order must have at least one line — which the application (or a trigger) has to enforce. Diagrams record intent; constraints record what's enforced. Keep the two in sync.

AdvancedOther notations and tools

Chen notation (the textbook original, 1976) draws entities as rectangles, relationships as diamonds and attributes as ovals, with 1, N, M on the lines. You'll see it in university courses and some interviews; it's too bulky for real schemas.
UML class diagrams show the same thing with multiplicities like 1, 0..1, 1..*, 0..* — handy if your team already uses UML.
Diagrams as code: many teams keep the ERD next to the schema as text, so it's reviewed in pull requests — for example Mermaid's erDiagram (rendered by GitHub and many wikis), where CUSTOMERS ||--o{ ORDERS : places means exactly the "places" line above.
Generated diagrams: MySQL Workbench (Database → Reverse Engineer), DBeaver and DataGrip draw an ERD from a live schema using exactly the foreign keys the query above lists. A schema without declared foreign keys produces a diagram with no lines.

None of these tools were run here; the diagram above was drawn by hand from the query result.

AdvancedPractice: read relationships out loud

#Why normalise: update, insert and delete anomalies

Added

Normalisation is the process of organising columns into tables so that each fact is stored exactly once. When a fact is stored in many rows, every copy has to be kept in sync, and sooner or later they disagree. The classic symptoms are called anomalies:

Update anomaly — changing one fact means changing many rows; miss one and the data contradicts itself.
Insert anomaly — you can't record one fact without inventing another (a customer can't exist until they've ordered something).
Delete anomaly — deleting one fact destroys an unrelated one (cancelling the last order deletes the customer).

The mental model is a spreadsheet that grew too wide: one row per order line, with the customer's name, email and city and the product's name and price typed into every row. It's perfect for reading one order. It's a trap for everything else.

Here is exactly that table, built from the sample data:

SQL

CREATE TABLE orders_flat (
  order_id       INT,
  product_id     INT,
  customer_name  VARCHAR(50),
  customer_email VARCHAR(100),
  customer_city  VARCHAR(50),
  product_name   VARCHAR(80),
  list_price     DECIMAL(10,2),
  quantity       INT,
  PRIMARY KEY (order_id, product_id)
);
INSERT INTO orders_flat
SELECT o.id, p.id, c.name, c.email, c.city, p.name, p.price, oi.quantity
FROM orders o
JOIN customers c    ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id;
SELECT order_id, customer_name, customer_email, customer_city, product_name, list_price, quantity
FROM orders_flat ORDER BY order_id, product_id;

Result

+----------+---------------+-------------------+---------------+---------------------------------------+------------+----------+
| order_id | customer_name | customer_email    | customer_city | product_name                          | list_price | quantity |
+----------+---------------+-------------------+---------------+---------------------------------------+------------+----------+
|        1 | Rohit         | rohit@example.com | Toronto       | ThinkBook 14                          |     899.00 |        1 |
|        1 | Rohit         | rohit@example.com | Toronto       | USB-C Charger                         |      29.00 |        2 |
|        2 | Rohit         | rohit@example.com | Toronto       | SQL Performance Explained             |      39.00 |        1 |
|        2 | Rohit         | rohit@example.com | Toronto       | Designing Data-Intensive Applications |      49.00 |        1 |
|        3 | Maya          | maya@example.com  | Mumbai        | Pixel 10                              |     799.00 |        1 |
|        3 | Maya          | maya@example.com  | Mumbai        | Noise-cancelling Headphones           |     249.00 |        1 |
|        4 | Liam          | liam@example.com  | London        | MacBook Air                           |    1299.00 |        1 |
|        5 | Liam          | liam@example.com  | London        | Designing Data-Intensive Applications |      49.00 |        2 |
|        5 | Liam          | liam@example.com  | London        | USB-C Charger                         |      29.00 |        1 |
|        6 | Sofia         | sofia@example.com | NULL          | Noise-cancelling Headphones           |     249.00 |        1 |
|        7 | Noah          | noah@example.com  | Toronto       | ThinkBook 14                          |     899.00 |        1 |
|        7 | Noah          | noah@example.com  | Toronto       | SQL Performance Explained             |      39.00 |        3 |
|        8 | Maya          | maya@example.com  | Mumbai        | USB-C Charger                         |      29.00 |        4 |
+----------+---------------+-------------------+---------------+---------------------------------------+------------+----------+

What’s happening

The join copies customer and product facts into every order line: 13 rows, one per order_items row.
Rohit's name, email and city appear four times (two orders × two lines). The ThinkBook's name and price appear twice, the USB-C Charger's three times.
Each repeated fact is a promise that every copy will stay identical. Nothing in the table enforces that promise: the primary key (order_id, product_id) only stops duplicate order lines.
Reading is easy — one table, no joins. The next three cards show what happens when you write.

AdvancedUpdate anomaly: Rohit moves to Vancouver

The application updates the city on the order it's looking at:

SQL

UPDATE orders_flat SET customer_city = 'Vancouver' WHERE order_id = 1;
SELECT customer_name, GROUP_CONCAT(DISTINCT customer_city) AS cities, COUNT(*) AS rows_for_customer
FROM orders_flat WHERE customer_name = 'Rohit' GROUP BY customer_name;

Result

+---------------+-------------------+-------------------+
| customer_name | cities            | rows_for_customer |
+---------------+-------------------+-------------------+
| Rohit         | Toronto,Vancouver |                 4 |
+---------------+-------------------+-------------------+

What’s happening

The UPDATE changed the two rows of order 1 and left the two rows of order 2 alone.
Rohit now lives in both Toronto and Vancouver. Which is true? The database can't say — and a report "customers by city" counts him twice.
The fix isn't "remember to update all rows": in a normalised schema the city is stored once, in customers, and the question can't arise.

AdvancedInsert anomaly: a customer with no orders

Zara signed up but hasn't ordered anything. There's nowhere to put her:

SQL

INSERT INTO orders_flat (order_id, product_id, customer_name, customer_email, customer_city)
VALUES (NULL, NULL, 'Zara', 'zara@example.com', 'Dubai');

Result

ERROR 1048 (23000): Column 'order_id' cannot be null

What’s happening

The table's key is (order_id, product_id) — its rows are order lines. A customer without an order line has no key, so the row is rejected.
The same goes for a product nobody has ordered yet (the iPhone 17): it can't be listed until someone buys it.
Workarounds — a fake order 0, a dummy product — make every query and report filter them out. The real fix is a customers table and a products table whose rows exist on their own.

AdvancedDelete anomaly: cancelling the last order

Two deletes that look like routine clean-up:

SQL

DELETE FROM orders_flat WHERE order_id = 4;
SELECT COUNT(*) AS rows_mentioning_macbook FROM orders_flat WHERE product_name = 'MacBook Air';
SELECT COUNT(*) AS rows_mentioning_sofia_before FROM orders_flat WHERE customer_name = 'Sofia';
DELETE FROM orders_flat WHERE order_id = 6;
SELECT COUNT(*) AS rows_mentioning_sofia_after FROM orders_flat WHERE customer_name = 'Sofia';

Result

+-------------------------+
| rows_mentioning_macbook |
+-------------------------+
|                       0 |
+-------------------------+
+------------------------------+
| rows_mentioning_sofia_before |
+------------------------------+
|                            1 |
+------------------------------+
+-----------------------------+
| rows_mentioning_sofia_after |
+-----------------------------+
|                           0 |
+-----------------------------+

What’s happening

Order 4 (Liam's cancelled MacBook) was the only order containing the MacBook Air. Deleting it erased the only record that the product exists and costs 1299.00.
Order 6 was Sofia's only order. Deleting it erased Sofia: her name and email are gone from the database.
You wanted to remove an order; you lost a product and a customer. That's the delete anomaly: unrelated facts were stored in the same row, so they died together.

AdvancedNote: normalisation optimises writes

#First, second and third normal form

Added

The normal forms are a checklist for "is each fact stored once?". Each one builds on the previous one, and each is defined in terms of functional dependencies: A → B ("A determines B") means that whenever two rows have the same A, they must have the same B. customer_id → customer_city: one customer, one city.

1NF — atomic values, no repeating groups. Every column holds one value, not a list; every row is identifiable by a key.
2NF — no partial dependencies. Every non-key column depends on the whole key, not part of a composite key. (Only matters when the key is composite.)
3NF — no transitive dependencies. Non-key columns depend on the key directly, not through another non-key column.

The classic one-line summary (William Kent's): every non-key column must depend on "the key, the whole key, and nothing but the key".

We'll take the order data through all three steps with real tables. Step zero is the unnormalised version: one row per order, with the items packed into a text column.

SQL

CREATE TABLE orders_unf AS
SELECT o.id AS order_id, c.name AS customer, c.city,
       GROUP_CONCAT(CONCAT(p.name, ' x', oi.quantity) ORDER BY p.id SEPARATOR '; ') AS items
FROM orders o
JOIN customers c    ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
GROUP BY o.id, c.name, c.city;
SELECT * FROM orders_unf WHERE order_id <= 5 ORDER BY order_id;
SELECT order_id, items FROM orders_unf WHERE items LIKE '%Charger%';

Result

+----------+----------+---------+------------------------------------------------------------------------+
| order_id | customer | city    | items                                                                  |
+----------+----------+---------+------------------------------------------------------------------------+
|        1 | Rohit    | Toronto | ThinkBook 14 x1; USB-C Charger x2                                      |
|        2 | Rohit    | Toronto | SQL Performance Explained x1; Designing Data-Intensive Applications x1 |
|        3 | Maya     | Mumbai  | Pixel 10 x1; Noise-cancelling Headphones x1                            |
|        4 | Liam     | London  | MacBook Air x1                                                         |
|        5 | Liam     | London  | Designing Data-Intensive Applications x2; USB-C Charger x1             |
+----------+----------+---------+------------------------------------------------------------------------+
+----------+------------------------------------------------------------+
| order_id | items                                                      |
+----------+------------------------------------------------------------+
|        1 | ThinkBook 14 x1; USB-C Charger x2                          |
|        5 | Designing Data-Intensive Applications x2; USB-C Charger x1 |
|        8 | USB-C Charger x4                                           |
+----------+------------------------------------------------------------+

What’s happening

items is a repeating group squeezed into one column: a list of products and quantities. The table is not even in 1NF.
Finding orders with a charger needs LIKE '%Charger%' — a full scan with no index, and it would also match a future "Charger Cable" or "Supercharger".
"How many chargers did we sell?" means parsing x2, x1, x4 out of text. Changing a quantity means rewriting a string. A product name containing ; breaks the format.
The database can't enforce anything inside the list: no foreign key to products, no CHECK on quantity, no uniqueness of a product within an order.
That's what "atomic" means in practice: a value the database never has to look inside to answer your questions.

AdvancedStep 1: first normal form, one row per order line

Split the list into rows. Each row is now one product in one order, identified by the composite key (order_id, product_id):

SQL

CREATE TABLE orders_1nf (
  order_id      INT,
  product_id    INT,
  customer_id   INT,
  customer_name VARCHAR(50),
  customer_city VARCHAR(50),
  product_name  VARCHAR(80),
  category_id   INT,
  category_name VARCHAR(50),
  quantity      INT,
  unit_price    DECIMAL(10,2),
  PRIMARY KEY (order_id, product_id)
);
INSERT INTO orders_1nf
SELECT o.id, p.id, c.id, c.name, c.city, p.name, cat.id, cat.name, oi.quantity, oi.unit_price
FROM orders o
JOIN customers c    ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id;
SELECT order_id, product_id, customer_name, product_name, category_name, quantity
FROM orders_1nf WHERE order_id IN (1, 5) ORDER BY order_id, product_id;
SELECT SUM(quantity) AS chargers_sold FROM orders_1nf WHERE product_name = 'USB-C Charger';

Result

+----------+------------+---------------+---------------------------------------+---------------+----------+
| order_id | product_id | customer_name | product_name                          | category_name | quantity |
+----------+------------+---------------+---------------------------------------+---------------+----------+
|        1 |          1 | Rohit         | ThinkBook 14                          | Laptops       |        1 |
|        1 |          7 | Rohit         | USB-C Charger                         | Accessories   |        2 |
|        5 |          6 | Liam          | Designing Data-Intensive Applications | Books         |        2 |
|        5 |          7 | Liam          | USB-C Charger                         | Accessories   |        1 |
+----------+------------+---------------+---------------------------------------+---------------+----------+
+---------------+
| chargers_sold |
+---------------+
|             7 |
+---------------+

What’s happening

Every column now holds one value, and each row has a key: 1NF. The 13 rows match the 13 order lines.
"Chargers sold" is now an ordinary SUM(quantity): 2 + 1 + 4 = 7. Quantities are numbers the database can check and add.
But this is the orders_flat table from the previous topic with a few more columns — all three anomalies are still here. 1NF fixed the shape; it didn't remove the duplication.

AdvancedStep 2: second normal form, remove partial dependencies

The key is (order_id, product_id). Which columns depend on only part of it? Customer columns depend on order_id alone (one order, one customer); product and category columns on product_id alone. Only quantity and unit_price need both. You can check a suspected dependency against the data — a group with more than one value disproves it:

SQL

SELECT 'order_id -> customer_name' AS dependency,
       COUNT(*) AS violating_groups
FROM (SELECT order_id FROM orders_1nf GROUP BY order_id HAVING COUNT(DISTINCT customer_name) > 1) x
UNION ALL
SELECT 'product_id -> product_name',
       COUNT(*) FROM (SELECT product_id FROM orders_1nf GROUP BY product_id HAVING COUNT(DISTINCT product_name) > 1) x
UNION ALL
SELECT 'product_id -> unit_price',
       COUNT(*) FROM (SELECT product_id FROM orders_1nf GROUP BY product_id HAVING COUNT(DISTINCT unit_price) > 1) x;
CREATE TABLE orders_2nf AS
  SELECT DISTINCT order_id, customer_id, customer_name, customer_city FROM orders_1nf;
CREATE TABLE products_2nf AS
  SELECT DISTINCT product_id, product_name, category_id, category_name FROM orders_1nf;
CREATE TABLE order_lines_2nf AS
  SELECT order_id, product_id, quantity, unit_price FROM orders_1nf;
SELECT (SELECT COUNT(*) FROM orders_1nf) AS rows_1nf, (SELECT COUNT(*) FROM orders_2nf) AS orders_rows,
       (SELECT COUNT(*) FROM products_2nf) AS product_rows, (SELECT COUNT(*) FROM order_lines_2nf) AS order_line_rows;
SELECT * FROM orders_2nf ORDER BY order_id;
SELECT * FROM products_2nf ORDER BY product_id;

Result

+----------------------------+------------------+
| dependency                 | violating_groups |
+----------------------------+------------------+
| order_id -> customer_name  |                0 |
| product_id -> product_name |                0 |
| product_id -> unit_price   |                0 |
+----------------------------+------------------+
+----------+-------------+--------------+-----------------+
| rows_1nf | orders_rows | product_rows | order_line_rows |
+----------+-------------+--------------+-----------------+
|       13 |           8 |            7 |              13 |
+----------+-------------+--------------+-----------------+
+----------+-------------+---------------+---------------+
| order_id | customer_id | customer_name | customer_city |
+----------+-------------+---------------+---------------+
|        1 |           1 | Rohit         | Toronto       |
|        2 |           1 | Rohit         | Toronto       |
|        3 |           2 | Maya          | Mumbai        |
|        4 |           3 | Liam          | London        |
|        5 |           3 | Liam          | London        |
|        6 |           4 | Sofia         | NULL          |
|        7 |           5 | Noah          | Toronto       |
|        8 |           2 | Maya          | Mumbai        |
+----------+-------------+---------------+---------------+
+------------+---------------------------------------+-------------+---------------+
| product_id | product_name                          | category_id | category_name |
+------------+---------------------------------------+-------------+---------------+
|          1 | ThinkBook 14                          |           2 | Laptops       |
|          2 | MacBook Air                           |           2 | Laptops       |
|          3 | Pixel 10                              |           3 | Phones        |
|          5 | SQL Performance Explained             |           4 | Books         |
|          6 | Designing Data-Intensive Applications |           4 | Books         |
|          7 | USB-C Charger                         |           5 | Accessories   |
|          8 | Noise-cancelling Headphones           |           5 | Accessories   |
+------------+---------------------------------------+-------------+---------------+

What’s happening

The data agrees with order_id → customer_name and product_id → product_name: no group has two values.
It also agrees with product_id → unit_price — every sale of a product so far was at the same price. But that's not a rule: unit_price is the price at the time of the order, and it will differ once a price changes. Data can only disprove a dependency; deciding which ones are real is a business question. So unit_price stays on the order line, next to quantity.
The decomposition puts each group of columns with the part of the key it depends on: orders_2nf (key order_id), products_2nf (key product_id) and order_lines_2nf (key order_id, product_id, with quantity and unit_price).
Row counts: 13 lines, 8 orders, 7 products. Each product name is now stored once — the update anomaly for product names is gone.
Notice the iPhone 17 is missing from products_2nf: we built it from order lines, and nobody ordered one. In a real design products is filled on its own — the insert anomaly disappears once products are their own table.

AdvancedStep 3: third normal form, remove transitive dependencies

orders_2nf still repeats Rohit's name and city on orders 1 and 2. The chain is order_id → customer_id → customer_city: the city depends on the key only through another non-key column. Same for product_id → category_id → category_name. Split each chain at the middle:

SQL

CREATE TABLE customers_3nf  AS SELECT DISTINCT customer_id, customer_name, customer_city FROM orders_2nf;
CREATE TABLE orders_3nf     AS SELECT order_id, customer_id FROM orders_2nf;
CREATE TABLE categories_3nf AS SELECT DISTINCT category_id, category_name FROM products_2nf;
CREATE TABLE products_3nf   AS SELECT product_id, product_name, category_id FROM products_2nf;
SELECT * FROM customers_3nf ORDER BY customer_id;
SELECT * FROM categories_3nf ORDER BY category_id;
UPDATE customers_3nf SET customer_city = 'Vancouver' WHERE customer_id = 1;
SELECT o.order_id, c.customer_name, c.customer_city
FROM orders_3nf o JOIN customers_3nf c USING (customer_id) WHERE c.customer_id = 1 ORDER BY o.order_id;

Result

+-------------+---------------+---------------+
| customer_id | customer_name | customer_city |
+-------------+---------------+---------------+
|           1 | Rohit         | Toronto       |
|           2 | Maya          | Mumbai        |
|           3 | Liam          | London        |
|           4 | Sofia         | NULL          |
|           5 | Noah          | Toronto       |
+-------------+---------------+---------------+
+-------------+---------------+
| category_id | category_name |
+-------------+---------------+
|           2 | Laptops       |
|           3 | Phones        |
|           4 | Books         |
|           5 | Accessories   |
+-------------+---------------+
+----------+---------------+---------------+
| order_id | customer_name | customer_city |
+----------+---------------+---------------+
|        1 | Rohit         | Vancouver     |
|        2 | Rohit         | Vancouver     |
+----------+---------------+---------------+

What’s happening

customers_3nf holds each customer once, orders_3nf keeps only customer_id as a pointer. categories_3nf and products_3nf do the same for categories.
Rohit's move to Vancouver is now one UPDATE of one row — and both of his orders see it through the join. The update anomaly from the previous topic can't happen.
Compare the result with the site's sample schema: customers, orders, order_items, products, categories. Normalising the flat table step by step rebuilt it. That's the point of 3NF in practice — it's what a sensible schema already looks like.
3NF doesn't mean "no repeated values". Toronto still appears twice in customers_3nf (Rohit and Noah), and that's fine: they're two different facts that happen to share a value. Only repeated facts are a problem.

AdvancedProof: the decomposition is lossless

Splitting a table is only safe if joining the pieces gives back exactly the original rows — no row lost, none invented. EXCEPT (MySQL 8.0.31+) checks both directions:

SQL

UPDATE customers_3nf SET customer_city = 'Toronto' WHERE customer_id = 1;
WITH original AS (
  SELECT order_id, product_id, customer_id, customer_name, customer_city, product_name, category_id, category_name, quantity, unit_price
  FROM orders_1nf
), rejoined AS (
  SELECT o.order_id, l.product_id, c.customer_id, c.customer_name, c.customer_city, p.product_name, k.category_id, k.category_name, l.quantity, l.unit_price
  FROM order_lines_2nf l
  JOIN orders_3nf o     ON o.order_id = l.order_id
  JOIN customers_3nf c  ON c.customer_id = o.customer_id
  JOIN products_3nf p   ON p.product_id = l.product_id
  JOIN categories_3nf k ON k.category_id = p.category_id
)
SELECT (SELECT COUNT(*) FROM (SELECT * FROM original EXCEPT SELECT * FROM rejoined) a) AS lost,
       (SELECT COUNT(*) FROM (SELECT * FROM rejoined EXCEPT SELECT * FROM original) b) AS invented;

Result

+------+----------+
| lost | invented |
+------+----------+
|    0 |        0 |
+------+----------+

What’s happening

Rohit's city is set back to Toronto first, so both sides hold the same facts. original is the 1NF table; rejoined joins the five 3NF tables back together.
original EXCEPT rejoined would list rows the split lost; rejoined EXCEPT original would list rows the join invented. Both are 0.
It's lossless because every split was along a real dependency, and each join is on the column that determines the others (customer_id, product_id, category_id) — a key of one side. The next card shows what happens when it isn't.

AdvancedCounter-example: a lossy split

Split on a column that isn't a key of either piece — here customer_city — and the join invents rows:

SQL

CREATE TABLE lossy_a AS SELECT DISTINCT order_id, customer_city FROM orders_2nf;
CREATE TABLE lossy_b AS SELECT DISTINCT customer_city, customer_name FROM orders_2nf;
SELECT a.order_id, b.customer_name, a.customer_city
FROM lossy_a a JOIN lossy_b b ON b.customer_city = a.customer_city
WHERE a.customer_city = 'Toronto' ORDER BY a.order_id, b.customer_name;

Result

+----------+---------------+---------------+
| order_id | customer_name | customer_city |
+----------+---------------+---------------+
|        1 | Noah          | Toronto       |
|        1 | Rohit         | Toronto       |
|        2 | Noah          | Toronto       |
|        2 | Rohit         | Toronto       |
|        7 | Noah          | Toronto       |
|        7 | Rohit         | Toronto       |
+----------+---------------+---------------+

What’s happening

lossy_a says "order 1 went to Toronto", lossy_b says "Rohit and Noah live in Toronto". Neither table knows who placed order 1.
Joining on the city pairs every Toronto order with every Toronto customer: 3 orders × 2 customers = 6 rows, of which 3 are false ("Noah placed order 1").
Such invented rows are called spurious tuples. The rule that prevents them: the column you split on must be a key of at least one of the two pieces.

Which normal form does this break?

SQL

CREATE TABLE enrolments (
  student_id   INT,
  course_id    INT,
  course_title VARCHAR(100),
  grade        CHAR(2),
  PRIMARY KEY (student_id, course_id)
);

Show answer

2NF. course_title depends only on course_id, which is part of the composite key (student_id, course_id) — a partial dependency.

What’s happening

Check 1NF first: every column holds a single value and there's a key, so it's in 1NF.
For each non-key column, ask "do I need the whole key to know it?". grade — yes: a grade belongs to one student in one course.
course_title — no: course_id alone determines it. Every student in course 42 repeats the same title, so renaming a course means updating every enrolment (an update anomaly), and a course with no students has nowhere to store its title (an insert anomaly).
Fix: move course_title to courses (course_id PK, course_title) and keep enrolments (student_id, course_id, grade).

And this one?

SQL

CREATE TABLE employees_copy (
  id              INT PRIMARY KEY,
  name            VARCHAR(50),
  department_id   INT,
  department_name VARCHAR(50)
);

Show answer

3NF. The key is the single column id, so 2NF is automatic, but department_name depends on department_id, a non-key column: id → department_id → department_name is a transitive dependency.

What’s happening

A table whose primary key is one column can't have partial dependencies, so it's in 2NF.
department_name doesn't depend on the employee directly; it depends on the department, and gets to the employee through department_id.
Renaming "Sales" to "Revenue" would mean updating every Sales employee, and a department with no employees (Research, in the sample data) couldn't be stored. That's exactly why the sample schema has a separate departments table.

#BCNF and beyond

Added

3NF has a loophole. It only restricts dependencies that determine non-key columns; a dependency that determines part of a candidate key slips through. Boyce–Codd normal form (BCNF) closes it with a stricter rule: for every functional dependency X → Y, X must be a super key. In plain words: if some column determines another, it must identify the whole row.

The loophole only matters when a table has overlapping composite candidate keys, which is rare — that's why 3NF and BCNF coincide for most real tables. The classic example is tutoring:

each student takes each course with exactly one instructor: (student, course) → instructor;
each instructor teaches exactly one course: instructor → course.

Candidate keys are (student, course) and (student, instructor). Every column is part of some candidate key, so 3NF is satisfied. But instructor → course, and instructor alone isn't a key — a BCNF violation.

SQL

CREATE TABLE tutoring (
  student    VARCHAR(20) NOT NULL,
  course     VARCHAR(20) NOT NULL,
  instructor VARCHAR(20) NOT NULL,
  PRIMARY KEY (student, course)
);
INSERT INTO tutoring VALUES
  ('Rohit', 'SQL',     'Ben'),
  ('Rohit', 'MongoDB', 'Elena'),
  ('Maya',  'SQL',     'Chloe'),
  ('Liam',  'SQL',     'Ben'),
  ('Liam',  'Redis',   'Hiro');
UPDATE tutoring SET course = 'PostgreSQL' WHERE instructor = 'Ben' AND student = 'Rohit';
SELECT * FROM tutoring WHERE instructor = 'Ben' ORDER BY student;

Result

+---------+------------+------------+
| student | course     | instructor |
+---------+------------+------------+
| Liam    | SQL        | Ben        |
| Rohit   | PostgreSQL | Ben        |
+---------+------------+------------+

What’s happening

The fact "Ben teaches SQL" is stored once per student Ben tutors — twice here (Rohit, Liam). That's the duplication BCNF is about.
Ben switches to teaching PostgreSQL, and the update only touches Rohit's row. Now the table says Ben teaches both SQL and PostgreSQL — the same update anomaly as before, even though the table is in 3NF.
There's also an insert anomaly: a new instructor (say Grace, teaching Kafka) can't be recorded until a student enrols, because student is part of every key.
The root cause is instructor → course where instructor isn't a key. BCNF says: give that dependency its own table.

AdvancedDecomposing to BCNF

Split along instructor → course: one table for "who teaches what", one for "who studies with whom":

SQL

UPDATE tutoring SET course = 'SQL' WHERE instructor = 'Ben';
CREATE TABLE instructors (
  instructor VARCHAR(20) PRIMARY KEY,
  course     VARCHAR(20) NOT NULL
);
CREATE TABLE enrolments (
  student    VARCHAR(20) NOT NULL,
  instructor VARCHAR(20) NOT NULL,
  PRIMARY KEY (student, instructor),
  FOREIGN KEY (instructor) REFERENCES instructors(instructor)
);
INSERT INTO instructors SELECT DISTINCT instructor, course FROM tutoring;
INSERT INTO enrolments  SELECT student, instructor FROM tutoring;
INSERT INTO instructors VALUES ('Grace', 'Kafka');
UPDATE instructors SET course = 'PostgreSQL' WHERE instructor = 'Ben';
SELECT e.student, i.course, i.instructor
FROM enrolments e JOIN instructors i USING (instructor)
ORDER BY e.student, i.course;
SELECT * FROM instructors ORDER BY instructor;

Result

+---------+------------+------------+
| student | course     | instructor |
+---------+------------+------------+
| Liam    | PostgreSQL | Ben        |
| Liam    | Redis      | Hiro       |
| Maya    | SQL        | Chloe      |
| Rohit   | MongoDB    | Elena      |
| Rohit   | PostgreSQL | Ben        |
+---------+------------+------------+
+------------+------------+
| instructor | course     |
+------------+------------+
| Ben        | PostgreSQL |
| Chloe      | SQL        |
| Elena      | MongoDB    |
| Grace      | Kafka      |
| Hiro       | Redis      |
+------------+------------+

What’s happening

First the inconsistent row is repaired (Ben back to SQL everywhere), then the data is split into instructors (key instructor) and enrolments (key student, instructor). In both tables every determinant is a key: BCNF.
Grace can now be added with no students — the insert anomaly is gone.
Ben's move to PostgreSQL is a one-row update, and both of his students see it through the join.
The split is lossless: instructor is the key of instructors, so joining on it can't invent rows.

AdvancedThe price of BCNF: a rule no key can enforce

The original table enforced "one instructor per student per course" with its primary key (student, course). After the split, course and student live in different tables, so no single key can enforce it:

SQL

UPDATE instructors SET course = 'SQL' WHERE instructor = 'Ben';
INSERT INTO enrolments VALUES ('Maya', 'Ben');
SELECT e.student, i.course, GROUP_CONCAT(i.instructor ORDER BY i.instructor) AS instructors
FROM enrolments e JOIN instructors i USING (instructor)
GROUP BY e.student, i.course HAVING COUNT(*) > 1;

Result

+---------+--------+-------------+
| student | course | instructors |
+---------+--------+-------------+
| Maya    | SQL    | Ben,Chloe   |
+---------+--------+-------------+

What’s happening

Ben teaches SQL again, and Maya already studies SQL with Chloe.
Enrolling Maya with Ben is accepted: (Maya, Ben) is a new pair, the foreign key is satisfied — nothing knows Ben and Chloe teach the same course.
Now Maya has two SQL instructors, which the business rule forbids. The decomposition lost the dependency (student, course) → instructor.
This is the textbook trade-off: 3NF can always be reached while preserving every dependency; BCNF sometimes can't. In practice you either accept BCNF and enforce the lost rule elsewhere (a trigger, the application, or a redundant course column in enrolments with a composite foreign key and UNIQUE (student, course)), or you stay in 3NF for that table.

AdvancedFourth normal form: independent multi-valued facts

4NF deals with multi-valued dependencies: two independent lists about the same thing stored in one table. An employee has a set of skills and, independently, a set of languages:

SQL

CREATE TABLE employee_skills_languages (
  employee VARCHAR(20) NOT NULL,
  skill    VARCHAR(20) NOT NULL,
  language VARCHAR(20) NOT NULL,
  PRIMARY KEY (employee, skill, language)
);
INSERT INTO employee_skills_languages VALUES
  ('Asha', 'SQL', 'English'), ('Asha', 'SQL', 'Hindi'),
  ('Asha', 'Go',  'English'), ('Asha', 'Go',  'Hindi');
INSERT INTO employee_skills_languages VALUES ('Asha', 'SQL', 'French');
SELECT skill, GROUP_CONCAT(language ORDER BY language) AS languages
FROM employee_skills_languages WHERE employee = 'Asha' GROUP BY skill ORDER BY skill;
CREATE TABLE employee_skills    (employee VARCHAR(20), skill    VARCHAR(20), PRIMARY KEY (employee, skill));
CREATE TABLE employee_languages (employee VARCHAR(20), language VARCHAR(20), PRIMARY KEY (employee, language));
INSERT INTO employee_skills    SELECT DISTINCT employee, skill    FROM employee_skills_languages;
INSERT INTO employee_languages SELECT DISTINCT employee, language FROM employee_skills_languages;
SELECT (SELECT COUNT(*) FROM employee_skills) AS skill_rows, (SELECT COUNT(*) FROM employee_languages) AS language_rows,
       (SELECT COUNT(*) FROM employee_skills_languages) AS combined_rows;

Result

+-------+----------------------+
| skill | languages            |
+-------+----------------------+
| Go    | English,Hindi        |
| SQL   | English,French,Hindi |
+-------+----------------------+
+------------+---------------+---------------+
| skill_rows | language_rows | combined_rows |
+------------+---------------+---------------+
|          2 |             3 |             5 |
+------------+---------------+---------------+

What’s happening

The table has no non-key columns at all — the key is all three — so it's in BCNF. Yet it's redundant: to say "Asha speaks English and Hindi and knows SQL and Go" it needs every combination, 2 × 2 = 4 rows.
Asha learns French. Adding only ('Asha', 'SQL', 'French') makes the table claim she speaks French only when using SQL — nonsense, since languages have nothing to do with skills. To stay consistent you'd have to add one row per skill.
The cure is to split the two independent facts: employee_skills and employee_languages. Now French is one row. The split tables hold 2 + 3 = 5 facts, and adding a skill or language is always one row.
4NF says: a table shouldn't contain two or more independent multi-valued facts about the same key. It shows up in practice as "user × role × region" or "product × colour × size" tables that grow as a cartesian product.

AdvancedBeyond 4NF: fifth and sixth normal form

#Denormalisation and when it's worth it

Added

Denormalisation means deliberately storing a fact more than once — a copy, a pre-computed total, a summary table — to make reads faster or simpler. It's not the opposite of normalisation; it's a step after it. You start from a clean, normalised model and add specific, documented redundancy where a measured read path needs it.

A good mental model is a cached answer written into the schema. Like any cache, it's fast to read, and it has an invalidation problem: every write that changes the source facts must also update the copy, and you need a way to detect and repair drift.

Common forms: a stored total (orders.items_total), a counter (products.review_count), a copied attribute to avoid a join (order_items.product_name), summary/rollup tables for reports, and read models built from events.

SQL

ALTER TABLE orders ADD COLUMN items_total DECIMAL(12,2) NOT NULL DEFAULT 0.00;
UPDATE orders o
JOIN (SELECT order_id, SUM(quantity * unit_price) AS total FROM order_items GROUP BY order_id) t
  ON t.order_id = o.id
SET o.items_total = t.total;
CREATE TRIGGER trg_items_ai AFTER INSERT ON order_items FOR EACH ROW
  UPDATE orders SET items_total = items_total + NEW.quantity * NEW.unit_price WHERE id = NEW.order_id;
CREATE TRIGGER trg_items_au AFTER UPDATE ON order_items FOR EACH ROW
  UPDATE orders SET items_total = items_total - OLD.quantity * OLD.unit_price + NEW.quantity * NEW.unit_price WHERE id = NEW.order_id;
CREATE TRIGGER trg_items_ad AFTER DELETE ON order_items FOR EACH ROW
  UPDATE orders SET items_total = items_total - OLD.quantity * OLD.unit_price WHERE id = OLD.order_id;
SELECT id, status, items_total FROM orders WHERE id IN (6, 8) ORDER BY id;
INSERT INTO order_items VALUES (6, 7, 2, 29.00);
UPDATE order_items SET quantity = 5 WHERE order_id = 8 AND product_id = 7;
SELECT id, status, items_total FROM orders WHERE id IN (6, 8) ORDER BY id;
SELECT COUNT(*) AS drifted_orders FROM (
  SELECT o.id FROM orders o JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.id, o.items_total HAVING o.items_total <> SUM(oi.quantity * oi.unit_price)) d;

Result

+----+---------+-------------+
| id | status  | items_total |
+----+---------+-------------+
|  6 | pending |      249.00 |
|  8 | shipped |      116.00 |
+----+---------+-------------+
+----+---------+-------------+
| id | status  | items_total |
+----+---------+-------------+
|  6 | pending |      307.00 |
|  8 | shipped |      145.00 |
+----+---------+-------------+
+----------------+
| drifted_orders |
+----------------+
|              0 |
+----------------+

What’s happening

items_total is the redundant copy: it duplicates what SUM(quantity * unit_price) over the order's lines already says. The backfill UPDATE … JOIN sets it for existing orders: order 6 is 249.00, order 8 is 4 × 29.00 = 116.00.
Three triggers keep it in sync: insert adds the new line's amount, delete subtracts the old one, update subtracts old and adds new. They run inside the same transaction as the change, so the copy can't be seen out of date.
Adding two chargers to order 6 (+58.00) gives 307.00; changing order 8's quantity from 4 to 5 (−116 + 145) gives 145.00.
The reconciliation query recomputes every total from the source rows and counts mismatches: 0. Any denormalised value needs a check like this, run regularly, because the copy will drift sooner or later — through a bug, a bulk load, or a path that bypasses the trigger.
The payoff is on reads: listing or sorting orders by total no longer needs a join and a GROUP BY. The next card measures how much that's worth.

AdvancedMeasured: what the stored total buys

The same idea on 200,000 orders and 500,000 order lines (big_orders, big_items), with an index on big_orders.items_total. "The three largest orders", computed vs stored:

SQL

EXPLAIN ANALYZE
SELECT order_id, SUM(quantity * unit_price) AS total
FROM big_items GROUP BY order_id ORDER BY total DESC LIMIT 3;
EXPLAIN ANALYZE
SELECT id, items_total FROM big_orders ORDER BY items_total DESC LIMIT 3;

Result

*************************** 1. row ***************************
EXPLAIN: -> Limit: 3 row(s)  (actual time=77.3..77.3 rows=3 loops=1)
    -> Sort: total DESC, limit input to 3 row(s) per chunk  (actual time=77.3..77.3 rows=3 loops=1)
        -> Stream results  (cost=167125 rows=191066) (actual time=0.402..68 rows=200000 loops=1)
            -> Group aggregate: sum((big_items.quantity * big_items.unit_price))  (cost=167125 rows=191066) (actual time=0.394..59.7 rows=200000 loops=1)
                -> Index scan on big_items using PRIMARY  (cost=50921 rows=504325) (actual time=0.388..32.8 rows=500000 loops=1)

*************************** 1. row ***************************
EXPLAIN: -> Limit: 3 row(s)  (cost=0.00181 rows=3) (actual time=0.024..0.0246 rows=3 loops=1)
    -> Covering index scan on big_orders using idx_total (reverse)  (cost=0.00181 rows=3) (actual time=0.007..0.00742 rows=3 loops=1)

What’s happening

Computed: MySQL reads all 500,000 lines in primary-key order (32.8 ms), aggregates them into 200,000 totals (59.7 ms), then sorts to find the top 3 — 77.3 ms, and it grows linearly with the table.
Stored: MySQL walks the idx_total index backwards and stops after 3 entries — 0.025 ms, about 3,000 times faster, and it stays that fast at any size.
The cost moved to the write path: every insert, update and delete on big_items would now also update big_orders. That's the trade: denormalise when a read is frequent and slow, and writes to the source are comparatively rare.
Timings are from this sandbox (EXPLAIN ANALYZE actually runs the query); see EXPLAIN ANALYZE for how to read them.

AdvancedWhere else denormalisation pays off

Counters on hot pages — products.review_count, posts.like_count. Counting rows on every page view is wasteful; but a counter row updated by every like is also a write hotspot, so high-traffic counters often live in Redis and are flushed periodically (see What Redis is).
Reporting tables — a nightly daily_sales (day, product_id, units, revenue) built by a batch job. Reports read a few thousand rows instead of scanning millions of order lines. MySQL has no materialised views, so these are ordinary tables you refresh yourself; PostgreSQL has CREATE MATERIALIZED VIEW … REFRESH.
Read models — in event-driven systems, services keep their own denormalised copy of data they need (an order service storing the customer's name), updated from events. The copy is eventually consistent by design.
Snapshots that aren't really denormalisation — order_items.unit_price looks like a copy of products.price, but it's a different fact: the price at the time of the order. It must not be "kept in sync". See Modelling an e-commerce schema.

Spot the bug in the trigger

The trg_items_au trigger from the main example handles quantity changes correctly. What happens when an order line is moved to another order?

SQL

UPDATE order_items SET order_id = 7 WHERE order_id = 6 AND product_id = 7;
SELECT o.id, o.items_total, SUM(oi.quantity * oi.unit_price) AS recomputed
FROM orders o JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.items_total
HAVING o.items_total <> recomputed;

Show answer

Both orders end up wrong:

Result

+----+-------------+------------+
| id | items_total | recomputed |
+----+-------------+------------+
|  6 |      307.00 |     249.00 |
|  7 |     1016.00 |    1074.00 |
+----+-------------+------------+

What’s happening

The trigger does items_total - OLD.amount + NEW.amount but only WHERE id = NEW.order_id — it assumes the line stays on the same order.
Moving the line from order 6 to order 7: the trigger updates order 7 only, subtracting 58.00 and adding 58.00 — net zero. Order 7's total stays 1016.00 though it gained a line (1074.00 real).
Order 6 is never touched, so it still includes the 58.00 it lost: 307.00 instead of 249.00.
The fix subtracts from OLD.order_id and adds to NEW.order_id in two statements (a BEGIN … END trigger body). After recreating the trigger that way, repairing the totals with the backfill query, and moving the line back, the reconciliation query reports drifted_orders = 0.
The general lesson: every write path to the source must be thought through — and the reconciliation query is what caught this.

AdvancedThe corrected trigger: old and new orders

Replace the update trigger with one that handles both orders, repair the totals and move the line back:

SQL

DROP TRIGGER trg_items_au;
DELIMITER //
CREATE TRIGGER trg_items_au AFTER UPDATE ON order_items FOR EACH ROW
BEGIN
  UPDATE orders SET items_total = items_total - OLD.quantity * OLD.unit_price WHERE id = OLD.order_id;
  UPDATE orders SET items_total = items_total + NEW.quantity * NEW.unit_price WHERE id = NEW.order_id;
END//
DELIMITER ;
UPDATE orders o
JOIN (SELECT order_id, SUM(quantity * unit_price) AS total FROM order_items GROUP BY order_id) t ON t.order_id = o.id
SET o.items_total = t.total;
UPDATE order_items SET order_id = 6 WHERE order_id = 7 AND product_id = 7;
SELECT COUNT(*) AS drifted_orders FROM (
  SELECT o.id FROM orders o JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.id, o.items_total HAVING o.items_total <> SUM(oi.quantity * oi.unit_price)) d;
SELECT id, items_total FROM orders WHERE id IN (6, 7) ORDER BY id;

Result

+----------------+
| drifted_orders |
+----------------+
|              0 |
+----------------+
+----+-------------+
| id | items_total |
+----+-------------+
|  6 |      307.00 |
|  7 |     1016.00 |
+----+-------------+

What’s happening

DELIMITER // lets the mysql client send a multi-statement trigger body as one statement; inside, each UPDATE ends with ;.
The first UPDATE takes the old amount off the old order, the second adds the new amount to the new order. When the order doesn't change, both hit the same row and the net effect equals the original formula.
The backfill repairs the totals the buggy trigger corrupted; then the line moves back from order 7 to order 6 through the new trigger.
Totals are right again: order 6 307.00 (it has the chargers back), order 7 1016.00, and the reconciliation finds no drift.

#Modelling an e-commerce schema

Added

Modelling questions in interviews ("design the tables for an online shop") test whether you can turn business rules into tables and constraints. A good answer works entity by entity, states the cardinalities, and calls out the handful of decisions that separate a toy schema from a real one:

Snapshot what must not change. An order records the price paid and the address shipped to at the time; later edits to the catalogue or the customer's address book must not rewrite history.
Money is a ledger. Payments and refunds are rows you add, never amounts you overwrite; the balance is a sum.
One fact, one home. Customers, products, categories and addresses are their own tables; orders reference them.
Constraints carry the rules. Unique provider references (no double-charging the same payment), CHECK (amount > 0), foreign keys everywhere.

Starting from the sample schema, here are addresses, payments and shipments, plus an order-summary query that uses them all:

SQL

CREATE TABLE addresses (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  label       VARCHAR(20) NOT NULL,
  line1       VARCHAR(100) NOT NULL,
  city        VARCHAR(50) NOT NULL,
  country     CHAR(2) NOT NULL,
  UNIQUE (customer_id, label),
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);
ALTER TABLE orders
  ADD COLUMN ship_line1   VARCHAR(100) NULL,
  ADD COLUMN ship_city    VARCHAR(50)  NULL,
  ADD COLUMN ship_country CHAR(2)      NULL;
CREATE TABLE payments (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  order_id     INT NOT NULL,
  kind         ENUM('charge', 'refund') NOT NULL,
  amount       DECIMAL(10,2) NOT NULL CHECK (amount > 0),
  provider_ref VARCHAR(40) NOT NULL UNIQUE,
  created_at   DATETIME NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id)
);
CREATE TABLE shipments (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  order_id        INT NOT NULL,
  carrier         VARCHAR(30) NOT NULL,
  tracking_number VARCHAR(40) NOT NULL UNIQUE,
  shipped_at      DATETIME NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id)
);
INSERT INTO addresses (customer_id, label, line1, city, country) VALUES
  (1, 'home', '1 King St W', 'Toronto', 'CA'), (1, 'work', '200 Bay St', 'Toronto', 'CA'),
  (2, 'home', '12 Marine Dr', 'Mumbai', 'IN');
UPDATE orders o JOIN addresses a ON a.customer_id = o.customer_id AND a.label = 'home'
SET o.ship_line1 = a.line1, o.ship_city = a.city, o.ship_country = a.country;
INSERT INTO payments (order_id, kind, amount, provider_ref, created_at) VALUES
  (1, 'charge', 957.00, 'ch_001', '2025-02-01 10:01:00'),
  (2, 'charge',  88.00, 'ch_002', '2025-03-15 12:31:00'),
  (3, 'charge', 1048.00, 'ch_003', '2025-02-10 09:21:00'),
  (3, 'refund',  249.00, 're_001', '2025-02-20 11:00:00');
INSERT INTO shipments (order_id, carrier, tracking_number, shipped_at) VALUES
  (1, 'Canada Post', 'CP123', '2025-02-02 15:00:00'),
  (3, 'Delhivery',   'DL456', '2025-02-11 10:00:00');
SELECT o.id, c.name, o.status,
       (SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = o.id) AS items_total,
       (SELECT COALESCE(SUM(IF(kind = 'charge', amount, -amount)), 0) FROM payments WHERE order_id = o.id) AS net_paid,
       (SELECT GROUP_CONCAT(tracking_number) FROM shipments WHERE order_id = o.id) AS tracking,
       o.ship_city
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.id IN (1, 2, 3) ORDER BY o.id;

Result

+------+-------+---------+-------------+----------+----------+-----------+
| id   | name  | status  | items_total | net_paid | tracking | ship_city |
+------+-------+---------+-------------+----------+----------+-----------+
|    1 | Rohit | shipped |      957.00 |   957.00 | CP123    | Toronto   |
|    2 | Rohit | paid    |       88.00 |    88.00 | NULL     | Toronto   |
|    3 | Maya  | shipped |     1048.00 |   799.00 | DL456    | Mumbai    |
+------+-------+---------+-------------+----------+----------+-----------+

What’s happening

Addresses are one-to-many from customers (home, work…). UNIQUE (customer_id, label) stops two "home" addresses for one customer.
Orders snapshot the shipping address into their own columns (ship_line1, ship_city, ship_country), copied from the address book when the order is placed. It looks like denormalisation, but it's a different fact: "where this order was shipped", which must never change.
Payments are a ledger. A charge and a refund are separate rows; CHECK (amount > 0) keeps signs out of the data and kind says which way the money moved. Maya's order 3 was charged 1048.00 and refunded 249.00 (the headphones), so net_paid is 799.00. The unique provider_ref makes a retried webhook from the payment provider fail instead of recording the same charge twice.
Shipments are one-to-many from orders (an order may ship in parts), each with a unique tracking number. Order 2 hasn't shipped, so its tracking is NULL.
The summary query reads each child table with a correlated subquery rather than joining them all at once. Joining payments and shipments and items in one FROM would multiply rows (2 payments × 2 items = 4 rows for order 3) and inflate every SUM — the classic fan-out trap.

AdvancedWhy snapshots matter: prices and addresses change

A month later the ThinkBook gets more expensive and Rohit moves house:

SQL

UPDATE products SET price = 999.00 WHERE id = 1;
UPDATE addresses SET line1 = '55 Front St E' WHERE customer_id = 1 AND label = 'home';
SELECT oi.order_id, p.name, p.price AS price_today, oi.unit_price AS price_paid
FROM order_items oi JOIN products p ON p.id = oi.product_id WHERE oi.product_id = 1 ORDER BY oi.order_id;
SELECT o.id, o.ship_line1 AS shipped_to, a.line1 AS current_home
FROM orders o JOIN addresses a ON a.customer_id = o.customer_id AND a.label = 'home'
WHERE o.customer_id = 1 ORDER BY o.id;

Result

+----------+--------------+-------------+------------+
| order_id | name         | price_today | price_paid |
+----------+--------------+-------------+------------+
|        1 | ThinkBook 14 |      999.00 |     899.00 |
|        7 | ThinkBook 14 |      999.00 |     899.00 |
+----------+--------------+-------------+------------+
+----+-------------+---------------+
| id | shipped_to  | current_home  |
+----+-------------+---------------+
|  1 | 1 King St W | 55 Front St E |
|  2 | 1 King St W | 55 Front St E |
+----+-------------+---------------+

What’s happening

The catalogue price is now 999.00, but both orders still say 899.00 was paid — because order_items.unit_price is a snapshot, not a reference. If orders computed their totals from products.price, every past invoice would change with each price update.
Rohit's address book has his new home, but orders 1 and 2 still show where they actually went. A courier dispute or a tax audit needs the address at the time.
The rule of thumb: if a value describes the transaction, copy it into the transaction; if it describes the thing right now, reference it.

AdvancedStock without overselling: a conditional update

Keeping products.stock correct under concurrent checkouts doesn't need a read-then-write; one conditional UPDATE does both:

SQL

UPDATE products SET stock = stock - 1 WHERE id = 4 AND stock >= 1;
UPDATE products SET stock = stock - 1 WHERE id = 2 AND stock >= 1;
SELECT id, name, stock FROM products WHERE id IN (2, 4);

Result

Query OK, 0 rows affected (0.000 sec)
Rows matched: 0  Changed: 0  Warnings: 0
Query OK, 1 row affected (0.009 sec)
Rows matched: 1  Changed: 1  Warnings: 0
+----+-------------+-------+
| id | name        | stock |
+----+-------------+-------+
|  2 | MacBook Air |     4 |
|  4 | iPhone 17   |     0 |
+----+-------------+-------+

What’s happening

The iPhone 17 has stock = 0, so WHERE … AND stock >= 1 matches nothing: 0 rows affected. The application treats that as "sold out".
The MacBook Air had 5; the update matches and takes it to 4.
Because the check and the decrement are one statement, InnoDB's row lock makes concurrent checkouts queue on that row; two buyers can't both take the last unit. A SELECT stock followed by a separate UPDATE would have a race window — see Optimistic vs pessimistic locking.
Adding CHECK (stock >= 0) is a good belt-and-braces constraint on top.

AdvancedWhat a fuller design adds

The sample stops here, but an interview answer would usually mention:

Product variants — size and colour as product_variants (id, product_id, sku UNIQUE, price, stock); the variant, not the product, goes on the order line and holds the stock.
Carts — carts (id, customer_id NULL, session_id, updated_at) and cart_items; guest carts have no customer yet.
Order status history — order_events (order_id, status, at) instead of overwriting orders.status, so you know when it was paid and shipped (see Soft deletes, audit columns and history tables).
Shipment lines — shipment_items (shipment_id, order_id, product_id, quantity) for partial shipments.
Discounts and tax — stored per order line as amounts, not recomputed, for the same snapshot reason as prices.
Idempotency keys — a unique client_request_id on orders so a retried "Place order" request doesn't create two orders.

Design the database for an online shop.

"I'd start with the entities: customers, addresses, categories, products — usually with variants that hold the SKU, price and stock — orders, order lines, payments and shipments. Customers to orders is one-to-many, orders to products is many-to-many through order lines, which also store the quantity and the unit price at the time of purchase. Orders snapshot the shipping address too, so later changes don't rewrite history. Payments are a ledger of charges and refunds with a unique provider reference so webhook retries can't double-charge, and the balance is a sum. I'd use surrogate keys, foreign keys everywhere, CHECK constraints on amounts and quantities, and decrement stock with a conditional update so we can't oversell. Then I'd list the main queries — order history, product page, admin reports — and add indexes for them."

#Soft deletes, audit columns and history tables

Added

Real systems rarely want data to simply vanish. Three related patterns keep a trail:

Soft delete — instead of DELETE, set deleted_at = NOW() and filter it out. The row stays for audits, undo, and foreign keys from historical data (an old order still points at its customer).
Audit columns — created_at, updated_at, sometimes created_by/updated_by on every table: when (and by whom) a row last changed.
History tables — a separate table that keeps every previous version of a row, so you can answer "what was the price on 1 March?".

The mental model: a soft delete is a "void" stamp on a paper record rather than shredding it; audit columns are the "last modified" footer; a history table is the filing cabinet of every previous version.

SQL

ALTER TABLE customers ADD COLUMN deleted_at DATETIME NULL;
UPDATE customers SET deleted_at = '2025-05-01 09:00:00' WHERE id = 6;
SELECT COUNT(*) AS all_rows, SUM(deleted_at IS NULL) AS live_customers FROM customers;
INSERT INTO customers (name, email, city, created_at) VALUES ('Zara', 'zara@example.com', 'Dubai', '2025-06-01 10:00:00');
ALTER TABLE customers
  ADD COLUMN live_email VARCHAR(100) AS (IF(deleted_at IS NULL, email, NULL)) VIRTUAL,
  DROP INDEX email,
  ADD UNIQUE INDEX uq_live_email (live_email);
INSERT INTO customers (name, email, city, created_at) VALUES ('Zara', 'zara@example.com', 'Dubai', '2025-06-01 10:00:00');
INSERT INTO customers (name, email, city, created_at) VALUES ('Zara again', 'zara@example.com', 'Dubai', '2025-06-02 10:00:00');
SELECT id, name, email, deleted_at, live_email FROM customers WHERE email = 'zara@example.com' ORDER BY id;

Result

+----------+----------------+
| all_rows | live_customers |
+----------+----------------+
|        6 |              5 |
+----------+----------------+
ERROR 1062 (23000): Duplicate entry 'zara@example.com' for key 'customers.email'
ERROR 1062 (23000): Duplicate entry 'zara@example.com' for key 'customers.uq_live_email'
+----+------+------------------+---------------------+------------------+
| id | name | email            | deleted_at          | live_email       |
+----+------+------------------+---------------------+------------------+
|  6 | Zara | zara@example.com | 2025-05-01 09:00:00 | NULL             |
|  8 | Zara | zara@example.com | NULL                | zara@example.com |
+----+------+------------------+---------------------+------------------+

What’s happening

Zara closes her account: deleted_at is set instead of deleting the row. The table still has 6 rows, but only 5 live customers.
A month later she signs up again with the same email, and the insert fails with ERROR 1062: the soft-deleted row still holds zara@example.com in the UNIQUE index. This is the most common soft-delete bug.
The fix makes uniqueness apply only to live rows. The generated column live_email is the email while the row is live and NULL once it's deleted; a unique index on it ignores deleted rows, because NULLs never collide (see UNIQUE, NOT NULL, DEFAULT and CHECK).
Now the re-signup works (id 8 — id 7 was burned by the failed insert), and a second live account with the same email is still refused, this time by uq_live_email.
PostgreSQL does this with a partial unique index (CREATE UNIQUE INDEX … ON customers (email) WHERE deleted_at IS NULL); MySQL has no partial indexes, so the generated column plays that role.

AdvancedPitfall: every query must remember the filter

A report on customers in Dubai, with and without the soft-delete filter:

SQL

SELECT city, COUNT(*) AS customers FROM customers WHERE city = 'Dubai' GROUP BY city;
CREATE VIEW live_customers AS
  SELECT id, name, email, city, created_at FROM customers WHERE deleted_at IS NULL;
SELECT city, COUNT(*) AS customers FROM live_customers WHERE city = 'Dubai' GROUP BY city;

Result

+-------+-----------+
| city  | customers |
+-------+-----------+
| Dubai |         2 |
+-------+-----------+
+-------+-----------+
| city  | customers |
+-------+-----------+
| Dubai |         1 |
+-------+-----------+

What’s happening

A report that forgets WHERE deleted_at IS NULL counts the deleted Zara and the new Zara: 2 customers in Dubai, really 1.
A view live_customers bakes the filter in; application code and reports read from the view, and only admin tools read the base table. ORMs offer the same idea (Sequelize's paranoid: true, global query scopes elsewhere).
Soft deletes also bypass foreign key actions: ON DELETE CASCADE never fires, because nothing is deleted. Children of a soft-deleted row must be handled explicitly.
And they don't satisfy erasure laws: under GDPR "right to be forgotten", a soft-deleted personal record still holds personal data. Real systems anonymise (overwrite name and email) or hard-delete after a retention period.

AdvancedHistory tables with a trigger

MySQL has no built-in row versioning, so a trigger copies the old version into a history table whenever the price changes. @app_user is a session variable the application sets after connecting, because the database itself only knows the database account, not the human:

SQL

CREATE TABLE product_price_history (
  product_id INT NOT NULL,
  price      DECIMAL(10,2) NOT NULL,
  valid_from DATETIME(6) NOT NULL,
  valid_to   DATETIME(6) NOT NULL,
  changed_by VARCHAR(50) NULL,
  PRIMARY KEY (product_id, valid_from),
  FOREIGN KEY (product_id) REFERENCES products(id)
);
ALTER TABLE products
  ADD COLUMN price_since DATETIME(6) NOT NULL DEFAULT '2025-01-01 00:00:00',
  ADD COLUMN updated_by  VARCHAR(50) NULL;
DELIMITER //
CREATE TRIGGER trg_products_price_history BEFORE UPDATE ON products FOR EACH ROW
BEGIN
  IF NEW.price <> OLD.price THEN
    INSERT INTO product_price_history (product_id, price, valid_from, valid_to, changed_by)
    VALUES (OLD.id, OLD.price, OLD.price_since, NOW(6), @app_user);
    SET NEW.price_since = NOW(6);
    SET NEW.updated_by = @app_user;
  END IF;
END//
DELIMITER ;
SET @app_user = 'rohit';
UPDATE products SET price = 949.00 WHERE id = 1;
DO SLEEP(1);
SET @app_user = 'pricing-bot';
UPDATE products SET price = 999.00 WHERE id = 1;
UPDATE products SET stock = stock - 1 WHERE id = 1;
SELECT product_id, price, valid_from, valid_to, changed_by FROM product_price_history ORDER BY valid_from;
SELECT id, price, price_since, updated_by, stock FROM products WHERE id = 1;

Result

+------------+--------+----------------------------+----------------------------+-------------+
| product_id | price  | valid_from                 | valid_to                   | changed_by  |
+------------+--------+----------------------------+----------------------------+-------------+
|          1 | 899.00 | 2025-01-01 00:00:00.000000 | 2026-10-16 15:53:41.607655 | rohit       |
|          1 | 949.00 | 2026-10-16 15:53:41.607655 | 2026-10-16 15:53:42.612375 | pricing-bot |
+------------+--------+----------------------------+----------------------------+-------------+
+----+--------+----------------------------+-------------+-------+
| id | price  | price_since                | updated_by  | stock |
+----+--------+----------------------------+-------------+-------+
|  1 | 999.00 | 2026-10-16 15:53:42.612375 | pricing-bot |    11 |
+----+--------+----------------------------+-------------+-------+

What’s happening

It's a BEFORE UPDATE trigger so it can change NEW — stamping price_since and updated_by on the row being written. (ON UPDATE CURRENT_TIMESTAMP would also work for a plain updated_at; see Dates and times: DATETIME vs TIMESTAMP.)
The first price change copies the old version — 899.00, valid from 1 January 2025 until now — into the history, and records who ended it (rohit).
The second change closes the 949.00 version one second later, this time by pricing-bot. The live row holds the current price, since when, and who set it.
The stock update doesn't touch the price, so IF NEW.price <> OLD.price skips it: no history row for unrelated changes.
valid_from/valid_to are half-open intervals (from <= t < to) that tile the timeline without gaps or overlaps, which is what makes "as of" queries simple.

AdvancedQuerying "as of" a point in time

With the history table and the live row together, any past price is one query away:

SQL

SET @as_of = '2025-06-01 00:00:00';
SELECT price FROM product_price_history
WHERE product_id = 1 AND valid_from <= @as_of AND @as_of < valid_to
UNION ALL
SELECT price FROM products WHERE id = 1 AND price_since <= @as_of;
SET @as_of = NOW(6);
SELECT price FROM product_price_history
WHERE product_id = 1 AND valid_from <= @as_of AND @as_of < valid_to
UNION ALL
SELECT price FROM products WHERE id = 1 AND price_since <= @as_of;
CREATE TABLE prices_versioned (id INT PRIMARY KEY, price DECIMAL(10,2)) WITH SYSTEM VERSIONING;

Result

+--------+
| price  |
+--------+
| 899.00 |
+--------+
+--------+
| price  |
+--------+
| 999.00 |
+--------+
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SYSTEM VERSIONING' at line 1

What’s happening

On 1 June 2025 the price was in the history version valid from 2025-01-01 until today: 899.00. The current row's price_since is today, so it doesn't match.
"Now" falls in no closed history interval, but the current row matches: 999.00. Exactly one side of the UNION ALL answers for any moment.
SQL:2011 standardised system-versioned tables that do all of this automatically (FOR SYSTEM_TIME AS OF …). MariaDB, SQL Server and Db2 support them; MySQL 9.7 doesn't — the syntax is a plain ERROR 1064. In MySQL you build it with triggers, as above, or capture changes from the binary log (Debezium) into a history store.

AdvancedPractice: which tables need which pattern

How would you implement soft deletes, and what are the pitfalls?

"A nullable deleted_at timestamp: deleting sets it, and normal reads filter on deleted_at IS NULL — ideally through a view or an ORM scope so nobody forgets. The pitfalls: every query and report has to apply the filter; unique constraints still see deleted rows, so a user can't re-register with the same email unless uniqueness only covers live rows — a partial unique index in PostgreSQL, a generated column that's NULL for deleted rows plus a unique index in MySQL; foreign key cascades don't fire; the table keeps growing; and for personal data a soft delete isn't erasure, so you still need anonymisation or a purge job. For a full change history I'd use a history table filled by a trigger or from change data capture, since MySQL has no system-versioned tables."