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.

Key components

• Schema: employees(id, org_id, dept_id, manager_id, name, …), departments, employment_history; manager_id self-reference for hierarchy (adjacency list; closure table or recursive CTEs for deep org queries).
• Storage/indexing internals to discuss: B-tree indexes and why (ordered, range-friendly); clustered vs secondary indexes; composite index (org_id, dept_id) matching query prefixes; covering indexes to skip heap lookups; write amplification from over-indexing; how a query planner picks indexes (selectivity/statistics).
• Sharding: shard key choice — org_id/tenant keeps a company's data co-located (single-shard queries) vs hashing employee_id (uniform but scatters org queries); query routing via a shard map/proxy; rebalancing with consistent hashing or range splits; cross-shard queries and why to avoid them (fan-out + merge layer when unavoidable).

Deep dives / trade-offs

• Hot-tenant problem (one giant org on one shard) — sub-sharding or vertical carve-out.
• Global secondary indexes vs per-shard local indexes; keeping unique constraints (email) global via a lookup service.
• Read replicas for reporting; partitioning (time-based on history tables) vs sharding — different tools, both worth naming.