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 drops old partitions.
• Event sourcing: immutable append-only events (aggregate_id, event_type, sequence_number, payload, occurred_at); optimistic concurrency via UNIQUE(aggregate_id, sequence_number); projections as derived read models; version the payload for schema evolution; snapshots to bound replay cost.

3. Performance & concurrency, migrations, ETL: these are the highest-defect areas — apply the mechanism rules from Expert Practices: index every child FK column; INCLUDE covering indexes; partial-index literal-match limits; declarative partitioning (never inheritance+triggers); READ COMMITTED anomalies vs SERIALIZABLE+40001 retry; lock-aware DDL (lock_timeout, NOT VALID+VALIDATE, CREATE INDEX CONCURRENTLY); idempotent MERGE/ON CONFLICT upserts with staging tables and audit columns.