Bridge table: modelar muitos-para-muitos no star schema

Quando um cliente pode ter várias contas e a mesma conta pode pertencer a vários clientes, o modelo em estrela clássico deixa de chegar: ligar as duas dimensões diretamente cria uma relação muitos-para-muitos que provoca dupla contagem nas somas e mina a fiabilidade dos relatórios. A solução didática e testada pela metodologia Kimball é a bridge table (tabela-ponte), que liga as duas dimensões e distribui os valores com um fator de peso. A seguir criamos uma, passo a passo, em SQL, e mostramos como consultá-la sem inflacionar os totais.

Pré-requisitos

• Um SGBD relacional (os exemplos usam SQL Server, mas o padrão aplica-se a qualquer motor).
• Um star schema com, pelo menos, uma tabela de factos e duas dimensões.
• Conhecimentos básicos de CREATE TABLE, INSERT e JOIN.

Passo 1: Perceber o problema muitos-para-muitos

Imagine um banco: a conta C-100 é uma conta conjunta da Ana e do Bruno. Se ligar a dimensão de contas diretamente à dimensão de clientes e somar o saldo por cliente, o saldo de 1000 € da conta C-100 conta 1000 € para a Ana e 1000 € para o Bruno — um total de 2000 € que não existe. Este é o erro clássico de dupla contagem, e acontece porque a mesma linha de facto é lida por vários clientes. A bridge table resolve o problema ao registar, para cada par conta–cliente, que fração do valor pertence a cada um.

Passo 2: Criar as dimensões

Começamos com duas dimensões simples. Cada uma tem a sua própria chave substituta (surrogate key), que é a coluna pela qual a ponte e os factos se vão ligar.

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)
);

Passo 3: Criar a bridge table

A tabela-ponte tem uma linha por cada par conta–cliente. A coluna fator_peso é o coração do padrão: guarda a fração do valor que pertence a cada cliente. Colocamos o peso na ponte, e não na tabela de factos, para que a mesma linha de facto possa ser lida de várias formas sem ser duplicada. Para uma conta com dois titulares, cada linha recebe 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)
);

Regra de ouro: a soma dos fator_peso de uma mesma conta deve dar sempre 1,0. É isso que garante que os totais fecham.

Passo 4: Popular a ponte com o fator de peso

A conta conjunta C-100 (conta_key 1) tem dois titulares, por isso cada um leva 0,5. A conta individual C-200 (conta_key 2) tem um só titular, com fator 1,0. Repare como, em cada conta, os fatores somam exatamente 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

Passo 5: Consultar através da bridge

A tabela de factos FactSaldo guarda o saldo ao nível da conta (um registo por conta). Para obter o saldo por cliente sem dupla contagem, atravessamos a ponte e multiplicamos o saldo pelo 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;

A grande vantagem é a flexibilidade: se, em vez do valor repartido, quiser saber o saldo total das contas a que cada cliente tem acesso, basta remover o fator_peso da soma. O mesmo modelo responde às duas perguntas — quanto pertence a cada um e a que valores cada um acede.

Verificar o resultado

Com um saldo de 1000 € em C-100 e 500 € em C-200, a consulta ponderada devolve Ana = 1000 € (500 de C-100 mais 500 de C-200) e Bruno = 500 €. A soma total é 1500 €, exatamente o saldo real do banco — sem inflação. Se os números não fecharem, verifique primeiro se os fator_peso de cada conta somam 1,0; é quase sempre aí que está o erro.

Conclusão

Com uma bridge table e um fator de peso, resolveu uma relação muitos-para-muitos sem perder o detalhe nem inflacionar os totais — a base de relatórios em que se pode confiar. O passo seguinte é aplicar o mesmo padrão a outros casos, como produtos com várias categorias ou projetos com várias equipas, decidindo caso a caso quando usar a soma ponderada. Que relação muitos-para-muitos no seu modelo está à espera de uma ponte?