Table Normalization Example (Decomposing Customer Orders into Three Tables)

The Starting Point — The Unnormalized Customer Orders Table

The Decomposition Decision — Three Tables

SQL CREATE TABLE — Building the Normalized Schema

CREATE TABLE Customers (
    CustID   CHAR(6)      NOT NULL,
    Name     VARCHAR(50)  NOT NULL,
    City     VARCHAR(50)  NOT NULL,
    CONSTRAINT pk_customers PRIMARY KEY (CustID)
);

CREATE TABLE Items (
    Item_ID      CHAR(6)        NOT NULL,
    Description  VARCHAR(100)   NOT NULL,
    UnitPrice    DECIMAL(10,2)  NOT NULL,
    CONSTRAINT pk_items PRIMARY KEY (Item_ID)
);

CREATE TABLE Orders (
    OrderLineID  INT            NOT NULL AUTO_INCREMENT,
    CustID       CHAR(6)        NOT NULL,
    Item_ID      CHAR(6)        NOT NULL,
    Qty          INT            NOT NULL,
    CONSTRAINT pk_orders     PRIMARY KEY (OrderLineID),
    CONSTRAINT fk_cust       FOREIGN KEY (CustID)   REFERENCES Customers (CustID),
    CONSTRAINT fk_item       FOREIGN KEY (Item_ID)  REFERENCES Items (Item_ID)
);

The Diagram — Visualizing the Decomposition

The Lossless JOIN — Proving the Decomposition Works

SELECT
    c.CustID,
    c.Name,
    c.City,
    i.Item_ID,
    i.Description,
    o.Qty,
    (o.Qty * i.UnitPrice) AS Total
FROM Orders o
JOIN Customers c ON o.CustID   = c.CustID
JOIN Items     i ON o.Item_ID  = i.Item_ID
ORDER BY c.CustID, i.Item_ID;

The Single-Source Update Benefit

-- Unnormalized: must touch three rows simultaneously
UPDATE CustomerOrders
SET City = 'Denver'
WHERE CustID = '000001';

-- Normalized: one row, one update, guaranteed consistency
UPDATE Customers
SET City = 'Denver'
WHERE CustID = '000001';

Recognizing a Normalized Table

Applying the 1NF Check to the Three Tables

First Normal Form requires that every column contain atomic values — single, indivisible values of a consistent type — and that there are no repeating groups of columns. Check each of the three tables:

The Customers table has three columns: CustID (a single fixed-length identifier), Name (a single name string), and City (a single city name). Every cell contains one atomic value. No repeating groups. The Customers table satisfies 1NF.

The Items table has three columns: Item_ID (single identifier), Description (single product name), and UnitPrice (single decimal value). Every cell contains one atomic value. No repeating groups. The Items table satisfies 1NF.

The Orders table has four columns: OrderLineID (single auto-generated integer), CustID (single foreign key value), Item_ID (single foreign key value), and Qty (single integer). Every cell contains one atomic value. No repeating groups. The Orders table satisfies 1NF.

Applying the 2NF Check

Second Normal Form requires that every non-key attribute be fully functionally dependent on the entire primary key — no partial dependencies on part of a composite key. Check each table:

The Customers table has a single-column primary key (CustID). With a single-column key, partial dependency is impossible by definition — there is no "part of the key" to depend on partially. Name and City both depend fully on CustID. The Customers table satisfies 2NF.

The Items table has a single-column primary key (Item_ID). Description and UnitPrice both depend fully on Item_ID. The Items table satisfies 2NF.

The Orders table has a single-column primary key (OrderLineID). CustID, Item_ID, and Qty all depend fully on OrderLineID — knowing the order line ID tells you exactly which customer, which item, and what quantity. The Orders table satisfies 2NF.

Applying the 3NF Check

Third Normal Form requires that no non-key attribute be transitively dependent on the primary key through another non-key attribute. Check each table:

The Customers table: does City depend on Name rather than directly on CustID? No — two customers could share the same name but live in different cities. City depends directly on CustID, not transitively through Name. The Customers table satisfies 3NF.

The Items table: does UnitPrice depend on Description rather than directly on Item_ID? No — two items could have the same description at different price points. UnitPrice depends directly on Item_ID. The Items table satisfies 3NF.

The Orders table: does Qty depend on CustID or Item_ID rather than directly on OrderLineID? No — Qty is the quantity for this specific order line, identified by OrderLineID. It does not depend on the customer or the item independently. The Orders table satisfies 3NF.

The Practical Recognition Test

All three tables pass the 1NF, 2NF, and 3NF checks. The schema is normalized to the Third Normal Form — the standard target for transactional database design. The practical recognition test that confirms normalization is complete is this: can you change any single fact in the database by updating exactly one row in exactly one table? If yes, the schema is normalized. If changing a fact requires updating multiple rows, there is redundancy that normalization has not yet eliminated.

Apply this test to the three tables. Changing Smith's city: one row in Customers. Changing the price of Green Widgets: one row in Items. Recording a new order for Smith buying Blue Widgets: one INSERT into Orders. Recording a new customer: one INSERT into Customers. Each operation touches exactly one row in exactly one table. The schema passes the practical recognition test.

Table Normalization - Exercise