Modeling = how you shape data for its use
The schema determines whether queries are fast, correct, and maintainable. The core tension is normalization vs denormalization:
- Normalized (OLTP / transactional) — data split into many tables with no redundancy (3rd normal form). Great for writes and integrity (update a fact in one place). Powers applications.
- Denormalized (OLAP / analytics) — data pre-joined/duplicated for fast reads. Great for analytical queries; worse for writes. Powers warehouses and dashboards.
You pick based on the workload: transactional app → normalized; analytics/reporting → denormalized.
Dimensional modeling (the analytics standard)
The star schema: a central fact table (events/measurements — orders, submissions, with foreign keys and numeric measures) surrounded by dimension tables (the descriptive context — user, product, date, company). Queries join facts to dimensions to slice metrics ("revenue by product by month"). It's intuitive, fast for analytics, and the backbone of most warehouses. (A snowflake schema normalizes dimensions further — more joins, less redundancy.)
Slowly changing dimensions & grain
- Grain — decide what one row of a fact table means (one order line? one daily snapshot?) before anything else; the wrong grain breaks every downstream metric.
- Slowly changing dimensions (SCD) — how you handle attributes that change over time (a user's plan changes): Type 1 overwrites; Type 2 keeps history with versioned rows. Type 2 matters when you need "what was true then."
Modeling for AI
AI systems still need clean models: a feature table (entity + point-in-time features), conversation/message tables, submission/score tables, and (with pgvector) an embeddings table. Good modeling here prevents training/serving skew and makes features reusable — the data foundation the model sits on.