Progress
Database Design
Guide
Applying the Principles of the Relational Model
The relational model organizes data in tables and lets you create relationships among tables by referencing columns that are common to both—the primary and foreign keys. It is easiest to understand this concept of relationships between tables with a common business example.
Many businesses need to track information about customers and their orders. So their database probably includes a Customer table and that Customer table might include the Customer Number, Name, Sales Representative, and Postal Code. They might want to uniquely identify each customer, so every customer in the Customer table has a unique Customer Number; every item in the Item table has a unique Item Number. These columns are the primary key columns in each of the named tables. Figure 1–2 shows these four tables.
Figure 1–2: Example of a Relational Database
![]()
These are the tables described in Figure 1–2:
- The Customer table shows four rows, one for each individual customer. Each row has two columns: Cust Num and Name. Each column contains exactly one data value, such as C3 and Jim Cain. The primary key is Cust Num.
- The Order table shows five rows for the orders placed by the customers in the Customer table. Each Order row contains two columns: Cust Num (from the Customer table) and Order Num. The primary key is Order Num. The Cust Num column is the foreign key that relates the two tables. This relationship lets you find all the orders placed by a particular customer, as well as information about a customer for a particular order.
- The Order-Line table shows seven rows for the order-lines of each order. Each order-line row contains three columns: Order Num (from the Order table), Order-Line Num, and Item Num (from the Item table). The primary key is the combination of Order Num, Order Line. The two foreign keys (Order Num and Item Num) relate the Customer, Order, and Item tables so that you can find the following information:
- The Item table shows four rows for each separate item. Each Item row contains two columns: Item Num and Description. Item Num is the primary key.
You want to find out which customers ordered ski boots. To gather this data from your database, you must know what item number identifies ski boots and who ordered them. There is no direct relationship between the Item table and the Customer table, so to gather the data you need, you join four tables (using their primary/foreign key relationships). First you relate the Items to Orders (through Order-Lines) and then the Orders to Customers. Figure 1–3 shows how this works.
NOTE: The figures show the primary key values character data for clarity. A numeric key is better and more efficient.Figure 1–3: Selecting Records from Related Tables
![]()
- First, select the Item table row whose Description value equals ski boots. The Item Number value is I1.
- Next, you want to know the Orders that contain Item I1. But the Order table doesn’t contain Items. It contains Order-Lines. So you first select the Order-Lines that contain I1, and determine the Orders related to these Order-Lines. Orders 01 and 04 contain Item Number I1.
- Now that you know the Order Numbers, you can find out the customers who placed the orders. Select the 01 and 04 orders—determine the associated customer numbers. They are C1 and C3.
- Finally, to find out the names of Customers C1 and C3, you select the Customer table rows that contain customer numbers C1 and C3. Don Smith and Jim Cain—ordered ski boots.
By organizing your data into tables and relating the tables with common columns, you can perform powerful queries. The structures of tables and columns are relatively simple to implement and modify, and the data is consistent regardless of the queries or applications used to access the data.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |