Designing Relational Databases Using Microsoft Access

Designing relational tables is the foundation of every well-structured database. Before Access can generate useful queries, forms, or reports, you must decide how to organize your information into tables and how those tables will relate to each other. A relational database works best when each table represents a single subject, and the subjects connect through well-chosen key fields.

In this lesson, you will learn how to determine the fields needed in your database, how to decide which tables should exist, and how to create relationships that allow Access to combine information across multiple tables. The goal is to avoid redundancy, reduce maintenance effort, and support accurate and flexible reporting.

Understanding What to Store

A good design begins with a clear understanding of your data. For each business process—customers placing orders, employees receiving benefits, computers being assigned to staff—you identify:

1. The entities (people, items, orders, departments, computers)
2. The attributes describing each entity (name, date, cost, address, quantity)
3. The relationships between them (one customer can have many orders; one computer can have multiple software packages)

Before deciding how many tables you need, think ahead about the analysis you plan to perform. Access queries and reports rely on properly related tables. Missing or duplicated fields often lead to inconsistent results and unnecessary maintenance. When in doubt, examine examples of similar datasets, this often clarifies how data should be grouped.

Why Data Is Split Across Multiple Tables

Relational databases intentionally divide data across multiple tables to reduce duplication. If the same details are stored in several places (customer email in every order record, for example), changes become difficult and errors more likely. Instead, each “fact” should live in exactly one table. Relationships—implemented through primary keys and foreign keys—allow Access to bring the information back together whenever needed.

The following examples illustrate how different real-world scenarios benefit from organized table structures.

Example: Splitting Customer, Order, and Item Data

Example: Tracking Computers and Software in an Organization

Another common use of relational design is IT inventory. A single table containing every detail—computer IDs, user names, software lists, locations—quickly becomes unmanageable. Instead, each subject becomes its own table, and relationships tie them together.

Example: Organizing Employee and HR Information

Human Resources departments maintain a variety of employee-related information. Not all of this data belongs in a single table, especially when access requirements differ (e.g., salary vs. basic employee information). Splitting data allows teams to manage sensitive details separately.

Conclusion

Across all these examples, the design principle remains the same: each table should represent a single subject, and related subjects connect through key fields. This structure avoids duplication, protects data integrity, and enables Access to generate accurate forms, queries, and reports. When you design tables thoughtfully, the entire database becomes easier to maintain and far more reliable.

The next lesson explains how Access stores data inside a table—the most fundamental database object.

Database Concepts - Quiz