Lateral Paths

A medium 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
medium
Seniority
L5

Problem

We run a large tech company where a single transfer can move someone to a new team, a new manager, and a different office all at once, and departments themselves nest into larger divisions and orgs. Today HR keeps only each employee's current department, manager, and office in one table, so every move erases where they sat before. Design a schema that preserves the full movement history and can answer where any employee was on a given past date.

People switch teams. HR loses track.

Worked solution and explanation

Why this problem exists in real interviews

This probes whether a candidate recognizes the classic SCD Type 2 on the employee dimension pattern and can model atomic transfers that change multiple attributes at once. HR data is the textbook use case, and a single-row-per-employee design fails the moment anyone asks 'who managed this team last quarter?'

Break down the requirements

Step 1: Separate identity from assignment

employees holds stable attributes (employee_id, name, hire_date). Everything that changes (department, manager, location) lives on employee_assignments with effective dates.

Step 2: Make assignments Type 2

Each row carries effective_from and effective_to, with NULL on the current row. A partial unique index on (employee_id) WHERE effective_to IS NULL guarantees exactly one current assignment per person.

Step 3: Record transfers atomically

A transfer can change department, manager, and location together. transfers is one row per transfer event with the old and new assignment keys. That guarantees the three attribute changes are correlated rather than appearing as three unrelated history rows.

Step 4: Self-reference departments

An org chart is a hierarchy. departments carries parent_department_id pointing back at itself, which supports rollups like 'all engineering headcount' without a bridge table.

Step 5: Keep locations as a separate dimension

Office location changes independently of department (remote reorgs, office closures). A dedicated locations dimension lets location Type 2 evolve on its own cadence.

The solution

Below is one conceptually sound approach: identity on employees, mutable context on employee_assignments (Type 2), and a transfer event log that ties the changes together.

Type 2 `employee_assignments` anchors as-of queries; transfers ties multi-attribute changes to a single atomic event.
  • employees: employee_id BIGINT PK, full_name TEXT, hire_date DATE, email TEXT
  • departments: department_id INT PK, department_name TEXT, parent_department_id INT FK
  • locations: location_id INT PK, office_name TEXT, city TEXT, country TEXT
  • employee_assignments: assignment_id BIGINT PK, employee_id BIGINT FK, department_id INT FK, manager_id BIGINT FK, location_id INT FK, effective_from DATE, effective_to DATE
  • transfers: transfer_id BIGINT PK, employee_id BIGINT FK, from_assignment_id BIGINT FK, to_assignment_id BIGINT FK, transfer_date DATE, reason TEXT
  • employees references employee_assignments
  • departments references employee_assignments
  • locations references employee_assignments
  • departments references departments
  • employees references transfers
  • employee_assignments references transfers

The analysis pattern

Headcount by department as of a historical date
SELECT
    d.department_name,
    COUNT(*) AS headcount
FROM employee_assignments ea
JOIN departments d ON d.department_id = ea.department_id
WHERE ea.effective_from <= '2024-01-01'
  AND (ea.effective_to IS NULL OR ea.effective_to > '2024-01-01')
GROUP BY d.department_name
ORDER BY headcount DESC

Trade-offs and alternatives

Type 2 assignments plus transfer log
Familiar Kimball pattern, easy point-in-time queries, BI tools handle it natively. Cost: two writes per transfer (close old, insert new) and join cost on every report.
Append-only HR event stream
Raw event log of every HR change, projections built downstream. Cost: more infrastructure and analysts need projection tables to answer simple questions; buys replay and audit.

Common follow-up questions