Access Basics

Database Normalization: Design Better Access Databases

Poor database design causes data anomalies, redundancy, and maintenance nightmares. Learn normalization principles to build Access databases that last.

A
Anthony Passeri, MBA
••6 min read
Database Normalization: Design Better Access Databases

The most common source of problems in Access databases is not bad queries or slow performance — it is bad design. A database built on a poorly normalized structure will cause data anomalies, update inconsistencies, and maintenance headaches that compound over time. Understanding normalization is the single most valuable skill for any Access developer.

What Is Normalization?

Normalization is the process of organizing a database to reduce redundancy and improve data integrity. It was formalized by Edgar Codd in the 1970s and is expressed as a series of "normal forms" — each one building on the previous.

For practical Access development, you need to understand the first three normal forms: 1NF, 2NF, and 3NF. Achieving 3NF is sufficient for the vast majority of business databases.

The Problem: An Unnormalized Table

Start with a common example — a single table tracking orders:

OrderIDCustomerNameCustomerEmailProductNameProductPriceQuantityOrderDate
1Alice Smithalice@example.comWidget A9.9931/15/2026
2Alice Smithalice@example.comWidget B14.9911/15/2026
3Bob Jonesbob@example.comWidget A9.9921/16/2026

This looks reasonable, but it has serious problems:

  • Alice's email is stored twice — if it changes, you must update multiple rows
  • Widget A's price is stored twice — a price change requires updating every row
  • If you delete Order 3, you lose the information that Bob Jones exists

These are called update anomalies, insertion anomalies, and deletion anomalies — the symptoms of an unnormalized database.

First Normal Form (1NF)

Rule: Each column must contain atomic (indivisible) values, and each row must be unique.

Violations to look for:

  • Columns that contain multiple values (e.g., "Alice Smith, Bob Jones" in one cell)
  • Repeating groups (e.g., Product1, Product2, Product3 as separate columns)
  • No primary key

Fix: Ensure each cell contains exactly one value, and define a primary key.

Our example table already satisfies 1NF — each cell has one value and OrderID is a primary key.

Second Normal Form (2NF)

Rule: Must be in 1NF, and every non-key column must depend on the entire primary key (not just part of it).

This rule only applies when the primary key is composite (made up of multiple columns). If your primary key is a single column (like an AutoNumber), you are automatically in 2NF.

Example of a 2NF violation: An OrderDetails table with a composite key of (OrderID, ProductID):

OrderIDProductIDProductNameQuantity
1101Widget A3
2101Widget A1

ProductName depends only on ProductID, not on the combination of OrderID + ProductID. This is a partial dependency — a 2NF violation.

Fix: Move ProductName to a separate Products table where ProductID is the primary key.

Third Normal Form (3NF)

Rule: Must be in 2NF, and no non-key column should depend on another non-key column (no transitive dependencies).

Example of a 3NF violation: An Orders table:

OrderIDCustomerIDCustomerNameCustomerCityCustomerState
142Alice SmithPhoenixAZ
242Alice SmithPhoenixAZ

CustomerName, CustomerCity, and CustomerState all depend on CustomerID — not on OrderID. This is a transitive dependency.

Fix: Move customer information to a separate Customers table. The Orders table keeps only CustomerID as a foreign key.

The Normalized Design

Applying all three normal forms to our original example gives us:

Customers table:

CustomerIDCustomerNameCustomerEmail
42Alice Smithalice@example.com
43Bob Jonesbob@example.com

Products table:

ProductIDProductNameProductPrice
101Widget A9.99
102Widget B14.99

Orders table:

OrderIDCustomerIDOrderDate
1421/15/2026
2421/15/2026
3431/16/2026

OrderDetails table:

OrderDetailIDOrderIDProductIDQuantity
111013
221021
331012

Now:

  • Alice's email is stored once — update it in one place
  • Widget A's price is stored once — change it in one place
  • Deleting an order does not lose customer or product information

Practical Normalization Tips

Every table should have a primary key. Use an AutoNumber field if no natural key exists.

Repeating groups are a red flag. If you have columns named Month1, Month2, Month3 — or Product1, Product2, Product3 — you need a separate table.

Lookup values belong in their own tables. Status codes, categories, types, and other controlled vocabularies should be in lookup tables, not stored as free text in every row.

Many-to-many relationships need a junction table. If a student can enroll in many courses and a course can have many students, you need a StudentCourses junction table with StudentID and CourseID as a composite key.

When to Denormalize

Normalization is not always the final word. In some cases, deliberately denormalizing — combining tables or storing redundant data — improves performance for read-heavy workloads.

Common denormalization scenarios:

  • Reporting tables that aggregate data for dashboards
  • Archive tables that preserve a snapshot of data at a point in time
  • Calculated fields that are expensive to compute repeatedly

Denormalize intentionally and document why. Never denormalize by accident.

Conclusion

Normalization is the foundation of good database design. A properly normalized database is easier to maintain, less prone to data anomalies, and more flexible as requirements change. Take the time to design your tables correctly before building forms and queries — fixing a poorly normalized database after the fact is far more painful than getting it right from the start.

Work With Access Evolved

If you found this article useful, Access Evolved specializes in exactly this kind of work — from hands-on Microsoft Access support to full database modernization projects.

Want a normalization review of your Access database before migration? Schedule a free assessment and catch design issues before they become migration problems.

Schedule a Free Call →

Thinking about migrating?

Ready to move beyond Microsoft Access?

Access is a great starting point, but growing businesses often hit its limits. Anthony Passeri specializes in migrating Access databases to modern platforms — Power Platform, Power Apps, SQL Server, Azure, and more — so you keep your data and workflows without the headaches.

A

Written by

Anthony Passeri, MBA

Access Expert and Editor

Bridging the gap between Access and the Future

Explore Topics

#normalization#database design#relationships#best practices

Keep reading

More on Access Basics