Where Everyone Was

An easy Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
easy
Seniority
L4

Problem

Our customer support team needs to see a customer's current address and their full address history for regional sales analysis. How would you design a schema that handles address changes?

People move. Sometimes twice in a month. How do you remember where everyone was, and when?

Worked solution and explanation

Why this problem exists in real interviews

This is a tight probe of SCD Type 2 modeling. The moment a prompt asks for historical values and current values, the candidate must reach for effective-dated rows rather than mutating the dimension in place.

Break down the requirements

Step 1: Separate customers from addresses

Customer identity is stable; addresses change. Keep customers as the identity table and model addresses as their own dimension so several customers can share an address or reuse one after a move.

Step 2: Introduce a bridge with effective dates

customer_addresses carries (customer_id, address_id, effective_from, effective_to). This is a textbook Type 2 bridge: one row per period a customer lived at an address.

Step 3: Use NULL as sentinel for current

NULL on effective_to marks the current row. Query for current address with WHERE effective_to IS NULL; query for as-of with WHERE ts BETWEEN effective_from AND COALESCE(effective_to, 'infinity').

Step 4: Enforce one current row per customer

A partial unique index on (customer_id) WHERE effective_to IS NULL prevents two simultaneously active addresses per customer, which is the correctness guarantee the support team actually relies on.

The solution

Below is one conceptually sound approach: a Type 2 bridge between customers and addresses. The composite key (customer_id, address_id, effective_from) uniquely identifies each time-slice, and the NULL sentinel on effective_to anchors the current-row queries.

Composite PK (`customer_id`, `address_id`, `effective_from`) identifies each time-slice; a partial unique index on (`customer_id`) WHERE `effective_to` IS NULL enforces exactly one current address.
  • customers: customer_id BIGINT PK, full_name TEXT, email TEXT, created_at TIMESTAMP
  • addresses: address_id BIGINT PK, street TEXT, city TEXT, state TEXT, postal_code TEXT, country TEXT
  • customer_addresses: customer_id BIGINT PK, address_id BIGINT PK, effective_from TIMESTAMP PK, effective_to TIMESTAMP, address_type TEXT
  • customers references customer_addresses
  • addresses references customer_addresses

The analysis pattern

Customer address as-of a given date
SELECT
    c.full_name,
    a.street,
    a.city,
    a.state
FROM customers c
JOIN customer_addresses ca ON ca.customer_id = c.customer_id
JOIN addresses a ON a.address_id = ca.address_id
WHERE ca.effective_from <= '2024-01-15'
  AND (ca.effective_to IS NULL OR ca.effective_to > '2024-01-15')
  AND c.customer_id = 42

Trade-offs and alternatives

Type 2 SCD bridge
One join path, perfect as-of queries, clean constraints. Cost: slightly more complex writes and a close-then-insert transaction.
Type 1 plus separate audit log
Current address on customers, moves appended to a separate audit table. Cost: as-of queries require a union across two tables and drift between the two is silent.

Common follow-up questions