Skip to content
← Back to challenges
Dimensional Modeling FoundationsConcept Challengeintermediate20 min

Model campaign enrollments with a bridge table

Resolve a subscriber-to-campaign many-to-many relationship without losing enrollment detail.

How this preview works
Preview this problem before signing in. Sign in to save progress and submit work.

Concept

many-to-many-modeling

The primary modeling idea this problem reinforces.

Objectives

2

Concrete things this problem is meant to teach.

Scenario

A growth team runs campaigns that many subscribers can join. Each subscriber can join many campaigns, and analysts need to study enrollment timing and status without duplicating subscriber or campaign attributes.

Why this matters

Many-to-many relationships are a classic interview trap. A direct edge looks simple, but it hides the row grain and leaves nowhere clean to store relationship-specific facts like enrollment date or status.

Deliverable

Use the sandbox to add a campaign enrollment bridge, then connect it to subscribers and campaigns through explicit surrogate-key foreign keys.

Requirements
  • Keep subscribers and campaigns as reusable dimensions.
  • Add a campaign enrollment bridge between them.
  • Declare explicit bridge foreign keys to both dimension surrogate keys.
What success looks like
  • The model avoids a direct subscriber-to-campaign many-to-many relationship.
  • The bridge has subscriber and campaign foreign keys with N:1 relationships back to each dimension.
  • The bridge is ready to hold enrollment-specific attributes such as enrollment date or lifecycle status.
Starter Workspace Guidance
  • The two dimensions already exist; focus on the relationship grain between them.
  • Avoid a direct many-to-many edge. Give the enrollment relationship its own entity.
Hints
  • Ask what one row in the enrollment entity means.
  • The bridge should point outward to the dimensions, not replace them.
  • Relationship-level attributes belong on the bridge because they describe the enrollment itself.
Common pitfalls
  • Drawing a direct N:M relationship and calling the model complete.
  • Copying subscriber or campaign attributes onto the bridge instead of referencing reusable dimensions.
  • Adding bridge columns without declaring the foreign-key constraints that make the join enforceable.
Read-only starter workspace
Inspect the authored starting ERD before signing up. Editing, verification, and progress saving happen after auth.

Template

Campaign enrollment starter workspace

The model users start from in the full workspace.

Entities

2

Seeded tables visible in the public preview.

Dialect

postgresql

Generated SQL target for this starter model.

Mini Map

Preview mode is read-only. Create an account when you are ready to edit and submit work.

Sign up to edit
Reference approaches
These are valid approaches, not one absolute answer.

Subscriber campaign enrollment bridge

Following Kimball multivalued dimension bridge guidance, use a bridge entity with subscriber and campaign foreign keys, plus enrollment-level attributes.

Optimizes for

  • • Correct many-to-many grain
  • • Reusable dimensions

Trade-offs

  • • Additional join in analytical queries
  • • Requires clear enrollment row semantics
Validation contract
Deterministic checks that must pass before AI feedback becomes useful.

Layer 1

Structural

Generic ERD correctness such as keys and relationships.

Layer 2

9 checks

Authored checks for this exact modeling scenario.

Layer 3

On demand

AI critique after deterministic checks are satisfied.

  • Subscriber Dimension Exists

    Keep subscriber identity in a dimension instead of duplicating subscriber attributes on the bridge.

  • Campaign Dimension Exists

    Keep campaign attributes in their own dimension before modeling enrollments.

  • Enrollment Bridge Exists

    Add a bridge entity for campaign enrollments instead of drawing a direct many-to-many edge.

  • Bridge Has Subscriber Key

    Add a subscriber foreign key column to the enrollment bridge.

  • Bridge Has Campaign Key

    Add a campaign foreign key column to the enrollment bridge.

  • Bridge To Subscriber Relationship

    Connect the enrollment bridge to the subscriber dimension with an N:1 relationship.

  • Bridge To Campaign Relationship

    Connect the enrollment bridge to the campaign dimension with an N:1 relationship.

  • Bridge Fk Targets Subscriber Surrogate

    Declare the bridge subscriber foreign key against the subscriber surrogate primary key.

  • Bridge Fk Targets Campaign Surrogate

    Declare the bridge campaign foreign key against the campaign surrogate primary key.