PHYSICAL DATABASE DESIGN - Fundamentals of Database Management Systems - page 222

Physical design categories and techniques that DO NOT change
the logical design
• Adding External Features
■ Adding Indexes
■ Adding Views
• Reorganizing Stored Data
■ Clustering Files
• Splitting a Table into Multiple Tables
■

Horizontal Partitioning

Vertical Partitioning

■

Splitting-Off Large Text Attributes

■

Physical design categories and techniques that DO change
the logical design
• Changing Attributes in a Table
■ Substituting Foreign Keys
• Adding Attributes to a Table
■ Creating New Primary Keys
■ Storing Derived Data
• Combining Tables
■ Combine Tables in One-to-One Relationships
■ Alternatives for Repeating Groups
■ Denormalization
• Adding New Tables
■ F I G U R E 8.20

Physical database design categories and
techniques

Duplicating Tables

Adding Subset Tables

■

Adding Indexes Since the name of the game is performance and since today's
business environment is addicted to finding data on a direct-access basis, the use of
indexes in relational databases is a natural. There are two questions to consider.
The first question is: which attributes or combinations of attributes should you
consider indexing in order to have the greatest positive impact on the application
environment? Actually, there are two sorts of possibilities. One category is attributes
that are likely to be prominent in direct searches. These include:

■ Primary keys.
■ Search attributes , i.e. attributes whose values you will use to retrieve particular
records. This is true especially when the attribute can take on many different
values. (In fact, there is an argument that says that it is not beneficial to build an
index on an attribute that has only a small number of possible values.)

The other category is attributes that are likely to be major players in operations
such as joins that will require direct searches internally. Such operations also include