Understanding Table Relationships in Microsoft Access

If you have ever seen an Access database where the same customer name is spelled three different ways, or where deleting an order leaves orphaned line items floating in the database, you have seen what happens when relationships are not set up correctly.

Table relationships are the foundation of relational database design. Get them right and your database will be accurate, efficient, and easy to query. Get them wrong and you will spend years fighting data quality problems.

What Is a Relationship?

A relationship is a link between two tables based on a shared field. The most common type is the one-to-many relationship: one record in Table A can be related to many records in Table B, but each record in Table B is related to exactly one record in Table A.

Example: One customer can have many orders, but each order belongs to exactly one customer.

The link is established through a foreign key: a field in the "many" table that stores the primary key value of the related record in the "one" table.

In the Customers/Orders example:

• Customers table has a primary key: CustomerID
• Orders table has a foreign key: CustomerID (same name, same value, different table)

When you want to know which customer placed order #1042, you look up the CustomerID in the Orders table and find the matching record in the Customers table.

The Three Types of Relationships

One-to-Many (Most Common)

One record in the parent table relates to many records in the child table.

Examples:

• One customer → many orders
• One order → many order lines
• One employee → many time entries
• One product category → many products

This is the relationship type you will use for 80% of your database design.

One-to-One (Rare)

One record in Table A relates to exactly one record in Table B. This is used when you want to split a table with many columns into two tables — usually for performance or security reasons.

Example: A Users table with login information, and a UserProfiles table with detailed personal information. Splitting them means you can query login data without loading profile data.

In practice, one-to-one relationships are uncommon. If you find yourself creating one, ask whether the two tables should simply be merged.

Many-to-Many (Requires a Junction Table)

Many records in Table A can relate to many records in Table B. This cannot be represented directly in a relational database — you need a junction table (also called a bridge table or associative table) in the middle.

Example: Students and courses. One student can enroll in many courses, and one course can have many students.

The junction table (Enrollments) has two foreign keys:

• StudentID (from the Students table)
• CourseID (from the Courses table)

Each row in Enrollments represents one student enrolled in one course. The junction table can also store additional information about the relationship — like the enrollment date or the grade.

Setting Up Relationships in Access

Step 1: Define Primary Keys

Every table needs a primary key — a field (or combination of fields) that uniquely identifies each record. In most cases, use an AutoNumber field named [TableName]ID (e.g., CustomerID, OrderID).

Step 2: Add Foreign Keys

In the "many" table, add a field that will store the primary key value from the "one" table. Use the same data type as the primary key (usually Long Integer for AutoNumber fields).

Step 3: Open the Relationships Window

Go to Database Tools → Relationships. Drag both tables into the window.

Step 4: Create the Relationship

Drag the primary key field from the "one" table to the foreign key field in the "many" table. Access will open the Edit Relationships dialog.

Step 5: Enable Referential Integrity

Check Enforce Referential Integrity. This is the most important setting in the dialog — it prevents orphaned records and ensures your data stays consistent.

With referential integrity enforced:

• You cannot add an order for a customer that does not exist
• You cannot delete a customer who has orders (unless you also check Cascade Delete Related Records)
• Access will warn you if you try to violate these rules

Cascade Options

When you enforce referential integrity, you have two cascade options:

Cascade Update Related Fields: If you change a primary key value in the parent table, Access automatically updates all matching foreign key values in the child table. (This is rarely needed if you use AutoNumber primary keys, since those never change.)

Cascade Delete Related Records: If you delete a record in the parent table, Access automatically deletes all related records in the child table. Use this carefully — it can delete a lot of data silently.

For most databases, the safest approach is to enforce referential integrity without cascade delete. This means you must manually delete child records before you can delete a parent record, which forces you to think about what you are doing.

Normalization: The Theory Behind Good Design

Normalization is the process of organizing your tables to reduce redundancy and improve data integrity. There are several "normal forms," but for most Access databases, you only need to understand the first three.

First Normal Form (1NF)

Each column should contain a single, atomic value. No repeating groups.

Violation: A Products table with columns Tag1, Tag2, Tag3 for product tags.

Fix: Create a separate ProductTags table with one row per tag per product.

Second Normal Form (2NF)

Every non-key column should depend on the entire primary key, not just part of it. (This only applies to tables with composite primary keys.)

Violation: An OrderLines table with primary key (OrderID, ProductID) that also stores CustomerName — which depends only on OrderID, not on the combination.

Fix: Move CustomerName to the Orders table where it belongs.

Third Normal Form (3NF)

Every non-key column should depend directly on the primary key, not on another non-key column.

Violation: An Orders table that stores both CustomerID and CustomerCity — where CustomerCity depends on CustomerID, not directly on OrderID.

Fix: Store CustomerCity only in the Customers table. Retrieve it via a JOIN when needed.

Practical Normalization Tips

Do not over-normalize. The goal is to eliminate redundancy, not to make every query a 10-table join. A few practical guidelines:

• Store each fact once. If the same information appears in multiple tables, it will eventually become inconsistent.
• Use lookup tables for repeated values. If you have a Status field that can be "Active", "Inactive", or "Pending", consider a Statuses lookup table instead of typing those strings everywhere.
• Do not split tables just because you can. If two pieces of information always appear together and always change together, they probably belong in the same table.

Viewing Relationships in Your Database

The Relationships window (Database Tools → Relationships) gives you a visual map of your entire database schema. If you inherited a database and are trying to understand how it works, start here.

You can also use the Database Documenter (Database Tools → Database Documenter) to generate a detailed report of your table structures, relationships, and indexes.

The Payoff

A well-normalized database with properly enforced relationships is dramatically easier to work with than a flat, denormalized one. Your queries are simpler, your forms are more reliable, and your data is more accurate. Every hour you spend getting the relationships right at the design stage saves you ten hours of data cleanup later.