A bridge table enables you to resolve many-to-many relationships between tables.
For example, you might hold information about employees in an Employees table, and information about the jobs they do in a Jobs table. However, an organization's employees can have multiple jobs, and the same job can be performed by multiple employees. This situation would result in a many-to-many relationship between the Employees table and the Jobs table.
To resolve the many-to-many relationship, you can create a bridge table or intermediate table called Assignments. Each row in the Assignments table is unique, representing one employee doing one job. If an employee has several jobs, there are several rows in the Assignments table for that employee. If a job is done by several employees, there are several rows in the Assignments table for that job. The primary key of the Assignments table is a composite key, made up of a column containing the employee ID and a column containing the job ID.
By acting as a bridge table between the Job and Employee tables, the Assignments table enables you to resolve the many-to-many relationship between Employees and Jobs into:
A one-to-many relationship between Employees and Assignments
A one-to-many relationship between Assignments and Jobs
The image shows a Physical layer view of the example bridge and associated dimension tables.
You should include Weight Factor as an additional column in the bridge table, and to calculating during ETL for efficient query processing.
The following sections explain how to model bridge tables in the Physical and Business Model and Mapping layers:
To model bridge tables in the Physical layer, create joins between the bridge table and the associated dimension tables.
The image shows joins between the example Physical tables in the Physical Diagram.
In the Business Model and Mapping layer, you can choose to model the two dimension tables associated with a bridge table in a single dimension, or in two separate dimensions.
To model the associated dimension tables in one dimension, create a second logical table source that maps to the bridge table and the other dimension table, and then add columns from the other dimension table.
Providing two separate logical table sources makes queries more efficient, because it ensures that queries against a single dimension table do not involve the bridge table.
The image shows the Logical Table Source dialog for the bridge table source.
You can now create dimensions based on your logical tables, including the logical table with the bridge table source.
As an alternative to modeling the two dimension tables associated with a bridge table in a single dimension, you can choose to model them in separate dimensions.
To do this, create a logical join between the fact table and the dimension table that is not physically joined to the fact table, and then modify the logical table source for that same dimension table to add the other table mappings.
The image shows joins between the example logical tables in the Business Model Diagram.
The image shows the Logical Table Source dialog for the modified dimension table source.
You can now create dimensions based on your logical tables, including both logical tables associated with the bridge table.