Design trade-offs

Constructing a logical data model and physical warehouse schema is an iterative process of compromises and trade-offs. The following diagram shows the three major requirements that must be balanced to create an effective system.

Each of these categories affects the others. If you try to satisfy every single user requirement from the simplest to the most complex, you will have to create an extensive data model and schema to support those requirements. This results in an increased load on the warehouse, slower query performance, and greater maintenance for the database administrator. You must decide which factors are most important in your particular environment and weigh them against the other factors.

For example, if you have the storage space necessary to accommodate data in a star schema it may seem that you would never want to normalize your schema. However, SQL queries directed at a consolidated table require the use of a DISTINCT operator and these types of queries tend to be very expensive in terms of database resources and processing time. The use of a resource-intensive DISTINCT query could therefore negate any performance gain achieved by reducing the number of joins between higher-level lookup tables.

In addition to the previous points, you may need higher level lookup tables to take advantage of aggregate tables, which are discussed in Using summary tables to store data: Aggregate tables.

For more comparisons between the different schema types described in this chapter, see the following section Schema type comparisons.