Database Reference
In-Depth Information
Figure 4-9.
The OLAP design
Foreign Keys
Foreign key relations exist between the tables, but the lack of lines connecting the columns indicate that we have
not placed a foreign key constraint on the tables. This can cause confusion, so let's elaborate on this feature.
A foreign key
column
is a single column where the data represents values from another table. A foreign key
constraint
stops you from putting data in a foreign key column that does not exist in the original column you are
referencing. Many developers who do not work with database development on a daily basis get the two terms
confused. Most database administrators would shudder at the idea of not putting foreign key constraints in an
OLTP database, yet it is not nearly as common to include them in an OLAP database.
The argument against putting them in the OLAP database is that the data has already been validated in the
original OLTP database, so why validate it again? The argument for placing foreign key constraints in the OLAP
database is that it is considered cheap insurance. It is a type of fail-safe. If someone imports data that is somehow
incorrect, the foreign key constraints will catch it as an error. We prefer using foreign key constraints in both styles
of databases, but we are not doing so for this example to provide additional contrast for learning purposes.
Missing Features
Foreign key constraints are a common feature of most databases. The example in Figure
4-9
is also missing
some other common features. These features include several different types of dimensions, such as a time
