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:

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

  1. First, select the Item table row whose Description value equals ski boots. The Item Number value is I1.
  2. 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.
  3. 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.
  4. 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