SovranCode
SQL: Query, Model, and Analyze Data Normalization and intentional denormalization
This device
Course contentsNormalization and intentional denormalization · 32 topics

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
SQL: Query, Model, and Analyze Data32 complete · 0 planned

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
PREVIOUS LESSONConstraints and data integrity
NEXT LESSONViews and materialized views
Designing reliable schemas · Lesson 19 155 min

Normalization and intentional denormalization

Normalization puts each fact in one home so a change cannot leave contradictory copies. Denormalization keeps a copy only when that copy is a different, justified fact—with an owner who updates it.

Start from anomalies, not from Roman numerals

1NF, 2NF, and 3NF are names for common repairs. You do not need to recite a proof. You need to notice duplicated meaning, decide what one row represents, and move dependent facts to the table that owns them.

Duplication is a maintenance problem

This order sheet looks convenient: every row has the customer, the city, and a product. The convenience is fake. Amina's city is stored twice. The SQL notebook price is stored twice. If Casablanca is wrong, you must edit every Amina row. If you miss one, the database now contains two cities for one person.

That is an update anomaly. Closely related failures are an insert anomaly—you cannot record a customer who has not ordered yet—and a delete anomaly—deleting someone's last order also deletes the only copy of their city.

SQL BROWSER RUNNER

Read a duplicated order sheet

Notice which facts repeat across rows that share an order or a product.

CREATE TABLE order_sheet (
  order_id INTEGER NOT NULL,
  customer_name TEXT NOT NULL,
  customer_city TEXT NOT NULL,
  product_name TEXT NOT NULL,
  quantity INTEGER NOT NULL,
  unit_price_cents INTEGER NOT NULL
);

INSERT INTO order_sheet VALUES
  (5001, 'Amina Idrissi', 'Casablanca', 'SQL notebook', 1, 1499),
  (5001, 'Amina Idrissi', 'Casablanca', 'Database sticker', 2, 299),
  (5002, 'Bilal Karim', 'Rabat', 'SQL notebook', 1, 1499);

SELECT order_id, customer_name, customer_city, product_name, quantity
FROM order_sheet
ORDER BY order_id, product_name;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: How many times is Amina's city stored, and what would go wrong if only one of those rows were updated?

Repeating groups hide a second table

A worse spreadsheet uses product_1 and product_2. The number of products is now a column-design accident. A third item has nowhere to live. Queries such as “total quantity of SQL notebooks” have to inspect several columns. That shape is not first normal form.

First normal form asks for atomic values and a consistent grain: one row, one kind of thing. If an order can contain any number of products, those products are rows in a child table—not extra columns on the parent.

SQL BROWSER RUNNER

See a repeating-group order table

Compare two orders, one of which cannot store a third product.

CREATE TABLE wide_orders (
  order_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  product_1 TEXT,
  qty_1 INTEGER,
  product_2 TEXT,
  qty_2 INTEGER
);

INSERT INTO wide_orders VALUES
  (5001, 'Amina Idrissi', 'SQL notebook', 1, 'Database sticker', 2),
  (5002, 'Bilal Karim', 'SQL notebook', 1, NULL, NULL);

SELECT order_id, customer_name, product_1, qty_1, product_2, qty_2
FROM wide_orders
ORDER BY order_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Why is qty_2 NULL on order 5002, and what would you do if Bilal bought a third product?

First normal form makes the grain consistent

Move each product line onto its own row. (order_id, line_no) identifies one line. Every column now describes that line. The table can hold one item or twenty with the same shape.

1NF is necessary and not sufficient. Customer city still repeats on every line of the same order. You have fixed the repeating group; you have not yet given the customer a home of their own.

SQL BROWSER RUNNER

Put every order line in its own row

Replace product_1 and product_2 with one row per item.

CREATE TABLE order_items_1nf (
  order_id INTEGER NOT NULL,
  line_no INTEGER NOT NULL,
  customer_name TEXT NOT NULL,
  customer_city TEXT NOT NULL,
  product_name TEXT NOT NULL,
  quantity INTEGER NOT NULL,
  PRIMARY KEY (order_id, line_no)
);

INSERT INTO order_items_1nf VALUES
  (5001, 1, 'Amina Idrissi', 'Casablanca', 'SQL notebook', 1),
  (5001, 2, 'Amina Idrissi', 'Casablanca', 'Database sticker', 2),
  (5002, 1, 'Bilal Karim', 'Rabat', 'SQL notebook', 1);

SELECT order_id, line_no, customer_name, product_name, quantity
FROM order_items_1nf
ORDER BY order_id, line_no;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: What does one row represent after this change, and which customer fact is still duplicated?

SQL BROWSER RUNNER

Create an update anomaly on purpose

Change Amina's city on only one line and inspect the contradiction.

CREATE TABLE order_items_1nf (
  order_id INTEGER NOT NULL,
  line_no INTEGER NOT NULL,
  customer_name TEXT NOT NULL,
  customer_city TEXT NOT NULL,
  product_name TEXT NOT NULL,
  quantity INTEGER NOT NULL,
  PRIMARY KEY (order_id, line_no)
);

INSERT INTO order_items_1nf VALUES
  (5001, 1, 'Amina Idrissi', 'Casablanca', 'SQL notebook', 1),
  (5001, 2, 'Amina Idrissi', 'Casablanca', 'Database sticker', 2),
  (5002, 1, 'Bilal Karim', 'Rabat', 'SQL notebook', 1);

UPDATE order_items_1nf
SET customer_city = 'Marrakech'
WHERE order_id = 5001 AND line_no = 1;

SELECT order_id, line_no, customer_name, customer_city
FROM order_items_1nf
ORDER BY order_id, line_no;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: After the UPDATE, how many cities does order 5001 claim for Amina, and which table should own city instead?

Second normal form removes partial key dependencies

Second normal form matters when the primary key has more than one column. In this enrollment table, (student_id, course_id) identifies one enrollment. student_name does not depend on the whole key—it depends only on student_id. course_title depends only on course_id. Those are partial dependencies.

The repair is the same idea as before: give each independent thing its own table. Students own names. Courses own titles. Enrollments own only the relationship.

SQL BROWSER RUNNER

Spot a partial dependency

Read an enrollment table whose names belong to only part of the key.

CREATE TABLE enrollments_unnormalized (
  student_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  student_name TEXT NOT NULL,
  course_title TEXT NOT NULL,
  PRIMARY KEY (student_id, course_id)
);

INSERT INTO enrollments_unnormalized VALUES
  (101, 10, 'Amina Idrissi', 'SQL foundations'),
  (101, 20, 'Amina Idrissi', 'Schema design'),
  (102, 10, 'Bilal Karim', 'SQL foundations');

SELECT student_id, course_id, student_name, course_title
FROM enrollments_unnormalized
ORDER BY student_id, course_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: If Amina changes her name, how many rows must change? Which column depends only on course_id?

SQL BROWSER RUNNER

Split students, courses, and enrollments

Give each fact a table, then join them back into a readable list.

PRAGMA foreign_keys = ON;

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  student_name TEXT NOT NULL
);

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  course_title TEXT NOT NULL UNIQUE
);

CREATE TABLE enrollments (
  student_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

INSERT INTO students VALUES (101, 'Amina Idrissi'), (102, 'Bilal Karim');
INSERT INTO courses VALUES (10, 'SQL foundations'), (20, 'Schema design');
INSERT INTO enrollments VALUES (101, 10), (101, 20), (102, 10);

SELECT student.student_name, course.course_title
FROM enrollments AS enrollment
JOIN students AS student ON student.student_id = enrollment.student_id
JOIN courses AS course ON course.course_id = enrollment.course_id
ORDER BY student.student_name, course.course_title;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Insert a course with no enrollments yet. Why was that impossible in the previous table?

A single-column key is already in 2NF

If the primary key is only order_id, there is no “part of the key” for a non-key column to depend on. You can still violate 3NF. Do not stop at 2NF because the key looks simple.

Third normal form removes transitive dependencies

Here each order has one customer, so the primary key is only order_id. customer_city still does not describe the order. It describes the customer. City depends on customer_id, which depends on order_id. That chain is a transitive dependency.

Move city to customers. After one UPDATE, every order for Amina shows Marrakech because they all read the same customer row. The join is the cost of a single source of truth.

SQL BROWSER RUNNER

Spot a transitive dependency

City is stored on every order even though it belongs to the customer.

CREATE TABLE orders_with_city (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  customer_city TEXT NOT NULL,
  ordered_on TEXT NOT NULL
);

INSERT INTO orders_with_city VALUES
  (5001, 101, 'Casablanca', '2026-10-01'),
  (5002, 101, 'Casablanca', '2026-10-08'),
  (5003, 102, 'Rabat', '2026-10-02');

SELECT order_id, customer_id, customer_city
FROM orders_with_city
ORDER BY order_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Does customer_city describe the order, the customer, or both? What happens if Amina moves?

SQL BROWSER RUNNER

Give the city one home

Update the customer once and read the new city through every related order.

PRAGMA foreign_keys = ON;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  customer_city TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  ordered_on TEXT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO customers VALUES
  (101, 'Amina Idrissi', 'Casablanca'),
  (102, 'Bilal Karim', 'Rabat');
INSERT INTO orders VALUES
  (5001, 101, '2026-10-01'),
  (5002, 101, '2026-10-08'),
  (5003, 102, '2026-10-02');

UPDATE customers SET customer_city = 'Marrakech' WHERE customer_id = 101;

SELECT sale.order_id, customer.customer_name, customer.customer_city
FROM orders AS sale
JOIN customers AS customer ON customer.customer_id = sale.customer_id
ORDER BY sale.order_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: How many rows did the UPDATE touch, and how many order rows now show Marrakech?

A snapshot is a different fact

Normalized models are not allergic to copies. They are allergic to accidental copies of the same fact. The name on a receipt is often not the current catalog name. If the product is later renamed, last month's order should still print what the buyer saw.

Store product_id so the relationship remains. Also store product_name_snapshot and unit_price_cents as facts about the sale. That is intentional denormalization—or, more precisely, recording history instead of live catalog state.

SQL BROWSER RUNNER

Keep a purchase-time product name

Rename the catalog product and compare it with the name stored on the order item.

PRAGMA foreign_keys = ON;

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price_cents INTEGER NOT NULL
);

CREATE TABLE order_items (
  order_item_id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  product_name_snapshot TEXT NOT NULL,
  unit_price_cents INTEGER NOT NULL,
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

INSERT INTO products VALUES
  (1, 'SQL notebook', 1499),
  (2, 'Database sticker', 299);
INSERT INTO order_items VALUES
  (1, 5001, 1, 1, 'SQL notebook', 1499),
  (2, 5001, 2, 2, 'Database sticker', 299);

UPDATE products SET product_name = 'SQL field notebook' WHERE product_id = 1;

SELECT item.order_id, product.product_name AS catalog_name,
  item.product_name_snapshot, item.quantity
FROM order_items AS item
JOIN products AS product ON product.product_id = item.product_id
ORDER BY item.order_item_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which column should a receipt print, and why do we still keep product_id?

Cached totals need an owner

A dashboard may want orders.cached_total_cents so it does not sum items on every page load. The moment you store that total, you have two versions of the amount. If a later insert adds an item and nobody updates the cache, the report is wrong.

Before adding a cached column, name the writer: a transaction that updates items and the total together, a nightly rebuild, or a generated column in a database that supports one. If you cannot name the writer, do not store the copy. Compute it in a query or a view.

SQL BROWSER RUNNER

Watch a cached total drift

Add an order item without updating cached_total_cents, then compare the two amounts.

PRAGMA foreign_keys = ON;

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  cached_total_cents INTEGER NOT NULL
);

CREATE TABLE order_items (
  order_item_id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  unit_price_cents INTEGER NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

INSERT INTO orders VALUES (5001, 2097);
INSERT INTO order_items VALUES
  (1, 5001, 1, 1499),
  (2, 5001, 2, 299);

INSERT INTO order_items VALUES (3, 5001, 1, 500);

SELECT sale.order_id, sale.cached_total_cents,
  SUM(item.quantity * item.unit_price_cents) AS true_total_cents
FROM orders AS sale
JOIN order_items AS item ON item.order_id = sale.order_id
GROUP BY sale.order_id, sale.cached_total_cents;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Why do cached_total_cents and true_total_cents disagree, and what write should have happened with the extra item?

Denormalize the read, not the truth

A reporting table, a materialized summary, or an application cache can be valid. They remain valid only while something rebuilds them from the normalized source. If the copy becomes the only place a fact lives, you are back to anomalies.

Choose a shape on purpose

  1. Write the one-row meaning for each table.
  2. Look for repeating groups and split them into child rows.
  3. Look for facts that depend on only part of a composite key.
  4. Look for facts that depend on another non-key column.
  5. Keep a copy only when it is a snapshot, a cache with an owner, or a measured performance need.
  6. Prove the copy by changing the source and checking whether the copy should follow or stay still.

Independent lab: repair a workshop registration dump

A spreadsheet of registrations repeated member names, emails, workshop titles, and cities on every row. The provided solution gives each person, each workshop, and each registration its own table. One row in registrations is one member in one workshop.

Read the grain of each table, then try the writes that the spreadsheet made painful: rename a workshop city once, add a workshop with no registrations, and prevent the same member from registering twice.

SQL BROWSER RUNNER

Normalize members, workshops, and registrations

Split a duplicated dump into three tables with keys, uniqueness, and relationships.

PRAGMA foreign_keys = ON;

-- One row in members is one person.
CREATE TABLE members (
  member_id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);

-- One row in workshops is one scheduled workshop.
CREATE TABLE workshops (
  workshop_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL UNIQUE,
  city TEXT NOT NULL
);

-- One row in registrations is one member in one workshop.
CREATE TABLE registrations (
  member_id INTEGER NOT NULL,
  workshop_id INTEGER NOT NULL,
  seats INTEGER NOT NULL CHECK (seats > 0),
  PRIMARY KEY (member_id, workshop_id),
  FOREIGN KEY (member_id) REFERENCES members(member_id),
  FOREIGN KEY (workshop_id) REFERENCES workshops(workshop_id)
);

INSERT INTO members VALUES
  (1, 'Amina Idrissi', 'amina@example.com'),
  (2, 'Bilal Karim', 'bilal@example.com');
INSERT INTO workshops VALUES
  (10, 'SQL foundations', 'Casablanca'),
  (20, 'Schema design', 'Rabat');
INSERT INTO registrations VALUES
  (1, 10, 1),
  (1, 20, 1),
  (2, 10, 2);

SELECT member.full_name, workshop.title, workshop.city, registration.seats
FROM registrations AS registration
JOIN members AS member ON member.member_id = registration.member_id
JOIN workshops AS workshop ON workshop.workshop_id = registration.workshop_id
ORDER BY member.full_name, workshop.title;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Update the SQL foundations city to Marrakech. How many registration rows change, and why can Amina still attend two workshops?

Common mistakes to avoid

  • Stopping at 1NF and leaving customer or product facts copied onto every child row.
  • Treating “I do not like joins” as a schema requirement.
  • Using product_1, product_2 columns instead of a child table.
  • Copying a live catalog field onto orders without deciding whether it is a snapshot.
  • Caching a total with no transaction, job, or constraint that keeps it aligned.
  • Normalizing past the point of meaning—splitting a table whose columns all describe the same row.

Lesson review

  • I can recognize update, insert, and delete anomalies caused by duplicated facts.
  • I can repair repeating groups so each row has a consistent grain.
  • I can move partial-key facts into their own tables for 2NF.
  • I can move transitive facts, such as a city that belongs to a customer, for 3NF.
  • I can keep a snapshot or cache only when I can say who owns the copy.
  • I can join the normalized tables back into a readable result.
KNOWLEDGE CHECK

Check your normalization reasoning

Answer all ten questions, then revisit the example whose grain or anomaly still feels unclear.

01What problem does normalization mainly reduce?
02What is first normal form asking you to avoid?
03What does table grain mean here?
04When does second normal form matter?
05What does third normal form forbid?
06What is an update anomaly?
07What is an insert anomaly?
08When is storing product_name on an order item a justified denormalization?
09What is the main risk of caching order_total on the orders table?
10What should you decide before denormalizing?
PREVIOUS LESSONConstraints and data integrity
NEXT LESSONViews and materialized views
ON THIS PAGEDuplicated factsRepeating groupsFirst normal formSecond normal formThird normal formSnapshotsCached totalsChoose a shapeIndependent labCommon mistakesKnowledge check
Course contents