A many-to-many relationship exists when a row in one table has many related rows in a second table. Likewise, those related rows have many rows in the first table. The following figure shows examples of:
An order can contain many items, and an item can appear in many different orders
An employee can work on many projects, and a project can have many employees working on it
Figure 7. Examples of the many-to-many relationship
Accessing information in tables with a many-to-many relationship is difficult and time consuming. For efficient processing, you can convert the many-to-many relationship tables into two one-to-many relationships by connecting these two tables with a cross-reference table that contains the related columns.
For example, to establish a one-to-many relationship between Order and Item tables, create a cross-reference table Order-Line, as shown in the following figure. The Order-Line table contains both the Order Number and the Item Number. Without this table, you would have to store repetitive information or create multiple columns in both the Order and Item tables.
Figure 8. Using a cross-reference table to relate Order and Item tables
An order can contain many items, and an item can appear in many different orders
