A single-table patient-and-visit design worked for a pilot but now must scale 100x and reporting fails. How should you redesign the schema?

The correct approach is to normalize the schema. A single-table design for patient and visit data is inefficient for large-scale reporting due to redundant data and complex queries, especially if it involves self-joins. Normalizing into separate patient and visits tables (and potentially other related tables) reduces data redundancy, improves data integrity, and optimizes query performance for reporting by allowing more efficient joins. Adding capacity (memory and disk) only postpones the problem; it doesn't address the underlying schema inefficiency. Sharding by date ranges might help with some reports but doesn't solve the fundamental issue of a denormalized single table and can complicate queries spanning multiple shards. Partitioning by clinic could work for clinic-specific reports but makes consolidated reports more complex and still doesn't fully address the normalization issue within each partition.