Keys to join the fact table with the dimension tables

Each dimensional table needs to include a primary key that corresponds to a foreign key in the fact table. The fact table should have a primary (composite) key that is a combination of the foreign keys.

Assume that the following schema of shows both the logical and physical design of the database.

Figure 1. The Sales fact table references each dimension table

The "Sales Fact table" is in the center of the diagram. Lines illustrate the joins from the fact table to the four dimension tables: product, time, geography, customer. The reminder of the diagram is described in the surrounding text.

The database contains the following five tables:

Sales fact table
Product dimension table
Time dimension table
Customer dimension table
Geography dimension table

Each of the dimensional tables includes a primary key (product, time_code, customer, district_code), and the corresponding columns in the fact table are foreign keys. The fact table also has a primary (composite) key that is a combination of these four foreign keys. As a rule, each foreign key of the fact table must have its counterpart in a dimension table.

Additionally, any table in a dimensional database that has a composite key must be a fact table. This means that every table in a dimensional database that expresses a many-to-many relationship is a fact table. Therefore a dimension table can also be a fact table for a separate star schema. This type of dimensional database model is referred to as a snowflake schema.

Tip: The primary key should be a short numeric data type (INT, SMALLINT, SERIAL) or a short character string (as used for codes). Do not use long character strings as primary keys.