About Bridge Tables

Use a bridge table (or intermediate table) to resolve many-to-many relationships between tables.

For example, suppose there is an Employee table that contains information about employees, and a Jobs table that contains information about the jobs the employees perform. An organization's employees can have multiple jobs, and the same job can be performed by multiple employees. This results in a many-to-many relationship between the Employees table and the Jobs table.

For this scenario, you create a bridge table named Assignments to resolve the many-to-many relationship. 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 performed 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

You should include Weight Factor as an additional column in the bridge table, and to calculating during ETL for efficient query processing.