Link Tables
The Link Table (also known as a Bridging Table) is a replacement for a Synthetic Key table
The Link Table tends to contain:
- A Primary Key
- Usually a composite key made of 2+ columns from source tables to identify a record
- The individual source columns used in the compose key which then associate to the dimenson tables in the model
- A static column that identifies the source table name the key and the column(s) are loaded from
The Link Table is good for the following situations:
- Preventing wide tables that are the result of concatenation
- There can also be multiple fact tables in a model with this solution
- This results from concatenation
- Can be used to solve Many to Many data relationships between subjects
Link Tables disadvantages:
- Can be complex to code
- Increases the number of tables in the model
- Potential for performance degradation
Link Tables are typically only introduced in the visualization app(s), not within the scripting ETL apps