Design an Employee Management System

Problem Design an employee management system, with the discussion centered on database internals: storage layout, indexing, and a sharding strategy as the dataset grows.

Functional requirements

• Employee records (profile, department, manager hierarchy, compensation).
• Org-chart queries (reports-of, chain-of-command).
• Search by name/department/skill; payroll-adjacent reads.

Non-functional requirements

• Start single-region OLTP; plan the path to hundreds of millions of rows (multi-tenant SaaS framing).
• The interviewer wants how the database works underneath, not just boxes.

Areas to go deep

• Schema for the manager hierarchy and how deep org queries run.
• Storage/indexing internals (B-tree vs clustered, composite/covering indexes, planner selectivity).
• Sharding: shard-key choice, routing, rebalancing, and cross-shard queries.