loom-database-design

# Database Design ## Overview Designing schemas and data models across workloads: **OLTP** (normalized relational, transactional integrity), **OLAP** (star/snowflake warehouses), **NoSQL** (document/KV/wide-column), **time-series** (TimescaleDB/InfluxDB), **event sourcing** (append-only stores), and **ETL/pipeline** staging. Most examples are PostgreSQL; principles generalize. The mechanism-level rules — keys, indexing, concurrency, lock-aware DDL, partitioning — live in **Expert Practices** below; this section is the design method. ## Design Method **1. Requirements → model.** Entities, attributes, relationships (1:1 / 1:N / M:N); access patterns (read vs write heavy, hot queries); volume, growth, retention; OLTP vs OLAP. The access pattern, not the entities, drives the physical design. **2. Schema per workload:** - **OLTP:** normalize to 3NF (one home per fact), then derive read models. Surrogate vs natural PK (see Keys). FK cascade rules. Correct types + `CHECK` constraints. Deliberate NULL semantics. - **OLAP:** star schema (fact + denormalized dimensions); snowflake only when a dimension's cardinality/reuse justifies normalizing it. Surrogate dimension keys. SCD Type 1 (overwrite) / Type 2 (row-versioned history) / Type 3 (prior-value column). Fact tables = FKs + additive measures + degenerate dims. - **Time-series:** time as leading PK component; partition by time range; append-only writes; downsample into rollup/continuous-aggregate tables; retention policy that ...