Database normalisation gives each fact a clear home so ordinary changes do not create contradictory copies. The first three normal forms become easier to understand when you follow one order table through its problems.
Begin With the Facts and Their Keys
Imagine an order row containing customer details and a comma-separated list of products. Adding quantities or changing one product becomes awkward. The structure also encourages copying the customer's details into every order.
Before splitting anything, state the business rules. An order has one customer, a product has one current name, and an order can contain several products. Keys identify those things and make the rules testable.
The examples use temporary tables in a fresh session. They illustrate one simple model, not every possible ordering system. Real requirements may include repeated product lines, historical addresses, or negotiated descriptions.
First Normal Form Removes Repeating Lists
For this model, store one product occurrence per row instead of a list inside a column. Use values that the model treats as single values. Avoid Product1, Product2, and Product3 columns that impose an arbitrary limit.
CREATE TABLE #OrderFlat
(
OrderId int NOT NULL,
ProductId int NOT NULL,
OrderDate date NOT NULL,
CustomerId int NOT NULL,
CustomerName nvarchar(80) NOT NULL,
ProductName nvarchar(80) NOT NULL,
Quantity int NOT NULL,
UnitPriceAtSale decimal(10,2) NOT NULL,
PRIMARY KEY (OrderId, ProductId)
);
INSERT #OrderFlat VALUES
(1,10,'20260921',7,N'North Shop',N'Notebook',2,5.00),
(1,20,'20260921',7,N'North Shop',N'Pencil',3,1.00);The composite key expresses the example's rule that a product appears once per order. If repeated lines are allowed, use an appropriate line identifier instead. Normalisation depends on the actual dependencies, not only the column names.
Second Normal Form Uses the Whole Key
In the flat table, OrderDate depends on OrderId alone. ProductName depends on ProductId alone. Quantity and the agreed sale price belong to the complete order-product relationship in this example.
Second normal form removes these partial dependencies of non-key attributes on a candidate key. Move order facts to an order table and product facts to a product table. Keep the relationship facts in the order-line table.
SELECT DISTINCT OrderId, OrderDate, CustomerId, CustomerName
INTO #OrderHeader
FROM #OrderFlat;
SELECT DISTINCT ProductId, ProductName
INTO #Product
FROM #OrderFlat;
SELECT OrderId, ProductId, Quantity, UnitPriceAtSale
INTO #OrderLine
FROM #OrderFlat;SELECT DISTINCT is sufficient for this controlled, internally consistent example. It is not a general migration repair for contradictory source values. Validate dependencies and resolve conflicts before decomposing real data.
Third Normal Form Removes the Indirect Dependency
The order header still stores CustomerName, which depends on CustomerId. Under our rules, the customer's current name is a customer fact. It should not require changing every order whenever the current name changes.
SELECT DISTINCT CustomerId, CustomerName
INTO #Customer
FROM #OrderHeader;
ALTER TABLE #OrderHeader DROP COLUMN CustomerName;
ALTER TABLE #Customer ADD PRIMARY KEY (CustomerId);
ALTER TABLE #Product ADD PRIMARY KEY (ProductId);
ALTER TABLE #OrderHeader ADD PRIMARY KEY (OrderId);
ALTER TABLE #OrderLine ADD PRIMARY KEY (OrderId, ProductId);Third normal form addresses this kind of transitive dependency, with formal rules based on candidate keys and attributes. The practical question is which entity owns each fact. Do not split tables simply to increase their count.
A production design should enforce appropriate foreign keys and other constraints. These temporary tables demonstrate decomposition and primary keys only. SQL Server does not enforce foreign-key constraints on temporary tables.
Reassemble the View Without Duplicating Ownership
SELECT h.OrderId, h.OrderDate, c.CustomerName,
p.ProductName, l.Quantity, l.UnitPriceAtSale,
l.Quantity * l.UnitPriceAtSale AS line_amount
FROM #OrderHeader AS h
JOIN #Customer AS c ON c.CustomerId = h.CustomerId
JOIN #OrderLine AS l ON l.OrderId = h.OrderId
JOIN #Product AS p ON p.ProductId = l.ProductId
ORDER BY h.OrderId, l.ProductId;The join reconstructs a useful reading shape while each current fact retains a clear owner. Suitable indexes support the access pattern. Joins are not evidence that the design is unnecessarily complicated.
The price at sale remains on the line because it is a historical transaction fact. Replacing it with today's product price would change the meaning. Likewise, an invoice address snapshot can be intentional history rather than accidental duplication.
Denormalise for a Measured Reason
A reporting table or cached summary may deliberately repeat data to serve a measured access need. Define the authoritative source, refresh method, and tolerated delay. Also define how to detect and repair inconsistent copies.
Start by checking queries and indexes before assuming duplication is required. When denormalisation helps, document the tradeoff and measure the result. Higher normal forms can matter when additional dependencies exist, so third normal form is not a universal stopping law.
Stop when the model protects the required facts and supports the workload with understandable maintenance. Simplicity includes the cost of keeping copies synchronized. A shorter query is not the only measure of a simpler system.
Normalisation is not a contest to create more tables, it is a way to give each fact a dependable home.
This post was rewritten from scratch in September 2026. The original, published on 2021-02-24, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
