# Where They Used to Live

> They moved. The data stayed behind.

Canonical URL: <https://datadriven.io/problems/address_history_bridge>

Domain: Data Modeling · Difficulty: medium · Seniority: L4

## Problem

Customers move. We need to know their current address and their full address history, including when they moved in and moved out of each one. Design the schema.

## Worked solution and explanation

### Why this problem exists in real interviews

Retail companies need accurate customer addresses for shipping, marketing, and compliance. Address history is a classic SCD Type 2 pattern: you need to know what was true at any point in time. This question tests normalization instincts, temporal modeling, and the ability to design a bridge table.

---

### The three-table design

#### Step 1: customers

`customer_id` PK, `name`, `email`, `created_at`. This is the dimension. No address columns here.

#### Step 2: addresses

`address_id` PK, `street`, `city`, `state`, `zip_code`, `country`. This is the physical location, reusable across customers.

#### Step 3: customer_addresses (bridge)

`customer_address_id` PK, `customer_id` FK, `address_id` FK, `move_in_date` DATE NOT NULL, `move_out_date` DATE NULL. The temporal relationship lives here.

---

### Key query patterns

```sql

```

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/address_history_bridge)
- [Data Modeling Interview Questions](https://datadriven.io/data-modeling-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.