SQL for .NET developers Beginner

Database design and normalization

Normalization is how you split data into tables so each fact is stored once. This lesson walks a messy spreadsheet-style table through first, second and third normal form, shows the bugs that duplication causes, and explains when a little denormalization is fine.

From one sheet to normalized tables
  1. StartOne big sheetCustomer, product and order data repeated in every row
  2. Step 21NFOne value per cell: split product lists into rows
  3. Step 32NFMove product names to Products, keyed by ProductId
  4. Step 43NFMove customer details to Customers
  5. EndLinked tablesEach fact stored once, joined by keys

The problem with one big table

Imagine an Orders sheet with the columns OrderId, CustomerName, CustomerEmail, CustomerCity, Products ("Pen, Notebook") and Total. It works for ten rows. With ten thousand, the same customer's email appears in hundreds of rows. Change it in one row and forget the others, and your data now disagrees with itself.

These are called update, insert and delete anomalies: you cannot add a customer without an order, and deleting the last order deletes the customer.

The normal forms in plain words

  • 1NF: every cell holds one value. No lists like "Pen, Notebook" and no repeating columns like Product1, Product2.
  • 2NF: every column depends on the whole key. In OrderLines(OrderId, ProductId, ProductName) the name depends only on ProductId, so it moves to Products.
  • 3NF: no column depends on another non-key column. CustomerCity depends on the customer, not on the order, so it moves to Customers.
  • A short way to remember 3NF: every column depends on the key, the whole key, and nothing but the key.

The result

The sheet becomes four tables. Each fact lives in exactly one place and is linked by keys.

SQL
Customers  (CustomerId PK, Name, Email UNIQUE, City)
Products   (ProductId PK, Name, Price)
Orders     (OrderId PK, CustomerId FK, OrderedAt)
OrderLines (OrderId FK, ProductId FK, Quantity, UnitPrice,
            PRIMARY KEY (OrderId, ProductId))

-- UnitPrice is copied on purpose: it records the price at the
-- time of the order, which is a different fact from today's price.

When to denormalize

Normalized tables are ideal for writing. Reports that join eight tables on millions of rows can be slow, so it is fine to keep a deliberate copy for reading: a stored Total on Orders, an indexed view, or a separate reporting table refreshed overnight.

The rule is to denormalize on purpose, for a measured reason, and to keep one table as the source of truth. Never denormalize by accident.

Interview question

What is the difference between 2NF and 3NF?

2NF removes partial dependencies: in a table with a composite key, no column may depend on only part of the key. 3NF removes transitive dependencies: no non-key column may depend on another non-key column, for example City depending on CustomerId through the customer rather than on the order. A table in 3NF is also in 2NF.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.