Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 36 ยท Design and Normalization

Normalization in Access

Normalize an Access database step by step. See update, insert and delete anomalies, then fix a flat orders table with 1NF, 2NF and 3NF, and learn when to denormalize.

Normalization is the process of organizing tables so each fact is stored exactly once. It is the single most useful theory idea for Access developers because it explains why a design works and tells you how to repair one that does not. You do not need math: three plain rules, applied in order, handle nearly every business database. (For the SQL-wide view of the topic see SQL normalization; this lesson uses an Access-style order database.)

Why a flat table fails

Beginners often put everything in one table, exactly like a spreadsheet:

OrderID OrderDate CustomerName CustomerCity Items
1 2025-03-01 Ana Silva Lisbon Notebook x10, Pen x20
2 2025-03-09 Ana Silva Lisbon Desk x1
3 2025-03-10 Li Wei Beijing Notebook x5

This causes three kinds of anomalies:

  • Update anomaly: Ana moves to Porto, so you must change every one of her rows. Miss one and the database now disagrees with itself.
  • Insert anomaly: you cannot record a new customer who has not ordered yet, since there is no order row to put them in.
  • Delete anomaly: deleting order 3, Li's only order, also erases the fact that Li exists.

Also the Items cell holds a list, which you cannot sort, count or total with a query. Normalization removes all of these.

First normal form (1NF): atomic values, no repeating groups

Rules: every cell holds one value, there are no repeating columns (Item1, Item2, Item3) and each row can be identified. Split the Items list into separate rows, one per order line:

OrderID OrderDate CustomerName CustomerCity Product Qty
1 2025-03-01 Ana Silva Lisbon Notebook 10
1 2025-03-01 Ana Silva Lisbon Pen 20
2 2025-03-09 Ana Silva Lisbon Desk 1

The key is now the pair (OrderID, Product). Order data is repeated, but each cell is atomic and queries can finally filter and total the products.

Second normal form (2NF): no partial dependencies

Rule: a table with a composite key must have every non-key field depend on the whole key. Here OrderDate, CustomerName and CustomerCity depend only on OrderID, not on the product. They are repeated for every line. Move them out:

  • Orders(OrderID, OrderDate, CustomerName, CustomerCity)
  • OrderLines(OrderID, Product, Qty)

Quantity depends on the whole key (this product on this order), so it stays. If the key is a single column, 2NF is automatically satisfied.

Third normal form (3NF): no transitive dependencies

Rule: non-key fields must depend on the key and nothing else. In Orders, CustomerCity depends on the customer, who is not the key. Move customer facts to their own table and keep only a foreign key:

  • Customers(CustomerID, CustomerName, City)
  • Orders(OrderID, OrderDate, CustomerID)
  • Products(ProductID, ProductName, Price)
  • OrderLines(OrderID, ProductID, Qty)

A memorable summary: every non-key field depends on the key, the whole key and nothing but the key.

flowchart LR
  A["Flat table<br/>lists in cells"] -->|1NF atomic values| B["One row per line"]
  B -->|2NF whole key| C["Orders + OrderLines"]
  C -->|3NF nothing but the key| D["Customers, Orders,<br/>Products, OrderLines"]

Each form removes one kind of redundancy.

Seeing it in SQL

Run the flat version and see the anomaly, then the normalized version and see it disappear:

SQL Update anomaly: flat versus normalized

Press Run (or Ctrl + Enter) to see the result here.

Normalizing an existing table with queries

You can fix a flat imported table without retyping it. Create the lookup tables from the distinct values, then rebuild the references with joins. The pattern (Access SQL uses INSERT INTO ... SELECT DISTINCT, which is the same here):

SQL Split a flat table into related tables

Press Run (or Ctrl + Enter) to see the result here.

In Access you can also use Create/Database Tools > Analyze Table, a wizard that proposes this split for a flat table and creates the new tables and a lookup query for you. Review its suggestions carefully; it can pick odd groupings.

Beyond 3NF and when to denormalize

Higher forms exist: Boyce-Codd normal form (every determinant is a candidate key), 4NF (no independent multi-valued facts mixed in one table) and 5NF. In practice, 3NF is the target for transactional databases, and a quick check for 4NF is to ask whether one table lists two unrelated repeating lists of the same person (skills and languages), which should be two tables.

Sometimes you denormalize on purpose: a reporting table that copies customer names for speed, or UnitPrice copied into OrderLines so that an old invoice does not change when the product price changes. The difference between a deliberate copy and an accident is that you know why it is there and keep it consistent. Never denormalize before you have a measured problem.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A cell that contains the text 'Notebook x10, Pen x20' violates:

    1NF requires atomic values: one value per cell and no lists.
  2. 2. In OrderLines(OrderID, ProductID, Qty, OrderDate), OrderDate depends only on OrderID. This is a violation of:

    OrderDate depends on part of the composite key, so it belongs in Orders.
  3. 3. CustomerCity stored in Orders (which has CustomerID) is a problem because:

    The city depends on CustomerID, which is not the key of Orders: a 3NF violation.
  4. 4. Which is a sound reason to copy UnitPrice into OrderLines?

    A deliberate, documented copy preserves history on past invoices.

Summary

  • Normalization stores each fact once, preventing update, insert and delete anomalies.
  • 1NF: atomic values; 2NF: depend on the whole key; 3NF: depend on nothing but the key.
  • Split tables with INSERT INTO ... SELECT DISTINCT queries, or the Analyze Table wizard.
  • Denormalize only deliberately (history, reporting) and keep the copy consistent.

Next: Keys, indexes and data integrity