Normalization: Store Each Fact in the Right Place

Normalization is a way to organize related facts so a change does not require editing many copies of the same information. Begin with the problems in a combined table, then use the normal forms to explain how to separate it safely.

You should already understand rows, primary keys, and foreign keys from data models.

Start with a table that repeats facts

Suppose every order line stores the customer's details and the product's title:

The row's identifier is the pair (order_id, line_number). Alice's name is repeated, even though it describes a customer rather than a particular line.

If she changes her name, all three rows need an update. Missing one update leaves contradictory names for customer 1. This is an update anomaly: a problem caused by storing one fact in several places.

Two other anomalies follow. We cannot record a new product without inventing an order line, an insertion anomaly. Deleting the last line containing a product would lose its title, a deletion anomaly.

The update problem becomes visible if only the third row is changed:

All three rows refer to the same customer, yet two different names are stored. Updating customer_name in a single customer row would avoid this disagreement; the order lines would refer to that row by customer_id.

The insertion problem is different. Suppose product 30, Indexing Illustrated, is available for sale but has not been ordered. The combined table requires a non-null order_id and line_number, so the following product cannot be recorded on its own:

In a separate products table it can be stored immediately, whether or not any order_items row refers to it. Inventing an order to hold product 30 would assert a purchase that never happened.

For the deletion problem, imagine removing order line (101, 2), the only line referring to product 20. The remaining order data contains only these product details:

Product 20's title has disappeared, even though deleting one order line should not delete the catalog entry. With a separate products table, the row (20, SQL Practice) remains until someone deliberately removes that product.

Make the anomalies visible in SQL

Run this separate SQLite experiment in a fresh database. It records the intentionally repeated customer name from the opening example:

PRAGMA foreign_keys = ON;

CREATE TABLE norm_raw_lines (
    order_id INTEGER NOT NULL,
    line_number INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
    customer_name TEXT NOT NULL,
    product_id INTEGER NOT NULL,
    product_title TEXT NOT NULL,
    quantity INTEGER NOT NULL,
    PRIMARY KEY (order_id, line_number)
);
INSERT INTO norm_raw_lines VALUES
    (101, 1, 1, 'Alice', 10, 'Database Basics', 2),
    (101, 2, 1, 'Alice', 20, 'SQL Practice', 1),
    (102, 1, 1, 'Alice', 10, 'Database Basics', 1);
UPDATE norm_raw_lines SET customer_name = 'Alice Smith' WHERE order_id = 102;

SELECT customer_id, COUNT(DISTINCT customer_name) AS stored_names
FROM norm_raw_lines
GROUP BY customer_id;

The update succeeded because the raw table has no rule connecting the repeated names. Yet the business rule customer_id → customer_name has been broken. Normalization changes the representation so this fact is stored at its proper owner rather than trusting every writer to repair every copy.

First normal form: represent separate facts separately

Suppose the bookstore needs to find each ordered product and record its quantity. A column such as products = '10,20,30' bundles several independently meaningful product references into one value. Parsing this string to find product 20 or attach quantities is a warning sign.

An order with a repeating list inside one column might look like this:

Separating the order from its individual lines gives two tables:

orders(order_id, customer_id)
order_items(order_id, line_number, product_id, quantity)

The orders table records which customer placed the order:

The order_items table records each product and its quantity on a separate line. Each line can be identified without parsing a list or relying on its position in a comma-separated string.

Each cell now carries one value for this model, and a line has an identifiable key. This is the practical idea behind first normal form (1NF). It does not yet establish 2NF or 3NF; the earlier combined table has single values in every cell and still repeats customer and product facts.

“Atomic” does not mean physically indivisible: a date can remain one value despite containing a year, month, and day. A list or JSON document can also be a legitimate opaque value for a different requirement. The problem here is that the application treats its elements as independently related products.

Understand a dependency with a question

A functional dependency X → Y means that knowing X determines Y. Ask: “if two rows have the same X, must they have the same Y?”

In our bookstore:

customer_id → customer_name
product_id → product_title
order_id → customer_id
(order_id, line_number) → product_id, quantity, purchase_price

These are assumed business rules. A sample with no contradictions does not prove a rule. Product titles, for example, need not determine product IDs because several products can share a title.

Consider a hypothetical catalog with an additional customer and a new product that happens to share an existing title:

The product title Database Basics identifies both products 10 and 31. Therefore, product_title → product_id does not hold, even though product_id → product_title does. A dependency has a direction: the fact that every product ID has one title does not imply that every title belongs to only one product.

The dependency order_id → customer_id also becomes testable with two order lines:

Both lines belonging to order 101 must have customer 1. If the second row instead had customer 2, the two rows would contradict the rule about who placed that order. However, the two rows share order_id = 101 but have different line numbers, showing why order_id alone cannot identify a line; the pair (order_id, line_number) is needed.

These examples illustrate how to check a proposed dependency against rows. They do not prove that the rule holds for every possible future row. That requires the intended business meaning of the columns.

Second normal form: use the whole key

Our combined table has a two-column key (order_id, line_number). But order_id → customer_id: finding the customer needs only part of that key.

The following table is in 1NF but not 2NF because the customer depends only on order_id:

order_items(order_id, line_number, customer_id, product_id, quantity)

The two rows for order 101 must repeat customer_id = 1, although the customer is a property of the order rather than of either individual line. If the order were reassigned to another customer, both rows would need an update to avoid contradictory customer IDs within the same order.

Moving the order details to their own relation removes that partial dependency:

orders(order_id, customer_id, customer_name)
order_items(order_id, line_number, product_id, product_title, quantity, purchase_price_cents)

The orders row represents each order once; line-specific quantity and charged price stay with the line. This is an intermediate 2NF design, not yet 3NF: customer names and product titles still live in tables identified by something other than their natural owner.

For the original data, the intermediate tables contain the following rows. The customer name still appears once per order, but no longer once per order line.

orders:

order_items:

In the old combined line table, order_id = 101 appeared twice and each row repeated customer_id = 1. In the new orders table, order 101 appears once. This fixes the partial dependency because customer_id depends on order_id, not on the entire (order_id, line_number) key. By contrast, the quantity of product 10 is 2 on line (101, 1) and 1 on line (102, 1): that value really does describe a particular line and should remain there.

Notice that 2NF does not mean all repetition has gone away. customer_name still appears in both orders and product_title still appears in two order items. Those repetitions have different causes, which 3NF addresses next.

A candidate key is a minimal set of attributes identifying a row: removing any column stops it being a key. 2NF requires 1NF and that no non-prime attribute depend on a proper subset of any candidate key. A prime attribute belongs to at least one candidate key; for this example, customer_id is not prime in the combined line table. A single-column candidate key cannot have this kind of partial dependency.

Third normal form: remove the extra hop

The 2NF orders table still repeats Alice's name across her orders. The dependency has an extra hop:

order_id → customer_id → customer_name

Similarly, in the 2NF line table, (order_id, line_number) → product_id → product_title.

The intermediate 2NF tables still store a customer name with each order and a product title with each line: orders(order_id, customer_id, customer_name) and order_items(..., product_id, product_title, ...).

Separating customer and product details from orders and order lines produces the 3NF design:

customers(customer_id, customer_name)
products(product_id, product_title, current_price_cents)
orders(order_id, customer_id)
order_items(order_id, line_number, product_id, quantity, purchase_price_cents)

Now renaming Alice changes one customer row, and changing a catalog title changes one product row. Joins reconstruct the display for each order, rather than storing repeated names or titles.

After separating customers and products, the same purchases are stored in four tables:

customers:

products:

orders:

order_items:

The extra hop order_id → customer_id → customer_name is eliminated from orders: it has customer_id but no longer stores customer_name. The product title is also removed from each order item, because product_id already identifies it in products. Changing the name from Alice to Alice Smith affects only the customer row shown above, without requiring edits to either order or any item.

The tables still need foreign keys. For example, orders.customer_id must refer to an existing customer and order_items.product_id must refer to an existing product. Merely moving columns without enforcing valid references would not give the same protection against inconsistent data.

This illustrates 3NF: in this example, a non-key attribute must not depend transitively on the key through another non-key attribute. This is a teaching shortcut, not the full definition for overlapping candidate keys; the formal criterion appears below.

Implement the corrected design in SQLite

Run this after the raw-table experiment in the same SQLite database (with PRAGMA foreign_keys = ON). These four tables are the correct counterpart of norm_raw_lines. The primary and foreign keys make row identity and references explicit. Prices are integer cents.

CREATE TABLE norm_customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT NOT NULL
);
CREATE TABLE norm_products (
    product_id INTEGER PRIMARY KEY,
    product_title TEXT NOT NULL,
    current_price_cents INTEGER NOT NULL CHECK (current_price_cents >= 0)
);
CREATE TABLE norm_orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES norm_customers(customer_id)
);
CREATE TABLE norm_order_items (
    order_id INTEGER NOT NULL REFERENCES norm_orders(order_id),
    line_number INTEGER NOT NULL,
    product_id INTEGER NOT NULL REFERENCES norm_products(product_id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    purchase_price_cents INTEGER NOT NULL CHECK (purchase_price_cents >= 0),
    PRIMARY KEY (order_id, line_number)
);

INSERT INTO norm_customers VALUES (1, 'Alice Smith');
INSERT INTO norm_products VALUES
    (10, 'Database Basics', 1800),
    (20, 'SQL Practice', 2500);
INSERT INTO norm_orders VALUES (101, 1), (102, 1);
INSERT INTO norm_order_items VALUES
    (101, 1, 10, 2, 1500),
    (101, 2, 20, 1, 2500),
    (102, 1, 10, 1, 1500);

SELECT o.order_id, i.line_number, c.customer_name,
       p.product_title, i.quantity, i.purchase_price_cents
FROM norm_orders AS o
JOIN norm_customers AS c ON c.customer_id = o.customer_id
JOIN norm_order_items AS i ON i.order_id = o.order_id
JOIN norm_products AS p ON p.product_id = i.product_id
ORDER BY o.order_id, i.line_number;

The join output repeats names and titles to display each purchase, but the stored data has only one customer name and one title per product. norm_order_items owns each line's historical price, while norm_products owns the current catalog price.

The corrected name was chosen deliberately; blindly using SELECT DISTINCT on contradictory source records cannot decide which name is authoritative. A migration must resolve conflicts before adding keys and populating these tables. For a broader bookstore schema, see data models.

Purchase price is not unnecessary duplication

A product's current price and the price charged on an order line are different facts. If product 10 now costs 18.00 but a past sale charged 15.00, storing both values is correct.

Normalization asks what a value means before deciding where it belongs. Removing purchase price and substituting a join to the current price would destroy historical information.

The distinction is visible when comparing the catalog with an earlier purchase:

products:

order_items:

If the current catalog price later changes from 1800 to 2000, the products row changes but the two order_items rows stay at 1500. A report of what customers actually paid still produces 1500 for each purchase. Replacing purchase_price_cents with current_price_cents would incorrectly report 2000 for both historical orders.

The numbers are not duplicate versions of the same fact: they describe the same product at different points in the business process. A schema is not more normalized merely because it stores fewer values.

Split tables without inventing combinations

A good decomposition must be lossless: joining the pieces reconstructs the original facts without adding false combinations or losing facts.

For example, split (customer_id, customer_name, email) into tables connected by the customer ID, rather than separating a list of names from a list of emails with no reliable link.

The difference matters if two different customers happen to have the same name:

One safe decomposition keeps the IDs in both pieces:

customer_names:

customer_emails:

Joining on customer_id gives exactly the original two rows. But consider discarding the ID from customer_emails and keeping only (customer_name, email). Joining those records to customer_names on the name produces this result:

The two cross-matched rows are invented; no original row said customer 1 had the work address or customer 2 had the personal address. The split lost the information that connected each customer to the correct email. A lossless decomposition must preserve that connection, not merely include all the original values somewhere in the pieces.

Also check dependency preservation: can the original rules still be enforced on the separate tables without joining them merely to check validity? Some decompositions make enforcement more complicated even when the join is lossless.

A lossless join and dependency preservation ask different questions. In the email example, the join reconstructs the data without extra rows because both tables retain the identifying customer ID. Dependency preservation concerns whether a rule, such as “a student has at most one teacher per subject,” can still be checked using keys or constraints within the resulting tables. The BCNF example below shows a lossless split where this second property is not automatic.

More precise definitions and higher forms

Use these after the examples, rather than as the starting explanation. A superkey identifies a row but may contain unnecessary columns. A prime attribute belongs to at least one candidate key. A dependency is nontrivial when it determines something not already included on its left side.

For example, suppose the following orders rows obey the rule that every order ID is unique:

Here order_id is a candidate key because it identifies a row without any additional column. The pair (order_id, customer_id) is also a superkey, but it is not a candidate key: removing customer_id leaves a working key. Under these rules, order_id is prime, while customer_id and order_date are non-prime. The dependency order_id → order_date is nontrivial because the date is not part of the determinant on the left. By contrast, (order_id, customer_id) → order_id is trivial: the right-hand attribute is already included on the left.

For the DKNF distinction, imagine a small table of numbered order lines with a positive quantity:

If the only business restrictions are that order_id and line_number together must be unique and quantity must be a positive integer, the restrictions follow from the key and the column domains. Now consider a different table with a rule that every order with destination = DE must have delivery_cents = 500:

A row (103, DE, 900) has a unique order ID, a recognized destination and a nonnegative fee, yet violates the stated rule for German orders. Separate domain and key checks do not express the required relationship between destination and delivery_cents. That relationship needs an additional constraint or a different representation; it does not follow automatically from the allowed values of either column or from the key. DKNF asks whether every required constraint can be expressed using only domains and keys, not whether a table happens to look small or well-organized.

For a BCNF example, suppose each teacher teaches one subject and each student has one teacher per subject. In (student, subject, teacher), teacher → subject, but teacher alone does not identify a row. Splitting teacher-subject and student-teacher facts is lossless, yet enforcing one teacher per student and subject now needs a cross-table rule. BCNF can therefore trade simpler facts for harder constraint enforcement.

BCNF: show why overlapping keys matter

Suppose each teacher teaches exactly one subject, and a student has one teacher for each subject. For (student, subject, teacher), the candidate keys are (student, subject) and (student, teacher). Every attribute is prime because it belongs to a candidate key.

teacher → subject holds under the business rule. Teacher alone is not a superkey because Lee has several students. Thus the relation can meet 3NF's prime-attribute exception while violating Boyce–Codd normal form (BCNF).

To remove the repeated teacher–subject fact, keep each teacher's subject once and record student–teacher assignments separately.

teacher_subject (key: teacher):

student_teacher (key: (student, teacher)):

The shared teacher determines the subject, making the join lossless. However, the split does not automatically preserve every original rule. Assigning a new teacher Chen to Databases, then assigning Alice to Chen, would pass the individual table keys but break the rule that Alice has only one teacher for Databases. The corrected BCNF structure therefore still needs a cross-table constraint or transaction-safe validation for that business rule. This is why lossless decomposition and dependency preservation are distinct questions.

BCNF is useful when repeated facts remain because of overlapping keys. It is not a command to split every table further regardless of enforcement costs.

The constraint problem can be seen in actual rows. Suppose the teacher_subject table is extended with another Databases teacher:

The student_teacher table can then contain both of these assignments without breaking its (student, teacher) key:

Joining them gives:

Both tables satisfy their own keys and teacher → subject, but the original rule that Alice has only one teacher per subject is violated. This example explains exactly what was lost from local enforcement. The decomposition is lossless, yet the (student, subject) → teacher rule must now be checked across the tables, for example by a safely enforced database-level mechanism. Checking once in application code without concurrency protection would not be enough.

4NF: independent lists create repeated combinations

Suppose a customer can have several preferred languages and several favorite categories, and these lists are independent. Storing (customer, language, category) requires every language/category pairing:

These are multivalued dependencies: customer →→ language and customer →→ category. Adding a language requires a row for every category; deleting one combination can falsely suggest that a language is associated with only one category.

The combined table above stores four rows for two independent lists; this is the 4NF violation.

Represent the two independent lists in separate tables instead:

norm_languages:

norm_categories:

Instead of four stored language-category combinations, we now store two language facts and two category facts. If Alice adds French, only (Alice, fr) needs to be inserted into norm_languages, not a row for every category. The query below produces all combinations when the application actually needs them.

CREATE TABLE norm_languages (
    customer_name TEXT NOT NULL,
    language TEXT NOT NULL,
    PRIMARY KEY (customer_name, language)
);
CREATE TABLE norm_categories (
    customer_name TEXT NOT NULL,
    category TEXT NOT NULL,
    PRIMARY KEY (customer_name, category)
);
INSERT INTO norm_languages VALUES ('Alice', 'en'), ('Alice', 'de');
INSERT INTO norm_categories VALUES ('Alice', 'History'), ('Alice', 'Computing');

SELECT l.language, c.category
FROM norm_languages AS l
JOIN norm_categories AS c ON c.customer_name = l.customer_name
ORDER BY l.language, c.category;

The join reconstructs all combinations because independence is the stated rule. If Alice prefers German only for History, that rule is false, and separating the lists would invent preferences. The decomposition is justified by meaning, not by seeing repeated strings in a sample.

The text name is a short teaching identifier here; a production schema would normally reference a stable customer key.

The independence assumption matters. Suppose Alice prefers English for Computing and German for History, rather than having two independent preference lists:

If we split this table into languages and categories as above, their join also produces (Alice, en, History) and (Alice, de, Computing). Those extra combinations would claim preferences Alice never expressed. In this situation the relationship itself is a fact, so the three-column table must retain the pairings. The 4NF split is justified by independent multivalued relationships, not by repetition alone.

5NF: three pairwise facts do not always imply a triple

A supplier–product–project relation can record which supplier is approved to supply which product to which project. Pairwise lists of supplier/product, supplier/project, and product/project are enough only if the business explicitly says every triple allowed by all three pairs is valid.

Consider these approved triples:

A supplier ID identifies a supplier, a product ID identifies a product, and a project ID identifies a project. In this example, a row means that the specific supplier is approved to deliver that specific product to that specific project. Merely having permission for each pair does not necessarily imply permission for the triple.

(S1, P1, J1)
(S1, P2, J2)
(S2, P1, J2)

Projecting them into pairs permits joining (S1, P1), (S1, J2), and (P1, J2) into (S1, P1, J2), which was never approved. That is a spurious tuple: an invented combination. A naive three-way decomposition is lossy with respect to the intended association even if every original tuple can be recovered.

Here are the three pairwise projections formed from those original approved triples.

supplier_product:

supplier_project:

product_project:

Joining all three pairwise tables reconstructs the following rows:

The row (S1, P1, J2) is not a harmless duplicate. It grants an approval that did not exist in the original relation. This is why the pairwise projection is not lossless under the first business rule.

Replacing the three original triples with only their pairwise projections would admit (S1, P1, J2), so it changes the meaning of the data.

In a different business, the rules may explicitly guarantee that any supplier, product and project combination is permitted whenever all three pairwise permissions hold. For that business, the pairwise relationships themselves are the facts. Consider:

supplier_product:

supplier_project:

product_project:

The three-way join gives exactly the triples approved by this rule:

For example, (S2, P2, J1) does not appear because supplier S2 has no permission for project J1, even though S2 can supply product P2 and P2 is approved for J1. Conversely, (S1, P2, J2) appears because all three relevant pairs are present. Under the stated business rule, every generated triple is supposed to be present, so this decomposition is lossless. The difference from the earlier counterexample is the meaning of the approval rule, not the shape of the table.

5NF addresses join dependencies: when can projections be joined without changing the facts? It is useful for genuine multiway relationship rules, but a diagram with three pairwise edges is not enough evidence to remove the triple table.

6NF and temporal facts

Some temporal designs split independently changing facts into very small relations. A customer's address and credit limit may have different validity intervals. Keeping them in one versioned row can require copying one whenever only the other changes.

For example, separate address history (customer_id, valid_from, address) and credit-limit history (customer_id, valid_from, limit_cents) let each fact have its own timeline. Queries must still define interval endpoints, gaps, overlap rules, and how facts are combined at a chosen time. Timestamped tables are not automatically 6NF; the formal criterion concerns irreducible join dependencies.

Suppose both facts were originally kept in a single versioned customer table, with dates representing the first valid day and the first day after the interval. A range from 2026-01-01 to 2026-02-01 includes January but not February 1.

On February 1 only the credit limit changed, but the address had to be repeated in a new row. On March 1 only the address changed, but the credit limit had to be copied again. If these two facts genuinely vary independently, their histories can be stored separately:

customer_address_history:

customer_limit_history:

For a query asking about 2026-02-15, the address row valid on that date says Berlin and the limit row says 150000. The answer is obtained by matching the customer and the overlapping intervals, not by joining on equal valid_from dates: the starts of the two histories need not coincide.

Each history needs explicit rules about interval overlap, gaps, and inclusive or exclusive boundaries. This example shows why independently changing temporal facts may be separated; it does not establish that any table with timestamps is in 6NF. The formal requirement is that the relation have no nontrivial join dependency, which must be assessed against the full set of business constraints.

Most operational designs begin with 3NF or a carefully reasoned BCNF design, then inspect actual rules that motivate higher forms. PostgreSQL and other relational engines implement the resulting tables and constraints; there is no NORMALIZE TABLE command that discovers your business dependencies for you.

A design review procedure

Use a small incorrect-versus-correct checklist before declaring a schema normalized:

Check insertion, update, and deletion anomalies using counterexamples, not only happy-path data. Finally compare any proposed decomposition against the application's real business rules.

Normalization protects representation; concurrency control protects overlapping changes. A normalized schema can still suffer a lost update if the application uses unsafe read-and-write logic.

For example, even in a normalized inventory(product_id, stock_count) table, two transactions may both read stock 10 and each independently decide to write 9 after one item is sold:

Two items were sold, so the correct result is 8, but the final stored value is 9. That lost update cannot be repaired by moving columns into additional tables; it needs appropriate transactional update logic, locking, or other concurrency control.

Summary

Easy way to remember (1NF–BCNF)

• 1NF: One value per cell.
• 2NF: No partial dependencies.
• 3NF: No transitive dependencies (simplified).
• BCNF: Every determinant must be a superkey.

Remember: Each higher normal form automatically satisfies all lower normal forms.

Check your understanding

1. Which anomaly appears when Alice has different names on different order lines?
2. Why does an order's customer belong in orders, rather than every item row?
3. Why should purchase price remain on the order item?
4. What could go wrong if a decomposition loses the columns connecting its pieces?

Continue with denormalization to learn when a measured read workload justifies deliberate duplication.