|
Table
|
Index name
|
Index column(s)
|
Primary
|
Unique
|
|
Customer
|
cust-num
|
cust-num
|
YES
|
YES
|
|
–
|
Why the index is defined:
 Rapid access to a customer given a customer's number.
Reporting customers in order by number.
Ensuring that there is only one customer row for each customer number (uniqueness).
Rapid access to a customer from an order, using the customer number in the order row.
|
|||
|
name
|
name
|
NO
|
NO
|
|
|
Why the index is defined:
Rapid access to a customer given a customer's name.
Reporting customers in order by name.
|
||||
|
zip
|
zip
|
NO
|
NO
|
|
|
Why the index is defined:
Rapid access to all customers with a given zip code or in a zip code range.
Reporting customers in order by zip code, perhaps for generating mailing lists.
|
||||
|
Item
|
item-num
|
item-num
|
YES
|
YES
|
|
–
|
Why the index is defined:
Rapid access to an item given an item number.
Reporting items in order by number.
Ensuring that there is only one item row for each item number (uniqueness).
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 is defined:
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 column alone needs to be unique.
Rapid access to all of the order-lines for an order, ordered by line number.
|
|||
|
item-num
|
item-num
|
NO
|
NO
|
|
|
Why the index is defined:
Rapid access to all the order-lines for a given item.
|
||||
|
Order
|
order-num
|
order-num
|
YES
|
YES
|
|
–
|
Why the index is defined:
Rapid access to an order given an order number.
Reporting orders in order by number.
Ensuring that there is only one order row for each order number (uniqueness).
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 is defined:
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.
Ensuring that there is only one row for each customer/order combination (uniqueness).
Rapid access to the order numbers of a customer's orders.
|
||||
|
order-date
|
order-date
|
NO
|
NO
|
|
|
Why the index is defined:
Rapid access to all the orders placed on a given date or in a range of dates.
|
||||