Access Database Relationships

Relationships define a database. The power of a database lies in the way the tables are related to each other. Nearly every database that contains multiple tables also includes relationships between these different sets of information. There are three types of table relationships: one-to-many, many-to-many, and one-to-one. Let's learn about each type.

For Microsoft Access classes in Los Angeles call us on 888.815.0604.

One-to-Many

Consider the following database, which contains two tables:

This database has a table of Employees and a table of Expenses. As time goes by, the Expenses table could grow to become quite large.

Now imagine that you want to find out the phone number of every employee that submitted an expense. It is impractical to place the employee phone number in the same table as the expenses because it will create a lot of unnecessary and duplicate data. This is particularly true if there is another table containing employee data elsewhere.

Therefore, a relationship between the two tables can be created between the two common fields: Employee ID. The Employees table and the Expenses table are then in a one-to-many relationship, meaning that one entry in the Employees table can relate to many entries in the Expenses table. In other words, one employee can log many expenses.

Many-to-Many

Consider a database that contains two separate tables: Orders and Products. While a single order can include more than one product, a single product can appear on many orders. Therefore, for each record in the Orders table, there can be many records in the Products table. In addition, for each record in the Products table, there can be many records in the Orders table. This type of relationship is called a many-to-many relationship because, for any product, there can be many orders and, for any order, there can be many products.

In order to represent a many-to-many relationship, a third table will need to be created. Such tables are often called junction tables, and they break down the many-to-many relationship into two separate one-to-many relationships. In our example, the Orders table and the Products table have a many-to-many relationship that is defined by creating two one-to-many relationships to the Order Details table. One order can have many products, and each product can appear on many orders.

One-to-One

A one-to-one relationship requires each record in one table to have only one matching record in a second. Due to these constraints, one-to-one relationships are relatively rare, as information that is related in this way is most often stored in the same table. Most often, such relationships are used to divide and isolate parts of a table for security purposes.

Summary

When designing a database, relationships are frequently the most challenging step and often the place where most of the confusion with databases arises. However, as with most things, proper planning can make a world of difference.