Designing Tables and Indexes for Performance › Indexing › Efficient Database Design

Efficient Database Design

When designing your database, for tables that will contain a significant amount of data and considerable DML activity, aim to have only one index per table. This practice provides for an efficient database design. Each index would need to support the following:

• Insert strategy
• Primary key
• Foreign key (if a child table)
• SQL access path
• Clustering

Note the following design objectives:

• Avoid surrogate keys. Use meaningful business keys instead.
• Let the child table inherit the parent key as part of the child's primary key.
• Cluster all tables in a common sequence.
• Determine the common access paths, respect them, and try not to change them during design.
• Never entertain a just in case type of design mentality.