How to create a bridge table in a Data Warehouse

A bridge table is the correct way to represent a many-to-many relationship in a data warehouse star schema, without duplicating values or inflating totals. The classic example is a bank account that belongs to several customers: if you link the account directly to each customer, the balance gets counted once per holder. The solution is an intermediate table with a weighting factor that shares each value fairly.

Prerequisites

• A relational database server (the examples use SQL Server / T-SQL, but adapt to any engine).
• A data warehouse with at least one fact table and one dimension in a star schema.
• Basic SQL knowledge: CREATE TABLE, INSERT and JOIN.

Step 1: Understand the many-to-many problem

Imagine a fact table fact_saldo_diario at the account grain: each row holds the balance of one account on one day. The problem appears when an account has several holders and you want to analyse the balance per customer. You cannot put the customer in the account dimension (the account would have to repeat for each holder), nor duplicate the fact row (the balance would be summed several times). The bridge table solves this by sitting between the account and the customer, storing each account–customer pair in one row.

Step 2: Create the dimensions and the bridge table

First create the customer dimension and the account dimension, both with surrogate keys. Then create the bridge table, which links the two and includes the fator_peso column — the fraction of the value that belongs to each holder.

CREATE TABLE dim_cliente (
    cliente_sk   INT IDENTITY(1,1) PRIMARY KEY,
    nome         VARCHAR(100)
);

CREATE TABLE dim_conta (
    conta_sk     INT IDENTITY(1,1) PRIMARY KEY,
    numero_conta VARCHAR(20)
);

CREATE TABLE bridge_conta_cliente (
    conta_sk    INT NOT NULL REFERENCES dim_conta(conta_sk),
    cliente_sk  INT NOT NULL REFERENCES dim_cliente(cliente_sk),
    fator_peso  DECIMAL(5,4) NOT NULL,
    CONSTRAINT pk_bridge PRIMARY KEY (conta_sk, cliente_sk)
);

The composite primary key (conta_sk, cliente_sk) prevents duplicate pairs and ensures each holder appears only once per account.

Step 3: Populate the bridge with the weighting factor

The golden rule is simple: for each account, the sum of the fator_peso values of its holders must equal 1. An account with two holders splits the balance into 0.5 + 0.5; with a single holder, the weight is 1. The example below assumes account 1 has two holders and account 2 has only one.

INSERT INTO bridge_conta_cliente (conta_sk, cliente_sk, fator_peso) VALUES
    (1, 10, 0.5),   -- account 1, holder 10
    (1, 11, 0.5),   -- account 1, holder 11
    (2, 12, 1.0);   -- account 2, single holder

The fator_peso is also known as the allocation factor. Storing it in the bridge, rather than in the query, ensures everyone applies the same sharing rule.

Step 4: Query the facts with the weighting factor

To get the balance per customer without inflating totals, join the fact table to the bridge and then to the customer dimension, always multiplying the measure by fator_peso. That multiplication is what avoids double counting.

SELECT
    c.nome,
    SUM(f.saldo * b.fator_peso) AS saldo_alocado
FROM fact_saldo_diario AS f
JOIN bridge_conta_cliente AS b
    ON b.conta_sk = f.conta_sk
JOIN dim_cliente AS c
    ON c.cliente_sk = b.cliente_sk
GROUP BY c.nome;

Check the result

Run two quick checks. First, confirm that each account's weights add up to exactly 1 — no account may be short or over:

SELECT conta_sk, SUM(fator_peso) AS total_peso
FROM bridge_conta_cliente
GROUP BY conta_sk
HAVING SUM(fator_peso) <> 1;

If this query returns no rows, all the weights are correct. Second, compare the total allocated per customer (using fator_peso) with the original account total: the two figures must be equal. If they match, there is no double counting.

Conclusion

With a bridge table and a weighting factor you can now analyse many-to-many relationships without inflating totals — an essential technique in dimensional modelling. From here, try different weights (for example, a primary holder with 0.7 and a secondary one with 0.3) or add start and end dates to treat the bridge as a relationship that changes over time. And if a customer has several accounts, how would you handle the analysis in the opposite direction?