Bridge database table relationship

Chris Adamson’s Blog: Bridge Tables and Many-to-many Relationships

bridge database table relationship

An associative entity is a term used in relational and entity–relationship theory. A relational database requires the implementation of a base relation (or base table) to resolve many-to-many relationships. under many names, including association table, bridge table, cross-reference table, crosswalk, intermediary table. A bridge table enables you to resolve many-to-many relationships between tables. The key difference between a bridge table and a fact table is that the bridge table relationship is mandatory. The bridge table relationship restricts the data from.

Bridge tables

The image shows joins between the example Physical tables in the Physical Diagram. 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.

Drag objects from the Physical layer to the Business Model and Mapping layer, except the bridge table and the associated dimension table that is not joined to the fact table.

database design - Bridge table question - Database Administrators Stack Exchange

For the example described in the previous sections, you would drag all objects except for the Assignment and Employee tables. In the Business Model and Mapping layer, right-click the dimension table that is joined to the fact table, Jobs in this example, and select New Object, then select Logical Table Source.

bridge database table relationship

In the Logical Table Source dialog, provide a name for the new bridge table source. It is a good practice to use the bridge table name as the name of the source, for example, Assignment. Click the Add button in the upper right corner of the Logical Table Source dialog.

The bridge table allows us to link the repeating dimension to the facts, but as noted in the post, care must be taken not to double count.

But we are not discussing entity-relationship modeling.

Associative entity - Wikipedia

In a dimensional model, this kind of relationship is allowed. Despite what you may think, it is also compatible with your relational database management system. The figure above can be implemented as a physical design, and it will work.

  • Data Modeling - Many-to-many Relationship
  • Modeling Bridge Tables

Unfortunately, some of our software tools do not understand this. Tool Problems Many of the software products we use in data warehousing were originally developed for transaction processing systems.

bridge database table relationship

Because of this lineage, the many-to-many relationship can be a problem. This is a problem for the model pictured above. But it is not a primary key in either table.

bridge database table relationship

Luckily, this join will work even if it is not supported by any key constraints. Unfortunately, other tools may not be as forgiving.

ERD Connecting a Bridge table to another Bridge table

Some data modeling tools do not permit a many-to-many relationship in a physical model. This presumably reflects their entity-relationship lineage.

And if you use a SQL-generating BI tool to create reports, you may find it identifies bridge table as fact tables. Analytic expressions are not supported and will generate the following errors.

Query 'Query1' contains data item 'theRank' which has an analytic aggregate expression, this is not supported when the query references a bridge query subject.

Associative entity

Query 'Query1' contains data item 'nextRow' which has running aggregate expression, this is not supported when the query references a bridge query subject. Cross tab reports have very limited support when including a bridge query.

Here are the main errors generated in that context.

bridge database table relationship

The query is not supported: Query 'Query1' contains an explicit aggregate expression which is not supported in a Relational crosstab. Reports created against a DMR package.

bridge database table relationship

In general, the whole query must be pushed to the relational provider which is where the bridge table logic has been implemented. If the query requires local OLAP processing, then the bridge functionality cannot be applied and the following error is generated.