Table Relationships and Relationship Types | CustomGuide

Table Relationships and Relationship Types

Understand Table Relationships and Relationship Types in Access

Table Relationships and Relationship Types

Two Types of Databases

There are two basic types of databases:

  • Flat File: A flat-file database stores all its information - names, addresses, etc.— in the same place, just like addresses are stored on a Rolodex card. Flat-file databases are incredibly simple to create and use, but they're not very powerful or well suited to many business tasks.
  • Relational: A relational database contains multiple tables that are related through matching fields. It has two tables — one that stores customer names and addresses, and another, that stores customer orders. The two tables are related or linked by a common field. Relational databases are very powerful, but developing one takes a lot of skill, a lot of practice, and a strong understanding of tables and fields.

Microsoft Access can create either type of database — flat file or relational. However, most Access databases tend to be of the relational type. Below is an example of a relational database that tracks customers and their orders in two separate—but related—tables.

Two Types of Relationships

Relationship Types

Now let’s look at the different types of table relationships. When you link tables together, they form one of three possible relationships.

  • One to One: In a One to One relationship, each record in a table relates to one record in another table. This is the simplest type of relationship, but it doesn’t occur very often because it’s usually easier to store such information in one table instead of two.
    Two Types of Relationships
  • One to Many: In a One to Many relationship, each record in a table relates to one or more records in another table. This is the most common type of relationship.
    Two Types of Relationships
  • Many to Many: In a Many to Many relationship, one or more records in a table relate to one or more records in another table. Many-to-many relationships can be very confusing. To create a many-to-many relationship, use a third intermediate table that contains the primary keys from each of the two tables in the relationship. Such an intermediate table is called a junction table.
    Two Types of Relationships