Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Many-to-many modeling

Data modeling · Keys & Relationships

Many-to-many modeling

Mediummodel-19
many-to-manybridgejunctionfan-out

Question

How do you model a many-to-many relationship in dimensional modeling?

Solution

A many-to-many means each side can relate to many of the other: a student has many courses; a course has many students. An order can have many promotions; a promotion can apply to many orders.

OLTP style

Use a junction / association table:

students >< student_courses >< courses

Dimensional style

Facts are usually many-to-one to dimensions. True many-to-many between dimensions (or between a fact and a multi-valued dimension) needs a bridge table (sometimes called a helper table) so you do not explode the fact grain incorrectly.

fct_orders (grain: one row per order)
     |
bridge_order_promotion (order_sk, promotion_sk, allocation_weight)
     |
dim_promotion

Danger

If you join a multi-valued dimension directly without care, SUM(amount) double-counts. Bridge tables often include a weight (for example 0.5 / 0.5) so weighted sums stay correct.

Interview tip

> "Many-to-many → bridge table. Protect fact grain; use weights when allocations must sum to 100%."

PreviousNext