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
- In an SQL implementation, it is generally more efficient if your DATE, TIME and TIMESTAMP keys use the small exact numeric data types such as BIGINT, INT, SMALLINT and TINYINT.
- In an SQL implementation, for the larger non-numeric keys, including BINARY and VARBINARY, use the data types NUMERIC, CHAR and VARCHAR. However, it may be advantageous to map these to a small exact numeric data type. Do not use inexact numeric data types such as REAL and FLOAT since the results will be non-deterministic.
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 |