Normalization

In simple words#

Learning objectives#

• Explain 1NF, 2NF and 3NF
• Spot redundancy and anomalies
• Split a flat table into related tables
• Know when to denormalize

Explanation#

First Normal Form (1NF): each column holds a single value and there are no repeating groups such as Phone1, Phone2, Phone3.

Second Normal Form (2NF): in a table with a composite key, every non-key column depends on the whole key — not just part of it.

Third Normal Form (3NF): non-key columns depend only on the key, not on other non-key columns (City should not determine State inside Orders).

Denormalization deliberately duplicates data for read speed — common in reporting databases — but adds the work of keeping copies in sync.

Code example: From a flat sheet to ShopDB#

1-- ✗ Flat design: customer and product details repeated on every line
2-- OrderLines(OrderId, OrderDate, CustomerName, CustomerCity, ProductName, Category, Price, Qty)
3

4-- ✓ Normalized (3NF)
5CREATE TABLE Customers (CustomerId INT PRIMARY KEY, Name NVARCHAR(60), City NVARCHAR(40));
6CREATE TABLE Products  (ProductId INT PRIMARY KEY, Name NVARCHAR(60), Category NVARCHAR(30), Price DECIMAL(10,2));
7CREATE TABLE Orders    (OrderId INT PRIMARY KEY, CustomerId INT REFERENCES Customers, OrderDate DATE);
8CREATE TABLE OrderItems(OrderId INT REFERENCES Orders, ProductId INT REFERENCES Products,
9                        Qty INT, UnitPrice DECIMAL(10,2), PRIMARY KEY (OrderId, ProductId));

Each customer, product and order is stored once. Changing a customer’s city is now a single-row update instead of editing every past order line.

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

1. In the flat design, Asha’s city appears on every line she ever ordered — an update anomaly waiting to happen.
2. Customers and Products move to their own tables (removing repeated groups and partial dependencies).
3. OrderItems keeps only facts about the order line: quantity and the price at the time of sale.
4. UnitPrice is intentionally stored on the line because the product price may change later — that is history, not redundancy.

Real-world use case#

Common mistakes#

Best practices#

• Aim for 3NF in transactional databases
• Store historical values (like UnitPrice) where they belong
• Denormalize only with a measured reason
• Use views to present convenient, joined shapes

Remember this#

Practice exercise#

1It breaks 1NF (repeating group). Create Enrollments(StudentId, CourseId) with one row per student-course pair.

Mini quiz#

Want more? Practise all SQL Server MCQs · SQL Server interview questions

Related topics#

My notes#

Saved in this browser.