Hierarchies and Bridge Tables in Dimensional Modeling

A star schema assumes each dimension has one value per fact row and that hierarchies have a fixed number of levels. Real data breaks both assumptions: a bank account has several owners, a patient visit has several diagnoses, an org chart is eleven levels deep in one department and three in another. This page covers how dimensional models handle hierarchies of every shape, and how bridge tables handle many-to-many relationships without double counting.

Fixed-depth hierarchies: flatten them

When every member has the same levels, as with product → subcategory → category → department or day → month → quarter → year, store each level as its own column in one dimension row. This is the default and the easiest to query: drilling down is just adding a column to the group by.

A dimension can hold several hierarchies side by side (calendar and fiscal, or merchandising and supplier), each as its own set of columns.

Slightly ragged hierarchies: fill the gaps

Geography is the classic case: most addresses have city, state and country, but some countries have no states. When the variation is small and the maximum depth is known, keep the fixed columns and fill the missing level with a sensible value (repeat the city, or use "No state"). Queries stay simple and totals stay correct.

Ragged, variable-depth hierarchies

Org charts, cost-centre trees, bills of materials and nested category trees have no fixed depth. A recursive parent_id column describes them but makes queries like "all sales by everyone under this VP" painful: a recursive CTE on every report. The dimensional answer is a hierarchy bridge table (sometimes called a closure table) with one row for every ancestor-descendant pair, including each node paired with itself.

-- Sales credited to everyone in Asha's organisation, at any depth
select sum(f.amount)
from fact_sales f
join bridge_org b    on f.employee_key = b.descendant_key
join dim_employee a  on b.ancestor_key = a.employee_key
where a.employee_name = 'Asha';

The bridge is maintained by the pipeline whenever the tree changes. Add valid_from and valid_to columns if reorganisations must be reported historically, so "Asha's org as of last March" is a filter on the bridge.

Multivalued dimensions and bridge tables

Sometimes the problem is not depth but multiplicity: one fact row relates to several members of a dimension. A joint bank account has two owners. A hospital visit has three diagnoses. A shared ride carries four riders. A movie has several genres. Putting one of them on the fact loses the others; repeating the fact row once per member double counts the measure.

The solution is a group: give each distinct combination of members a group key, store that key on the fact, and list the members of each group in a bridge table.

Weighting factors and double counting

Joining through a bridge multiplies each fact row by the number of members in its group. That is exactly right for some questions and badly wrong for others, so decide which one the user is asking:

• Impact report (unweighted): "total balances of accounts that Priya owns, in whole or part". Each member sees the full amount; the grand total across all customers is larger than the bank's real total. Correct for per-customer views, wrong for totals.
• Weighted report: multiply the measure by the weighting factor. Each owner gets their share, and the grand total equals the true total. Use this whenever a number will be added up across members.
• Primary member only: some models also store a single "primary" member directly on the fact for simple reports that only need one.

-- Weighted: totals add up to the real total
select c.segment, sum(f.balance * b.weighting_factor) as balance
from fact_account_balance f
join bridge_owner_group b on f.account_owner_group_key = b.owner_group_key
join dim_customer c       on b.customer_key = c.customer_key
group by c.segment;

The interview trap: drawing a many-to-many relationship as a direct join between the fact and the dimension. Any sum across that join double counts. Say "bridge table with a weighting factor" and explain which reports are weighted.

Alternatives to a bridge