Design an employee directory with reporting queries
Company: Expedia
Role: Software Engineer
Category: System Design
Difficulty: hard
Interview Round: Onsite
## Scenario
Design an internal **employee management system**.
## Functional requirements
- CRUD for employees:
- Create/update employee profile (name, title, department, manager)
- Deactivate/terminate employees
- Support org-structure queries:
- Get an employee’s **manager**
- Get an employee’s **direct reports**
- Get an employee’s **full reporting chain** (up to CEO)
- Get an employee’s **full subtree** (all indirect reports)
## Non-functional requirements / discussion points
- High concurrency (many reads/writes), correctness under concurrent updates.
- Locking and consistency (avoid cycles, avoid lost updates).
- API design (endpoints, payloads, versioning).
- Data storage choices and schema design.
Explain your design and trade-offs, including how you would implement efficient reporting-line queries.
Quick Answer: This question evaluates system design competence with emphasis on hierarchical data modeling, concurrent data consistency, API and schema design, and efficient reporting-line queries within an employee management system.