Lesson 20 of 22Advanced20 min

Normalization

By the end of this lesson

Remove harmful duplication, and denormalise deliberately when justified.

Normalization is the practice of storing each fact in exactly one place.

It has a formal vocabulary — first, second and third normal form — that makes it sound like a compliance exercise. It is not. Every rule exists to prevent one specific failure: two copies of the same fact that no longer agree, and no way for the database to tell you which copy is right.

This lesson works against one badly designed table so the rules have something concrete to fix. The result is the schema you have been using since the first lesson of this course, which was normalised before you saw it.

One flat table holding everything about an order line

SQL

CREATE TABLE order_sheet (
    order_id          INT           NOT NULL,
    order_date        DATE          NOT NULL,
    customer_name     NVARCHAR(120) NOT NULL,
    customer_country  NVARCHAR(80)  NOT NULL,
    customer_credit   DECIMAL(12,2) NOT NULL,
    product_id        INT           NOT NULL,
    product_name      NVARCHAR(150) NOT NULL,
    category          NVARCHAR(60)  NOT NULL,
    quantity          INT           NOT NULL,
    unit_price        DECIMAL(10,2) NOT NULL,
    contact_numbers   NVARCHAR(200) NOT NULL
);

-- Four rows of real data from a table like this one
order_id  customer_name        customer_country  customer_credit  product_name   contact_numbers
1043      Kirby Logistics      India             50000.00         Desk lamp      0141 496 0112, 07700 900461
1043      Kirby Logistics      India             50000.00         Filing tray    0141 496 0112, 07700 900461
1052      Kirby Logistics Ltd  india             75000.00         Desk lamp      0141 496 0112
1061      Ward Supplies        India             25000.00         Desk Lamp      07700 900783

Nothing here is invalid. Every column has a sensible type and every row saves without complaint, which is exactly why this design survives long enough to cause damage.
Read the four rows instead of the column list. The same customer appears as "Kirby Logistics" and "Kirby Logistics Ltd", in "India" and "india", with a credit limit of 50,000 and 75,000. The same product appears as "Desk lamp" and "Desk Lamp".
You cannot tell which values are correct. The database cannot either, because it was never told these rows describe the same customer and the same product.
contact_numbers holds two phone numbers in one column. Counting them, validating one, or finding every customer reachable on a mobile number all become string parsing rather than queries.

Duplication is not the problem in itself. These three failures are, and they have names because they are predictable:

Update anomaly. Kirby Logistics raises its credit limit. That value sits in 400 rows, so the update has to find all 400. Miss any — a WHERE clause on the old company spelling, a row added between your two statements — and the table now holds two different credit limits for one customer, both of them stored as fact.
Insert anomaly. A new product arrives that nobody has ordered yet. There is nowhere to record it, because a product exists in this table only as part of an order line. The same applies to a customer who has registered but not yet bought anything.
Delete anomaly. An order is cancelled and its rows are removed. If that was the customer's only order, the customer's country and credit limit are gone with it, and the loss is silent — no error, no missing row you can point at, just a customer the database has never heard of.
Inconsistent duplicates, which is the compound effect of all three. Reports grouping by country return "India" and "india" as two countries. A total by customer splits across two spellings. Both numbers are wrong and both look plausible.

The three normal forms, stated plainly and applied to order_sheet. Assume the key is (order_id, product_id), since that is what identifies one line:

First normal form — one value per column, and no repeating groups: contact_numbers breaks this: it holds a list. The fix is a customer_contacts table with one row per number, which makes "how many customers have a mobile number?" a query rather than a parsing problem. A design with product_1, product_2 and product_3 columns breaks the same rule in the other direction.
Second normal form — every non-key column depends on the whole key, not part of it: product_name and category depend on product_id alone. They have nothing to do with order_id, so they repeat on every line of every order containing that product, and renaming a product means updating all of them. The fix is to move them to a products table and keep only product_id on the line.
Third normal form — no non-key column depends on another non-key column: customer_country and customer_credit depend on the customer, not on the order line. The dependency runs through a column that is not part of the key, which is why one customer's country is stored hundreds of times. The fix is a customers table, with customer_id on the order.
The practical test, if you remember nothing else: Point at any column and ask what it is a fact about. If the answer is not "the thing this table is about", it belongs in another table. customer_credit is a fact about a customer, sitting in a table about order lines.

The same information, each fact stored once

SQL

-- One row per customer. The credit limit now exists in exactly one place.
CREATE TABLE customers (
    customer_id  INT IDENTITY(1,1) NOT NULL,
    company_name NVARCHAR(120)     NOT NULL,
    country      NVARCHAR(80)      NOT NULL,
    credit_limit DECIMAL(12,2)     NOT NULL,
    CONSTRAINT pk_customers PRIMARY KEY (customer_id),
    CONSTRAINT uq_customers_company UNIQUE (company_name)
);

-- One row per phone number, so a customer may have none, one or five.
CREATE TABLE customer_contacts (
    contact_id   INT IDENTITY(1,1) NOT NULL,
    customer_id  INT               NOT NULL,
    phone_number NVARCHAR(30)      NOT NULL,
    contact_type NVARCHAR(20)      NOT NULL,
    CONSTRAINT pk_customer_contacts PRIMARY KEY (contact_id),
    CONSTRAINT fk_customer_contacts_customer FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id)
);

-- The order header holds facts about the order. Nothing about the customer.
CREATE TABLE orders (
    order_id    INT IDENTITY(1,1) NOT NULL,
    customer_id INT               NOT NULL,
    order_date  DATE              NOT NULL,
    status      NVARCHAR(20)      NOT NULL,
    CONSTRAINT pk_orders PRIMARY KEY (order_id),
    CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id)
);

-- The line holds facts about the line: which product, how many, at what price.
CREATE TABLE order_items (
    order_item_id INT IDENTITY(1,1) NOT NULL,
    order_id      INT               NOT NULL,
    product_id    INT               NOT NULL,
    quantity      INT               NOT NULL,
    unit_price    DECIMAL(10,2)     NOT NULL,
    CONSTRAINT pk_order_items PRIMARY KEY (order_item_id),
    CONSTRAINT fk_order_items_order FOREIGN KEY (order_id)
        REFERENCES orders (order_id),
    CONSTRAINT fk_order_items_product FOREIGN KEY (product_id)
        REFERENCES products (product_id),
    CONSTRAINT uq_order_items_order_product UNIQUE (order_id, product_id)
);

The credit limit now exists once. Raising it is a single-row UPDATE that cannot leave part of the database disagreeing with the rest, which removes the update anomaly by construction rather than by care.
A product or a customer can exist without any orders, because each has its own table. That removes the insert anomaly, and deleting an order no longer takes customer information with it.
UNIQUE on company_name is what stops "Kirby Logistics" and "Kirby Logistics Ltd" becoming two customers by accident. Normalization gives you one place to put such a rule; the rule itself is still a decision you have to make.
unit_price stays on order_items and is not read from products at query time. That looks like the duplication this lesson is about and is not: the price charged on 14 March is a different fact from the price today, and it must not change when somebody updates a price list.
The cost, stated plainly: answering "which products did Kirby Logistics buy?" now needs three joins. That is the trade normalization makes — more joins in exchange for data that cannot contradict itself.

Trade-off

Denormalising on purpose is a legitimate decision with a price

Third normal form is the right default for data you write. It is not a rule that overrides measurement. There are real cases where storing a fact twice is the better engineering choice.

A dashboard aggregating six million order lines on every page load, at four seconds a time, may be better served by an order_totals table holding a line count and a total per order. A product catalogue that joins five tables to show a category name may be better served by storing the name alongside the product. Both are denormalisation, and both can be correct.

The price is specific, and it is always the same price: you now own the consistency that the schema used to guarantee. Something must update the copy whenever the original changes — application code, a trigger, or a scheduled rebuild — and when that something fails, the two values disagree with no error to tell you.

So the decision is only defensible with three things attached: a measurement showing the normalised version is genuinely too slow for a real requirement, a named mechanism that maintains the copy, and an honest statement of how stale the copy may be. "Denormalised for performance" with none of those is a guess that will be discovered as a data bug.

Try the cheaper options first. An index, a covering index, or a view often closes the gap without introducing a second copy of a fact. An indexed view or a materialised view is a middle position worth knowing: the database maintains the copy for you, at a cost on every write to the underlying tables.

Common mistake

Common mistakes

Treating a comma-separated list as "good enough for now". Every later question about those values needs string parsing, nothing validates them, and by the time it hurts there is production data in that shape to migrate.

Copying a value to "avoid a join" without measuring the join. A well-indexed join between two tables on an integer key is one of the cheapest operations a database performs. The cost being avoided is frequently imaginary, and the consistency being given up is not.

Normalising past the point of usefulness. Splitting country into a countries table with an id is often worth it for consistency; splitting a person's title into its own table rarely is. The question is whether the value has facts of its own and whether it needs to be kept consistent, not how many tables you can produce.

Storing a calculated total that nothing maintains. line_total on an order line, written once at insert and never updated when the quantity changes, is a value that silently stops matching the columns beside it.

Assuming normalization removes the need for constraints. Moving the customer to its own table stops the country repeating; only a UNIQUE constraint stops the same customer being entered twice, and only a CHECK or a lookup table stops "india" and "India" both being accepted.

Denormalising during initial design, before any measurement exists. You take on the consistency problem for a performance problem you have not yet observed.

Summary

Normalization means each fact is stored once, so copies cannot disagree
The rules exist to prevent update, insert and delete anomalies — name the anomaly, not the form number
First normal form: one value per column. Second: no dependency on part of the key. Third: no dependency on another non-key column
Ask what each column is a fact about; if it is not the thing the table is about, it belongs elsewhere
Denormalise only with a measurement, a mechanism that maintains the copy, and a stated tolerance for staleness

Practice

Attempt each one before opening the solution. Getting it wrong first is how the idea sticks.

Try it yourself

This table records employee training. Identify which normal form each problem breaks, then redesign it.

training_log (employee_id, employee_name, job_title, manager_name, course_code, course_title, course_hours, completed_on, skills_gained)

skills_gained holds values such as "SQL, reporting, data modelling". The same course is taken by many employees, and course_hours is a property of the course.

Show solution

skills_gained breaks first normal form: it is a list in one column. It becomes a skills table plus a link table, because a skill is a thing in its own right that several courses can teach.

Taking the key as (employee_id, course_code), course_title and course_hours break second normal form — they depend on course_code alone, so they repeat for every employee who has taken that course. Correcting the hours on one course currently means updating every row that mentions it.

employee_name, job_title and manager_name break third normal form: they are facts about the employee, not about the employee's completion of a course. manager_name is worse than the others, because it duplicates a name that already exists in the employees table and will drift out of step with it.

The result is four tables: employees (already in your schema, with manager_id as a self-reference), courses, employee_courses for the completion, and a skills pair. What remains on employee_courses is exactly what is true of that combination and nothing else — which employee, which course, when they completed it.

One judgement call worth naming: completed_on stays on the link table because it is a fact about the completion itself. If a course could be taken more than once, the key needs to include completed_on or the table needs its own surrogate key, otherwise a retake cannot be recorded.

SQL

CREATE TABLE courses (
    course_id    INT IDENTITY(1,1) NOT NULL,
    course_code  NVARCHAR(20)      NOT NULL,
    course_title NVARCHAR(150)     NOT NULL,
    course_hours DECIMAL(5,2)      NOT NULL,
    CONSTRAINT pk_courses PRIMARY KEY (course_id),
    CONSTRAINT uq_courses_code UNIQUE (course_code),
    CONSTRAINT ck_courses_hours CHECK (course_hours > 0)
);

CREATE TABLE employee_courses (
    employee_id  INT  NOT NULL,
    course_id    INT  NOT NULL,
    completed_on DATE NOT NULL,
    CONSTRAINT pk_employee_courses PRIMARY KEY (employee_id, course_id, completed_on),
    CONSTRAINT fk_employee_courses_employee FOREIGN KEY (employee_id)
        REFERENCES employees (employee_id),
    CONSTRAINT fk_employee_courses_course FOREIGN KEY (course_id)
        REFERENCES courses (course_id)
);

CREATE TABLE skills (
    skill_id INT IDENTITY(1,1) NOT NULL,
    name     NVARCHAR(80)      NOT NULL,
    CONSTRAINT pk_skills PRIMARY KEY (skill_id),
    CONSTRAINT uq_skills_name UNIQUE (name)
);

CREATE TABLE course_skills (
    course_id INT NOT NULL,
    skill_id  INT NOT NULL,
    CONSTRAINT pk_course_skills PRIMARY KEY (course_id, skill_id),
    CONSTRAINT fk_course_skills_course FOREIGN KEY (course_id)
        REFERENCES courses (course_id),
    CONSTRAINT fk_course_skills_skill FOREIGN KEY (skill_id)
        REFERENCES skills (skill_id)
);

Think about it

A colleague proposes adding order_total to the orders table, so the customer statement page stops summing order_items on every request. The page currently takes 2.8 seconds and is the most viewed screen in the application.

Would you agree? What would you require before doing it, and what would you try first?

Show solution

The proposal is reasonable and the reason is specific, which already makes it better than most denormalisation. But 2.8 seconds on a sum over order lines is suspicious on its own — that is usually an indexing problem rather than a design problem.

So try the cheaper options first. Check whether order_items has an index on order_id, and whether a covering index including quantity and unit_price removes the lookups. Look at whether the page is summing every order the customer has ever placed when it displays twelve. Measure each change. A large share of "we need to denormalise" turns out to be a missing index or a query fetching far more than the screen shows.

If the measurement survives that, the column is defensible, and then three things have to be settled before it is added. What maintains it — a trigger, the application's write path, or a scheduled recalculation. How wrong it is allowed to be, and for how long. And how you detect a mismatch: a reconciliation query comparing order_total against the sum of its lines, run on a schedule, so a drift is found by you rather than by a customer.

Also say plainly what you are giving up. Today the total cannot be wrong, because it is derived. After the change it can be wrong, and the database will not tell you. That is an acceptable trade for a measured problem with a maintenance mechanism attached, and a poor one for a hypothetical problem with neither.

Saved in this browser only.

Summary

Practice

Try it yourself

Think about it

Knowledge check