Physical Design of Data Warehouse: Constraints and Indexes

An appropriate logical design of the data model for the star schema is the prerequisite for a good data mart. However, another important task is the physical design of the database objects. In addition to the dimension and fact tables, we need to take care of constraints and indexes on the tables.

Constraints

Database constraints ensure data integrity within the tables and the validity of the relationships between the tables. Additionally, they are useful for documentation of the data model and can be used by the optimizer to in some cases to improve query performance. However, constraints can have a negative effect in load performance. The different types of constraints and how they are used in a data warehouse environment are described here.

Primary Key Constraints:

Each dimension table in a star schema must contain a primary key. The primary key column is usually a numeric surrogate key. Natural keys or even multi-column primary keys should be avoided. The only exception is the calendar dimension.

Here it is common in Oracle databases to use the date as primary key of the dimension table. The benefit is that the date dimension key in the fact table can be used easily as a partition key.