Database Design Tips

The database design has a large influence on performance when the system is in operation. Many performance problems can be avoided if you observe the following tips when designing your database:

?     Before you define tables, analyze the expected data.

?     When defining key columns, place those columns that are especially selective and for which users frequently enter search conditions at the beginning of the key. In many cases, the database system will then only have to search a small part of the table when processing a SELECT statement.

?     Only invert columns with high selectivity.

Do not choose columns such as gender or marital status as indexes. As a rule, such columns have very few different values and the database system can only seldom use them for non-sequential searches.

?     In the case of relatively static datasets, you can invert a large number of tables. When defining the key columns at the beginning of the index, use selective columns that users frequently use in equality conditions.

?     Do not invert all columns which users use in search conditions. The space needed for the indexes and the cost of maintaining them is considerable.

See also:

Performance