Objectives of Database Normalization

Example: customer, order, and item facts in one table

The original teaching data below records four orders and their items. Its dates and abbreviated contact details are illustrative. Each displayed row contains one item, but the business permits an order to contain several different items.

Assume each order belongs to one customer, each item occurs at most once per order, and the customer details represent current information. Title is the item's current title, Price is the price charged on that order line, and Shipped describes that line's shipping status. Under these assumptions, the candidate key is (OrderNo, ItemNo); the four currently distinct order numbers do not establish that OrderNo alone is a key.

This table is already presented in 1NF form: there are no numbered repeating item groups or lists of values in its cells. Its width is not the reason to decompose it. The problem is that attributes depend on different identifiers, causing facts to repeat across rows.

Objective 1: reduce unnecessary repetition

Customer 101's name, address, and phone appear on orders 101 and 102. Customer 102's details appear on orders 103 and 104. Item 105's title appears for two different orders. These are repeated descriptions of the same customers and item.

The relevant dependencies are:

CustID → CustName, Address, Phone
OrderNo → OrderDate, CustID
ItemNo → Title
(OrderNo, ItemNo) → Price, Shipped

Each dependency expresses a business rule, not merely a pattern in this sample. Two identical prices for item 105 do not prove that ItemNo determines its sale price; another order could receive a discount.

Objective 2: prevent update anomalies

If customer 101 changes their current phone number, both of that customer's rows need updating. A missed row leaves conflicting contact information. More orders mean more places where the same fact must be maintained.

A Customer table keyed by CustID gives the current phone number one place to be updated. Orders continue to reference customer 101, so queries can retrieve the new number without changing every order line.

Objective 3: allow facts to be inserted independently

If customer and item information exists only in this combined table, recording a new customer or item requires an order-line row. Required order identifiers prevent adding the independent fact on its own.

Separate Customer and Product tables allow the business to register a customer before their first purchase and add an item before its first sale. Normalization aligns the storage structure with those independent lifecycles.

Objective 4: avoid accidental loss of information

Suppose the row for order 104 is removed. It is the only displayed row containing item 109 and the title DX. In the combined design, deleting that order line also loses the item's description if no other copy exists.

When product details live in Product, removing an order line leaves the product intact. Foreign-key deletion rules still need deliberate configuration: normalization alone does not decide whether deleting a customer should be blocked, cascaded, or handled through another policy.

Separate the facts while retaining their connections

A suitable design for the stated rules is:

Customer(CustID, CustName, Address, Phone)
Product(ItemNo, Title)
Orders(OrderNo, OrderDate, CustID)
OrderLine(OrderNo, ItemNo, Price, Shipped)

Primary keys:
Customer: CustID
Product: ItemNo
Orders: OrderNo
OrderLine: (OrderNo, ItemNo)

Foreign keys:
Orders.CustID → Customer.CustID
OrderLine.OrderNo → Orders.OrderNo
OrderLine.ItemNo → Product.ItemNo

Notice that every attribute from the original table has a home, including Shipped. Customer and product descriptions are separated from the order-line facts; their identifiers remain as references.

Moving attributes that depend on only part of the composite order-line key addresses 2NF. Separating customer details from order details also removes the transitive dependency through CustID, producing a 3NF design under these rules. These are distinct dependency checks, even when a designer performs the decompositions together.

If Shipped describes an entire order instead, it belongs in Orders. If partial shipments must be tracked, a separate shipment structure may be necessary. Likewise, a historical delivery address is a different fact from a customer's current address. Define the meaning of each attribute before deciding where it belongs.

Objective 5: preserve information and enforce the rules

A decomposition must support a lossless join: reconstructing the combined information must neither lose valid associations nor introduce false combinations. Here each order line joins to one order, one product, and the order's one customer through their keys.

Preserving dependencies also matters. Rules should remain enforceable in the resulting relations where possible. Primary keys prevent duplicate identifiers or pairs; foreign keys protect references; nullability and other constraints express additional requirements. Normalization helps create a sound structure, but it cannot guarantee correct input or replace those constraints.

How the normal forms support these objectives

• 1NF: use single values from the appropriate domains and avoid repeating groups in the conventional relational model.
• 2NF: be in 1NF and remove dependencies of non-prime attributes on a proper subset of any candidate key. A non-prime attribute belongs to no candidate key.
• 3NF: for each nontrivial dependency X → A, require X to be a superkey or A to be prime. A superkey uniquely identifies rows; a prime attribute belongs to a candidate key. The customer-details chain is a common violation this rule addresses.

Check all candidate keys, not just the chosen primary key. Adding a surrogate identifier does not remove the dependencies among the other attributes.

Normalization and performance are different questions

Normalization improves the structure for maintaining facts, but it does not guarantee faster queries or minimum storage. Additional joins may have a cost, while narrower rows and fewer repeated updates may provide benefits. Evaluate the actual workload before changing a sound design for performance.

Indexes, query changes, caching, and materialized summaries may address measured bottlenecks. Deliberate denormalization requires a documented source of truth and a reliable way to keep repeated data consistent. It should be a justified trade-off rather than an assumption that fewer tables must be better.

Check your understanding

For each table, ask: What does one row represent? Which attributes determine the others? Can an independent fact be added or removed safely? Can the decomposed tables reconstruct the original relationships?

Review redundancy and repeated values or foreign keys if needed, then try the Normalization Process Quiz. Continue to first normal form for the next lesson.