Progress
Database Design
Guide
Keys
There are two types of keys: primary and foreign. A primary key is a column (or group of columns) whose value uniquely identifies each row in a table. Because the key value is always unique, you can use it to detect and prevent duplicate rows. A good primary key has these characteristics:
- It is mandatory; that is, it must store non-null values. If the column is left blank, duplicate rows can occur.
- It is unique. For example, the social security column in an Employee or Student table is a good key because it uniquely identifies each individual. The Cust Number column in the Customer table uniquely identifies each customer. It is less practical to use a person’s name because more than one customer might have the same name. Also, databases do not detect variations in names as duplicates (for example, Cathy for Catherine, Joe for Joseph). Furthermore, people do sometimes change their names (for example, through a marriage or divorce).
- It is stable; that is, it is unlikely to change. As in the previous example, the social security number is a good key not only because it uniquely identifies each individual, but it is also unlikely to change, while a person’s or customer’s name might change.
- It is short; that is, it has few characters. Smaller columns occupy less storage space, database searches are faster, and entries are less prone to mistakes. For example, a social security column of 9 digits is easier to access than a name column of 30 characters.
NOTE: You can also have non-unique keys and word indexes.A foreign key is a column value in one table that is required to match the column value of the primary key in another table. In other words, it is the reference by one table to another. If the foreign key value is not null, then the primary key value in the referenced table must exist. It is this relationship of a column in one table to a column in another table that provides the relational database with its ability to join tables. "Table Relationships and Normalization" describes this concept in more detail.
A composite key is a key composed of multiple columns.
Indexes
An index in a database operates like the index tab on a file folder. It points out one identifying column, such as a customer’s name, that makes it easier and faster to find the information you want.
When you use index tabs in a file folder, you use those pieces of information to organize your files. If you index by customer name, you organize your files alphabetically. If you index by customer number, you organize them numerically. Indexes in the database serve the same purpose.
You may use a single column to define a simple index, or a combination of columns to define a compound index. To decide which columns to use, you determine how the data in the table will be accessed. If users frequently look up customers by last name, then the last name is a candidate for an index. It is typical to base indexes on primary keys (columns that contain unique information).
An index has these advantages:
- Faster row search and retrieval. It is more efficient to locate a row by searching a sorted index table than by searching an unsorted table.
- In a 4GL implementation, records are ordered automatically to support your particular data access patterns. No matter how you change the table, when you browse or print it, the rows appear in indexed order instead of their stored physical order on disk.
- When you define an index as unique, each row is unique. This ensures that duplicate rows do not occur. A unique index may contain nulls. However, a primary key, although unique, may not contain nulls.
- A combination of columns can be indexed together to allow you to sort a table in several different ways at once (for example, sort the Projects table by a combined employee and date column).
- Efficient access to data in multiple related tables.
- When you design an index as unique, each key value must be unique.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |