Bridge Tables

Many-to-many relationships need a bridge, or your star schema collapses

Many-to-many relationships need a bridge, or your star schema collapses

Category

Data Modeling

Difficulty

advanced

Duration

25 minutes

Challenges

0 hands-on challenges

Topics covered: When One Row Maps to Many, The Bridge Table Structure, Weighting Factors and Allocation, Bridge Tables vs Array/Map Columns, Walking the Interviewer Through Your Bridge Design

Lesson Sections

1. When One Row Maps to Many

   What They're Really Testing The interviewer is testing three things in sequence. First: do you recognize that this is a many-to-many problem that cannot be solved by adding a foreign key to the fact table? Second: do you know that a naive junction table will inflate aggregates? Third: do you know about weighting factors? Most candidates clear the first gate. Far fewer clear all three. The Problem in Three Sentences State the problem in one sentence: 'A $1,000 sale attributed to 3 sales reps beco

2. The Bridge Table Structure

   Most candidates can explain what a bridge table is. The interview tests whether you can design one correctly: the schema, the weight constraint, the query pattern. Getting any of these wrong produces inflated aggregates that look plausible but are mathematically wrong. The Schema You Design in the Interview Querying Through the Bridge The Query Pattern the Interviewer Expects Walk through the result: 'Alice gets $500, Bob gets $300, Carol gets $200. Total: $1,000. Correct. The weight factor prev

3. Weighting Factors and Allocation

   Weighting is where the interview gets interesting. Equal weights are the easy case. Real business problems have unequal, time-varying, and sometimes unknown allocations. How you handle each case is what the interviewer is probing. The Three Allocation Models The Follow-Up Trap Double-Counting Scenarios The AVG trap catches experienced candidates. Weighted average across a bridge is SUM(amount * weight) / SUM(weight), not AVG(amount * weight). If an interviewer asks about averages with bridge tab

4. Bridge Tables vs Array/Map Columns

   Modern warehouses (BigQuery, Snowflake, Databricks) support ARRAY and MAP column types. This creates a real alternative to bridge tables: store the many-to-many relationship as a nested array inside the fact row itself. Interviewers at companies using these platforms will test whether you know when each approach is appropriate. The Two Approaches: Know Both, Choose Contextually The Modern Alternative You Should Mention Your array-column answer: 'On BigQuery, UNNEST flattens the array into rows,

5. Walking the Interviewer Through Your Bridge Design

   Knowing the bridge table pattern is necessary but not sufficient. The interview signal is in how you narrate the decision. This section gives you the script for walking through a bridge table design from first principles, hitting every rubric item along the way. The 60-Second Framework This five-step narration takes about 60 seconds and hits every rubric item: problem recognition, aggregation awareness, solution design, query mechanics, and data quality. Practice saying it out loud. The Bridge M