Link table

The previous data model scenario is a typical scenario that occurs due to the presence of multiple fact tables. In this case, Orders and SalesTarget are two fact tables and they share common dimensions. Star schema prefers one single fact table in the schema. This scenario can be resolved with the help of a link table.

A link table is a central table that will contain common fields from the two tables and therefore it creates one table and avoids synthetic keys.

Link table essentials

The following steps are followed in the creation of any link table:

1. Create a composite key in all the concerned fact tables. Use the Autonumber function to make this key unique and numeric. This composite key will act as a key field to connect the link table with fact tables.
2. Load all the common fields from all the fact tables in one table called the link table. Use concatenate for this purpose.
3. Drop these common fields in the original fact table.

Creating a link table

In our data model we have two fact...