Progress
Database Design
Guide


Table Relationships

In a relational database, tables relate to one another by sharing a common column or columns. This column, existing in two or more tables, allows the tables to be joined. When you design your database, you define the table relationships based on the rules of your business. (The relationship is frequently between primary and foreign key columns; however, tables can also be related by other non-key columns.)

In Figure 2–1, the Customer and Order tables are related by a foreign key, Customer Number.

Figure 2–1: Relating the Customer and Order Tables

If the Customer Number is an index in both tables, you can do the following very quickly:

NOTES

Figure 2–2 shows the relationship between the Customer and the Order tables.

Figure 2–2: Relationship Between the Customer and Order Tables

From A, the Customer’s view point, the relationship from Customer to Order is one-to-many because one customer can have many orders (as shown by the double arrows).

From B, the Order’s view point, the relationship from Order to Customer is one-to-one because one order corresponds to exactly one customer (as shown by the single arrow).

C is the summary of the relationships from Customer to Order and from Order to Customer.

The following sections explain the three types of table relationships—one-to-one, one-to-many, and many-to-many-in greater depth.


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