Progress
Database Design
Guide


Indexes Used in the Sports Database

Table 4–1 lists some indexes defined in the sports database, showing why the index was defined.

Table 4–1: Reasons for Defining Some Sports Database Indexes
Table
Index Name
Index Column(s)
Primary
Unique
Customer
cust-num
cust-num
YES
YES

Why the Index Was Defined:

  1. Rapid access to a customer given a customer’s number.
  2. Reporting customers in order by number.
  3. Ensuring that there is only one customer row for each customer number (uniqueness).
  4. Rapid access to a customer from an order, using the customer number in the order row.
name
name
NO
NO

Why the Index Was Defined:

  1. Rapid access to a customer given a customer’s name.
  2. Reporting customers in order by name.
zip
zip
NO
NO

Why the Index Was Defined:

  1. Rapid access to all customers with a given zip code or in a zip code range.
  2. Reporting customers in order by zip code.
Item
item-num
item-num
YES
YES

Why the Index Was Defined:

  1. Rapid access to a item given an item number.
  2. Reporting items in order by number.
  3. Ensuring that there is only one item row for each item number (uniqueness).
  4. Rapid access to an item from an order-line, using the item-num column in the order-line row.
Order-line
order-line
order-num
line-num
YES
YES

Why the Index Was Defined:

  1. Ensuring that there is only one order-line row with a given order number and line number. The index is based on both columns together since neither alone need to be unique.
  2. Rapid access to all of the order-lines for an order, ordered by line number.
item-num
item-num
NO
NO

Why the Index Was Defined:

  1. Rapid access to all the order-lines for a given item.
Order
order-num
order-num
YES
YES

Why the Index Was Defined:

  1. Rapid access to a order given an order number.
  2. Reporting orders in order by number.
  3. Ensuring that there is only one order row for each order number (uniqueness).
  4. Rapid access to an order from an order-line, using the order-num column in the order-line row.
cust-order
cust-num
order-num
NO
YES

Why the Index Was Defined:

  1. Rapid access to all the orders placed by a customer. Without this index, all of the records in the order file would be examined to find those having a particular value in the cust-num column.
  2. Ensuring that there is only one row for each suterom/order combination (uniqueness).
  3. Rapid access ot the order numbers of a customer’s orders.
order-date
order-date
NO
NO

Why the Index Was Defined:

  1. Rapid access to all the orders placed on a given date or in a range of dates.


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