Bridge table

What is a bridge table?

A bridge table is an intermediate table that resolves a many-to-many relationship. In a star schema, a fact row normally points to one row in each dimension: one order has one customer, one product, one date. But some relationships do not fit that pattern. One bank account can have several account holders. One hospital stay can have several diagnoses. One product can belong to several categories.

A single foreign key cannot point to several dimension rows. The bridge table sits in the middle and stores the valid pairs: account 1 belongs to customer 91 and customer 92; account 2 belongs to customer 92; admission 5 has diagnosis A, B, and C.

That turns one awkward many-to-many relationship into two ordinary one-to-many relationships with the bridge in between.

When do you need a bridge table?

You need a bridge table when one row on either side can relate to several rows on the other side.

• Accounts and customers: a joint account has several holders, and a customer can hold several accounts.

• Admissions and diagnoses: one hospital stay can have multiple diagnoses, and each diagnosis appears on many stays.

• Products and categories: one product can sit in several categories, and each category contains many products.

• Orders and salespeople: one order may be credited to several salespeople, and each salesperson works on many orders.

In dimensional modelling, this pattern often appears as a multivalued dimension. The bridge records the membership of the group without forcing extra columns such as Diagnosis1, Diagnosis2, Diagnosis3, which would break as soon as the count changes.

The double-counting problem

A bridge table makes the relationship possible, but it also introduces a reporting trap. When a fact row joins to multiple bridge rows, the measure is repeated once per match.

Suppose a hospital stay costs 3,000 euros and has three diagnoses. If you group cost by diagnosis through the bridge, the join attaches the same 3,000 euros to each diagnosis. A naive sum now shows 9,000 euros. The stay did not become three times more expensive. The model repeated it three times.

The usual solution is a weighting or allocation factor in the bridge table. If the stay has three diagnoses and you want an equal split, each bridge row gets a factor of one third. The report multiplies cost by the factor before summing by diagnosis. The three allocated amounts add back up to the original 3,000 euros.

Not every bridge needs a factor. If you only count relationships, the pair rows may be enough. If you allocate additive measures across the many side, decide the rule and store it explicitly.

The grain of a bridge table

The grain of a bridge table is usually one relationship. One row says that one item is linked to one other item for a given scope.

That scope matters. Is the account-customer relationship valid forever, or only between start and end dates? Are diagnoses linked to the admission, to the invoice, or to individual treatment lines? Does the sales allocation apply to the order header or each order line?

A bridge table is only clean when its grain is clean. If one row sometimes means relationship and sometimes means relationship for a period, filters and totals become hard to trust.

Bridge tables in Power BI

Power BI can model many-to-many relationships directly, but a dedicated bridge table often gives more control. Microsoft's guidance for many-to-many dimensions uses a bridging table with one-to-many relationships on each side, and a deliberate filter direction so filters can reach the fact table.

That filter direction is the part to handle carefully. Bi-directional filters can solve a bridge-table problem, but they can also create ambiguous paths and surprising totals when used broadly. Keep the bridge small, keep relationships explicit, and test totals at the all-up level and at the filtered level.

If users see totals that do not add up across categories, the model may still be correct. Many-to-many measures are often non-additive: the same account, patient, or product can appear under more than one label. Explain that in the measure design and report labels.

Bridge table versus related patterns

Bridge table versus dimension table
A dimension describes an entity, such as a customer or product. A bridge table describes a relationship between entities. It usually contains keys and relationship attributes, not business descriptions.

Bridge table versus factless fact table
A bridge with only keys can behave like a factless fact table: it records that a relationship exists, without a numeric measure. The terms overlap depending on the modelling tradition.

Bridge table versus snowflake schema
Snowflaking normalises a dimension into smaller one-to-many tables. A bridge table solves a many-to-many relationship. They are different problems.

What to watch out for with bridge tables

Double counting
Always test additive measures after joining through the bridge. If a measure repeats, add an allocation rule or change the report question.

Ambiguous filters
Multiple paths between tables can make Power BI or SQL results confusing. Keep relationship paths intentional.

Changing relationships
If membership changes over time, store effective dates or version the bridge. Otherwise old facts may be analysed with today's relationships.

Hidden business rules
An equal weighting factor is easy, but it may not be fair. Sales credit, diagnosis cost, and account ownership often need business-approved allocation logic.