Progress
Database Design
Guide


Many-to-many Relationship

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. Figure 2–5 shows some examples: 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 2–5: 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 to 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 Figure 2–6. 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 2–6: Using a Cross-reference Table to Relate Order and Item Tables


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095