Schema Design for Analytics vs OLTP

Context

At companies like StripeShop, the same data often supports both application transactions and reporting. Interviewers ask this question to test whether you can connect schema design choices to workload patterns, query behavior, and maintenance trade-offs.

Core Question

Explain how you would choose between a normalized schema and a denormalized schema for transactional workloads versus analytics workloads. In your answer, address:

1. What normalization and denormalization mean in practice
2. Why OLTP systems usually favor normalized models
3. Why analytics systems often use denormalized models such as star schemas or wide reporting tables
4. The trade-offs in storage, write performance, read performance, consistency, and query complexity
5. Situations where a hybrid approach is better than choosing only one design

Scope Guidance

The interviewer expects a practical database-design explanation, not just textbook definitions. Focus on how schema shape affects joins, aggregations, update behavior, and operational reliability in PostgreSQL-based systems.