Page 26 of 29PREVNEXT

Table that data

A many-to-many table relationship

Callout 1 Primary key from the Orders table.
Callout 2 Primary key from the Products table.
Callout 3 One order can have many products ...
Callout 4 ... and each product can appear on many orders.

In a many-to-many relationship, a record in one table relates to multiple records in a second table, and a record in the second table relates to multiple records in the first table.

This type of relationship requires a third table, called a junction table. The junction table contains the primary keys from the other two tables as its foreign keys.

For example, the Orders table and the Products table have a many-to-many relationship. One order can contain many products, and each product can appear in many orders.

The third table — in this example, the Order Details table — contains primary keys from the Orders and Products tables. The Order Details table is the junction table for the other two.

Because each single item in the Orders table and each single item in the Products table can appear many times in the Order Details table, each of those two tables has a one-to-many relationship with the Order Details table. All junction tables connect tables that have one-to-many relationships with the junction tables, and the primary keys of those connected tables are the foreign keys of the junction table.

When one table can relate to many others simply by referencing information in them, the parts of your database are like the cells of your body. Each is active, each is the right size for its business, and together they can achieve effects vastly beyond their individual abilities.

Page 26 of 29PREVNEXT