Скачать в pdf «PostgreSQL»

•    Numbers require less storage space than character strings.

In the statename table, the two-letter state code is probably a good join key for the following reasons:

•    Two-letter codes are easy for users to remember and enter.

•    State codes are always unique.

•    State codes do not change.

•    Joins of short two-letter codes are not significantly slower than integer joins.

•    Two-letter codes do not require significantly more storage space than integers.

Essentially, two choices for join keys exist: identification numbers and short character codes. If an item is referenced repeatedly, it is best to use a short character code as a join key. You can display this key to users and allow them to refer to customers and employees using codes. Users prefer to identify items by short, fixed-length character codes containing numbers and letters. For example, customers might be identified by six-character codes (FLE001) employees by their initials (BAW) and parts by five-character codes (e7245). Codes are easy to use and remember. In many cases, users can choose the codes, as long as they are unique.

It is possible to allow users to enter short character codes and still use identification numbers as join keys. Adding a code column to the table accomplishes this goal. For the customer table, a new column called code can be added to hold the customer code. When the user enters a customer

SELECT order_id

FROM customer, salesorder

WHERE customer.code = ‘FLE001’ AND

customer.customer_id = salesorder.customer_id;

Скачать в pdf «PostgreSQL»