Lesson 44 • Advanced
Database Design Patterns (OLTP vs OLAP)
Choose the right schema design for your workload — transactional, analytical, or flexible.
✅ What You'll Learn
- • OLTP schemas: normalised, fast transactions
- • OLAP schemas: star/snowflake for analytics
- • EAV pattern for flexible attributes
- • When to use each pattern in real systems
🏗️ Choosing Your Architecture
🎯 Real-World Analogy: OLTP is like a cash register — fast, one transaction at a time, precise. OLAP is like a year-end financial report — slow to generate but covers millions of transactions at once. You wouldn't use a cash register to do year-end analysis, and you wouldn't use a report generator to process checkouts.
⚡ OLTP
Web apps, e-commerce, banking. Fast single-row operations, normalised, row-oriented.
📊 OLAP
Data warehouses, BI dashboards. Slow complex aggregations, denormalised, column-oriented.
Try It: OLTP Schema Design
Build a normalised transactional schema optimised for fast reads/writes
-- OLTP: Online Transaction Processing
-- Optimised for fast reads/writes of individual records
-- Normalised schema, row-oriented storage
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL REFERENCES customers(customer_id),
status VARCHAR(20) NOT NULL DEFAULT 'pending',
created_
...Try It: OLAP Star Schema
Design a star schema with fact and dimension tables for analytics
-- OLAP: Online Analytical Processing
-- Optimised for complex aggregations across millions of rows
-- Denormalised star schema, column-oriented thinking
-- Fact table: one row per measurable event
CREATE TABLE fact_sales (
sale_id BIGSERIAL PRIMARY KEY,
date_key INT NOT NULL,
product_key INT NOT NULL,
customer_key INT NOT NULL,
store_key INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
discount DECIMAL(5,2) DEFAULT 0,
total_amount DECI
...Try It: Entity-Attribute-Value Pattern
Build flexible schemas for entities with variable attributes
-- Entity-Attribute-Value (EAV) Pattern
-- Flexible schema for variable attributes
-- Used when different entities have different fields
CREATE TABLE entities (
entity_id SERIAL PRIMARY KEY,
entity_type VARCHAR(50) NOT NULL,
name VARCHAR(200) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE attributes (
attribute_id SERIAL PRIMARY KEY,
entity_id INT NOT NULL REFERENCES entities(entity_id),
attribute_name VARCHAR(100) NOT NULL,
attribute_value TEXT,
...📋 Quick Reference
| Pattern | Schema | Query Type | Use Case |
|---|---|---|---|
| OLTP | 3NF normalised | Point lookups | Web apps, APIs |
| OLAP Star | Denormalised | Aggregations | BI, reporting |
| EAV | Flexible | Key-value | CMS, configs |
| JSONB | Semi-structured | Document | APIs, catalogs |
🎉 Lesson Complete!
You now understand the major database design patterns! Next, learn about cross-database queries and foreign data wrappers.
Sign up for free to track which lessons you've completed and get learning reminders.