Three types of relationships can exist
between tables in a database: one to many, one to one, and many to
many. Setting up the proper type of relationship between two tables in
a database is imperative. The right type of relationship between two
tables ensures
The reasons behind these benefits are covered
throughout this hour. Before you can understand the benefits of
relationships, though, you must understand the types of relationships
available.
One-to-Many Relationships
A one-to-many relationship is by far the most common type of relationship. In a one-to-many relationship,
a record in one table can have many related records in another table. A
common example is a relationship set up between a Customers table and
an Orders table. For each customer in the Customers table, you want to
have more than one order in the Orders table. On the other hand, each
order in the Orders table can belong to only one customer. The
Customers table is on the “one” side of the relationship, and the
Orders table is on the “many” side. For you to implement this
relationship, the field joining the two tables on the “one” side of the
relationship must be unique.
In the Customers and Orders tables example, the
CustomerID field that joins the two tables must be unique within the
Customers table. If more than one customer in the Customers table has
the same customer ID, it is not clear which customer belongs to an
order in the Orders table. For this reason, the field that joins the
two tables on the “one” side of the one-to-many relationship must be a
primary key or have a unique index. In almost all cases, the field
relating the two tables is the primary key of the table on the “one”
side of the relationship. The field relating the two tables on the
“many” side of the relationship is the foreign key.
One-to-One Relationships
In a one-to-one relationship, each record in the
table on the “one” side of the relationship can have only one matching
record in the table on the “many” side of the relationship. This
relationship is not common and is used only in special circumstances.
Usually, if you have set up a one-to-one relationship, you should have
combined the fields from both tables into one table. The following are
the most common reasons to create a one-to-one relationship:
The
maximum number of fields allowed in an Access table is 255. There are
very few reasons a table should ever have more than 255 fields. In
fact, before you even get close to 255 fields, you should take a close
look at the design of the system. On the rare occasion when having more
than 255 fields is appropriate, you can simulate a single table by
moving some of the fields to a second table and creating a one-to-one
relationship between the two tables.
The second situation in which you would want to
define one-to-one relationships is when you will use certain fields in
a table for only a relatively small subset of records. An example is an
Employees table and a Vesting table. Certain fields are required only
for employees who are vested. If only a small percentage of a company’s
employees are vested, it is not efficient, in terms of performance or
disk space, to place all the fields containing information about
vesting in the Employees table. This is especially true if the vesting
information requires a large number of fields. By breaking the
information into two tables and creating a one-to-one relationship
between the tables, you can reduce disk-space requirements and improve
performance. This improvement is particularly pronounced if the
Employees table is large.
Many-to-Many Relationships
In a many-to-many relationship,
records in two tables have matching records. You cannot directly define
a many-to-many relationship in Access; you must develop this type of
relationship by adding a table called a junction table.
You relate the junction table to each of the two tables in one-to-many
relationships. For example, with an Orders table and a Products table,
each order will probably contain multiple products, and each product is
likely to be found on many different orders. The solution is to create
a third table, called OrderDetails. You relate the OrderDetails table
to the Orders table in a one-to-many relationship based on the OrderID
field. You relate it to the Products table in a one-to-many
relationship based on the ProductID field.
