Table Relationships in Access

In this blog I have described use of relationships between tables in Access Database. The main objective to create relationships between tables, to remove data redundancy (duplicate data). To achieve that goal, you divide your data into many subject-based tables so that each fact is represented only once. You then provide Access with a way to bring the divided information back together — you do this by placing common fields in tables that are related. To do this step correctly, though, you have to understand the relationships between your tables, and then specify these relationships in your database.

Types of table relationships

There are three types of table relationships.

One-To-One Relationships

In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints.

One-To-Many Relationships

A one-to-many relationship is the most common type of relationship. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A. For example, the Publishers and Titles tables have a one-to-many relationship: each publisher produces many titles, but each title comes from only one publisher.

A one-to-many relationship is created if only one of the related columns is a primary key or has a unique constraint.

In Access, the primary key side of a one-to-many relationship is denoted by a key symbol. The foreign key side of a relationship is denoted by an infinity symbol.

Many-To-Many Relationships

In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B. For example, the Authors table and the Titles table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the TitleAuthors table. The primary key of the TitleAuthors table is the combination of the au_id column (the authors table’s primary key) and the title_id column (the Titles table’s primary key).

Leave Comment