Bridge table: model many-to-many in a star schema

When a customer can hold several accounts and the same account can belong to several customers, the classic star schema no longer suffices: linking the two dimensions directly creates a many-to-many relationship that causes double counting in your sums and undermines the reliability of your reports. The didactic, battle-tested answer from the Kimball methodology is the bridge table, which connects the two dimensions and distributes the values with a weighting factor. Below we build one, step by step, in SQL, and show how to query it without inflating the totals.

Prerequisites

• A relational DBMS (the examples use SQL Server, but the pattern applies to any engine).
• A star schema with at least one fact table and two dimensions.
• Basic knowledge of CREATE TABLE, INSERT and JOIN.

Step 1: Understand the many-to-many problem

Picture a bank: account C-100 is a joint account held by Ana and Bruno. If you link the account dimension directly to the customer dimension and sum the balance per customer, the €1,000 balance of account C-100 counts €1,000 for Ana and €1,000 for Bruno — a total of €2,000 that does not exist. This is the classic double-counting error, and it happens because the same fact row is read by several customers. The bridge table fixes it by recording, for each account–customer pair, what fraction of the value belongs to each one.

Step 2: Create the dimensions

We start with two simple dimensions. Each one has its own surrogate key, the column the bridge and the facts will join on.

CREATE TABLE DimCliente (
    cliente_key   INT PRIMARY KEY,
    nome          VARCHAR(100)
);

CREATE TABLE DimConta (
    conta_key     INT PRIMARY KEY,
    numero_conta  VARCHAR(20),
    tipo          VARCHAR(30)
);

Step 3: Create the bridge table

The bridge table has one row per account–customer pair. The fator_peso column is the heart of the pattern: it stores the fraction of the value that belongs to each customer. We place the weight in the bridge, not in the fact table, so the same fact row can be read in several ways without being duplicated. For an account with two holders, each row gets 0.5.

CREATE TABLE PonteContaCliente (
    conta_key     INT NOT NULL,
    cliente_key   INT NOT NULL,
    fator_peso    DECIMAL(9,4) NOT NULL,
    CONSTRAINT PK_Ponte PRIMARY KEY (conta_key, cliente_key)
);

Golden rule: the sum of the fator_peso values for a given account must always equal 1.0. That is what guarantees the totals add up.

Step 4: Populate the bridge with the weighting factor

The joint account C-100 (conta_key 1) has two holders, so each takes 0.5. The individual account C-200 (conta_key 2) has a single holder, with a factor of 1.0. Notice how, within each account, the factors add up to exactly 1.0.

INSERT INTO PonteContaCliente (conta_key, cliente_key, fator_peso) VALUES
    (1, 10, 0.5),   -- C-100: Ana
    (1, 20, 0.5),   -- C-100: Bruno
    (2, 10, 1.0);   -- C-200: Ana

Step 5: Query through the bridge

The FactSaldo fact table stores the balance at account level (one record per account). To get the balance per customer without double counting, we cross the bridge and multiply the balance by the fator_peso.

SELECT
    c.nome,
    SUM(f.saldo * p.fator_peso) AS saldo_atribuido
FROM FactSaldo AS f
JOIN PonteContaCliente AS p ON p.conta_key = f.conta_key
JOIN DimCliente        AS c ON c.cliente_key = p.cliente_key
GROUP BY c.nome;

The big advantage is flexibility: if, instead of the split value, you want the total balance of the accounts each customer can access, simply drop the fator_peso from the sum. The same model answers both questions — how much belongs to each person, and which values each person can reach.

Verify the result

With a balance of €1,000 in C-100 and €500 in C-200, the weighted query returns Ana = €1,000 (€500 from C-100 plus €500 from C-200) and Bruno = €500. The grand total is €1,500, exactly the bank's real balance — with no inflation. If the numbers do not add up, first check whether the fator_peso values for each account sum to 1.0; that is almost always where the error is.

Conclusion

With a bridge table and a weighting factor, you have solved a many-to-many relationship without losing detail or inflating the totals — the foundation of reports you can trust. The next step is to apply the same pattern to other cases, such as products with several categories or projects with several teams, deciding case by case when to use the weighted sum. Which many-to-many relationship in your model is waiting for a bridge?