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 nonkey columns.
The following figure illustrates that the Customer and Order tables are related by a foreign key—the Customer Number.
Figure 4. Relating the Customer table and the Order Table
If the Customer Number is an index in both tables, you can quickly do the following:
Find all the orders for a given customer and query information for each order (such as order date, promised delivery date, the actual shipping date)
Find customer information for each order using an order's customer number (such as name and address)
Find all the orders for a given customer and query information for each order (such as order date, promised delivery date, the actual shipping date)
