A relationship is a conceptual "association among things". Some people make the mistake of thinking that the word "relational" in relational database management refers to "relationships" or associations between tables. That is not the case. The relational model is named after the mathematical concept of a RELATION, which has nothing to do with relationships between data in different tables. Relationships are a semantic issue and are not a specific feature of relational databases.

However, there are certain design patterns that are commonly used for representing conceptual relationships in a database and this article attempts to describe a few basic examples of these.

In the design and planning phase (see Database Planning and Design) of database development, the developer first determines the most appropriate database structure for the database. At this point the database design will include a detailed list of the tables that the database will include along with a detailed field list for each table. With complete table and field information in hand, the developer will turn to determining table relationships for the database.

Types of Relationships

The three types of relationships are #One to Many, #One to One, #Many to Many. The relationships in a relational database resemble the relationships between the real world objects that the database's tables represent. In Access, relationships are defined between pairs of tables using the relationship window. Each relationship involves primary key of one table and a foreign key or the primary key of the second table.

One to Many

A one to many relationship exists when one record in the primary table (the primary key side of the relationship) corresponds to many records in the related table (the foreign key side of the relationship.) When Referential Integrity is enforced, records can be created in the foreign key side of the relationship only when the corresponding record already exists in the primary table.

Example Invoice data is typically structured as two tables, an invoice header table, and an invoice details table. The header table contains data to identify the customer to whom goods have been sold and the date of the sale, for example. The invoice details table contains data related to the products and quantities of those products included in the sale that the invoice represents. Keep in mind that some of the data in these table will simply be foreign keys pointing to yet additional primary tables.

So in this example, the primary table is the invoice header table. The related table is the invoice details table which will have one record for each item (line item) sold on this invoice. The relationship is one to many -- one invoice header record to many invoice details records. Taken literally, the term many can be a bit deceptive because there may not always be more than one details record (more than one being a loose definition of many.) In fact, in data management terms many refers to the possibilty or expectation of multiple records but, at least in Access, it is possible to have a primary record with no corresponding records in the related table. For example, you may have created an invoice header record and then the customer decided not to complete the purchase.

One to One

One to one relationships are the least common of all three relationship types. This type of relationship can be used when only some primary records need certain data. In the invoice example above, you may need the ability to have notes about some invoices but do not expect to make notes about every invoice. A one to one relationship includes the primary key of one table and a unique field in the related table.

In this example the invoice note primary key field is also being used as a foreign key pointing back to the primary table. The field InvNoteId will store the value of the primary key of the related invoice. An alternative approach is to use an additional field in the related table that is uniquely indexed. Because the indexes on both sides of the relationship are unique the relationship is one to one.

Many to Many

A many to many relationship exists between two tables when each record in the first table can have many related records in the second table, while at the same time, each record in the second table can have many related records in the first. A typical many to many relationship exists, for example, between employees and projects in a work setting. Each employee can be assigned to many different projects. Each project employs several employees.

From a structural standpoint a many to many relationship differs from a one to many relationship because the foreign keys are stored outside the regular data tables in a third table, known as a junction table. If you look carefully at the example diagram, you may notice that there are actually two one to many relationships, one between employees and the junction table, and one between projects and the junction table.

In this example, the Project Employees table has a compound, or multi-field primary key. It is the uniqueness of the combination of employee and project ids that prevents the duplicate assignment of the same employee to the same project.

Analyzing Table Relationships

The fields you use to make the relationship connection are really secondary to first clearly defining the actual relationship in terms of how one table is related to another.

Keep in mind that each relationship in the database refers to a single pair of table. To define the relationships, work through the list of tables one pair at a time. For each pair, write a statement that indicates how table a is related to table b, another statement that indicates how table b is related to table a, and a final statement that concludes what type the relationship is.

For example, determining the relationship between personnel (people) and jobs might go like this:

1. Each person can have (one/many) job(s).
2. Each job can have (one/many) person (people).
3. Therefore the relationship is (one person to many jobs/one job to many people/many people to many jobs, one job to one person).

In the third statement only one phrase will apply. So the possibilities are:

One to Many

1. Each person can have one job.
2. Each job can employ many people.
3. Therefore the relationship is one person to many jobs.

(If by job you really mean assignment or task):

1. Each person can have many jobs.
2. Each job can employ one person.
3. Therefore the relationship is one job to many people.

(if people are multi-tasking and jobs require teams):

Many to Many

1. Each person can have many jobs.
2. Each job can employ many people.
3. Therefore there relationship is many people to many jobs.

(finally if each person has only one job and each job employs only one person):

One to One

1. Each person can have one job.
2. Each job can employ one person.
3. Therefore there relationship is one person to one job.

A many to many relationship requires the use of a third table (called a junction table) to define the relationship. The relating fields would be stored in the junction table rather than in the job or people table in this example. One the other hand in one to many, and one to one relationships, the relating fields are stored in the respective tables.

It is not likely that a one to one relationship would exist between jobs and people. However, a possible example might come up if you wanted to maintain a progress note about some jobs. If you were planning to have a progress note for all (most) jobs then you might add a note field to the jobs table. On the other hand, if you were planning to have a progress note for only some jobs then you might use a separate notes table. Keep in mind, however, that if you make the jobs-notes relationship one to one, then you would be able to have only a single note for each job.

While analyzing relationships can be tedious (you have to look at one pair of tables at a time) and time-consuming, it is an essential part of the design process. It is important to test relatationship definitions against the enterprise's business rules. If the relationship definitions do not reflect how a business operates in reality, the database will be unable to provide a workable solution for the business problem it is intended to solve.

Only when you have the relationships properly defined and established in the database can you start to look at use forms to maintain the data.

Implementing Table Relationships

A relationship is implemented in Access by connecting one or more fields in one table to one or more fields. The field or fields used are referred to as the primary key or foreign key depending on whether the key comes from the one side (unique)or the many side of the relationship. In the case of a one to one relationship both keys are unique. Access provides the relationship window for creating and displaying the relationships that exist in the database.

Access 2003 and Prior

In Access 2003 and earlier, open the relationships window from the Tools/Relationships menu.

Access 2007 and Later

In Access 2007 and later use the Relationships shortcut on the Database Tools ribbon tab

Demonstration

See Also: Record Key

(work in progress feb 20/2010)