Katkat
Back

Design an employee directory with reporting queries

ExpediaSoftware Engineer · Onsite
HardSoftware EngineerOnsiteSystem Design

Last updated

This question tests the ability to design a scalable hierarchical data structure, select appropriate storage backends for recursive queries, and handle write-heavy concurrent updates to organizational charts.

Problem Statement

Design an employee directory service for a large organization (100,000+ employees) that supports both standard CRUD operations and complex hierarchical reporting queries.

Functional Requirements

  1. Add new employees and update their manager.
  2. Retrieve an employee's direct reports.
  3. Retrieve the full management chain (ancestors) for any given employee.
  4. Retrieve the entire sub-tree of subordinates for a given manager.

Non-Functional Requirements

  1. High read availability for directory lookups.
  2. Support for frequent re-organizations (moving departments/reporting lines).
  3. Strong consistency for reporting lines to avoid circular dependencies (e.g., A reports to B, B reports to A).

Expectations

  • Define the database schema. How do you store the hierarchy to minimize query latency for sub-tree retrieval?
  • Design the API endpoints for the identified requirements.
  • Describe the architecture to handle concurrent updates to the organizational structure.
  • Discuss how to detect and prevent cycles during a 'manager move' operation.

Hints

Hint 1

Consider the trade-offs between adjacency lists, nested sets, and closure tables for storing tree-like hierarchical data.

Hint 2

For very large sub-trees, would caching the hierarchy or using a graph database be more efficient than recursive SQL joins?

Hint 3

Use a distributed lock or a validation service to ensure that moving an employee does not result in a circular reporting dependency.

Follow-up questions

  • How would you handle temporal data? (e.g., 'Show me the organizational chart as it existed on January 1st, 2023').
  • If the system requires read-heavy performance for the entire company hierarchy, how would you implement a caching strategy that invalidates efficiently during a re-org?
  • How would you scale the system if the organization grew to 10 million employees?