Orientation
What You'll Master Here
The same company needs two very different data shapes. Checkout needs a safe current-state order write. Finance needs years of history scanned and aggregated by month, category, and customer cohort.
OLTP and OLAP are workload terms, not competing products. A normalized transactional schema protects concurrent writes; an analytical model makes historical reads and metrics practical.
This chapter teaches how to choose the structure from the question being asked, without racing ahead into the full facts-and-dimensions design of Chapter 8.
Why it matters
Running analytical scans against the transactional source can slow customer operations, while forcing checkout through a reporting model weakens integrity.
Core mental model
Model for the workload: transaction integrity and current lookup for OLTP; historical scans and aggregation for OLAP.
| need | OLTP shape | OLAP shape |
|---|---|---|
| place order | normalized transactional rows | not the primary workload |
| monthly revenue | expensive multi-table scan | historical analytical model |
| support lookup | current order by id | optional derived serving view |
- OLTP
- Transactional workload optimized for many small concurrent writes and current-state reads.
- OLAP
- Analytical workload optimized for large historical reads, calculations, and trend analysis.
- serving boundary
- The system or model responsible for a particular consumer workload.
- semantic layer
- A business-friendly analytical layer that defines metrics and relationships for reporting.
Common mistake
Treating OLTP and OLAP as two names for the same database tables.
One workload eventually degrades the other.
Better habit
- Start from query shape.
- Separate source-of-truth writes from analytical reads.
- State freshness and history needs explicitly.
I would model checkout as normalized OLTP and move historical reporting into an analytical store, because the workloads, latency, and query shapes are different.
Practice prompts
- Classify five business questions as OLTP or OLAP.
- Explain why a dashboard should not scan checkout tables directly.
Remember this
Choose the model from the workload, not from a fashionable schema name.