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 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. ## Best Practices (rules of thumb — mechanisms in Expert Practices) - **Types:** `BIGINT GENERATED ALWAYS AS IDENTITY` for surrogate PKs (not `SERIAL`/`INT4`); `TIMESTAMPTZ` never bare `TIMESTAMP`; `NUMERIC`/`DECIMAL` (not `FLOAT`/`money`) for currency; `TEXT`/`VARCHAR` (not `char(n)`). - **Index wisely:** cover WHERE/JOIN/ORDER BY/GROUP BY, but every index is a write tax — audit `pg_stat_user_indexes` for `idx_scan = 0` and drop dead ones. - **Normalize first, derive later:** normalized system-of-record; denormalize only as materialized views / projections refreshed from it, not duplicated app-side writes. - **Constrain at the DB:** PK/FK/UNIQUE/CHECK/NOT NULL. `NOT EXISTS`, never `NOT IN (nullable subquery)`. - **Plan for scale early:** partition/shard/replicate high-volume tables; CQRS (separate read/write models) at high scale. - **Operational hygiene:** audit columns (`created_at`/`updated_at`/`created_by`); idempotent ETL; version event payloads; test migrations on production-like volumes; maintain an ERD/data dictionary. ## Examples ### Example 1: E-Commerce Schema (PostgreSQL) > **PK choice:** `gen_random_uuid()` (UUIDv4) is random and fragments the B-tree (page splits, low fill) on high-volume tables. It is fine for low-volume tables; for write-heavy OLTP prefer `uuidv7()` (PG18+) or `pg_uuidv7`'s `uuid_generate_v7()` (PG14-17) — time-ordered, append-friendly, but embeds an approximate creation timestamp — or `BIGINT GENERATED ALWAYS AS IDENTITY` when an opaque/distributed ID is not required. (See Expert Practices.) ```sql -- Users table with proper constraints CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email VARCHAR(255) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, name VARCHAR(100) NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), deleted_at TIMESTAMPTZ, CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') ); -- Products with proper indexing CREATE TABLE products ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), sku VARCHAR(50) NOT NULL UNIQUE, name VARCHAR(255) NOT NULL, description TEXT, price DECIMAL(10, 2) NOT NULL CHECK (price >= 0), stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0), category_id UUID REFERENCES categories(id), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- Indexes for common queries CREATE INDEX idx_products_category ON products(category_id); CREATE INDEX idx_products_price ON products(price); CREATE INDEX idx_products_name_search ON products USING gin(to_tsvector('english', name)); -- Orders with proper relationships CREATE TABLE orders ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL REFERENCES users(id), status VARCHAR(20) NOT NULL DEFAULT 'pending', total_amount DECIMAL(10, 2) NOT NULL, shipping_address JSONB NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), CONSTRAINT valid_status CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')) ); CREATE INDEX idx_orders_user ON orders(user_id); CREATE INDEX idx_orders_status ON orders(status); CREATE INDEX idx_orders_created ON orders(created_at DESC); -- Order items junction table CREATE TABLE order_items ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE, product_id UUID NOT NULL REFERENCES products(id), quantity INTEGER NOT NULL CHECK (quantity > 0), unit_price DECIMAL(10, 2) NOT NULL, UNIQUE(order_id, product_id) ); -- FK indexes: UNIQUE(order_id, product_id) leads with order_id so it covers -- the FK check on orders deletes, but NOTHING leads with product_id — deleting -- a product would seq-scan order_items. Index every child FK column explicitly. CREATE INDEX idx_order_items_product_id ON order_items(product_id); -- For a pure junction table, PRIMARY KEY (order_id, product_id) (dropping the -- surrogate UUID) removes the redundant unique index entirely. ``` ### Example 2: Migration Script ```sql -- Migration: Add customer loyalty program -- Version: 20240115_001 -- Fail fast instead of queuing behind a long transaction and stalling all -- traffic to users; run the whole migration inside a backoff+jitter retry loop. SET lock_timeout = '200ms'; BEGIN; -- Add loyalty tier to users. -- Constant DEFAULTs are metadata-only on PG11+ (no table rewrite). A volatile -- default like now()/gen_random_uuid() WOULD rewrite the whole table. ALTER TABLE users ADD COLUMN loyalty_tier VARCHAR(20) DEFAULT 'bronze', ADD COLUMN loyalty_points INTEGER DEFAULT 0; -- Add the CHECK in two phases so it never holds ACCESS EXCLUSIVE for a full scan. -- Phase 1: enforce on new writes immediately, brief lock, no scan. ALTER TABLE users ADD CONSTRAINT valid_loyalty_tier CHECK (loyalty_tier IN ('bronze', 'silver', 'gold', 'platinum')) NOT VALID; COMMIT; -- Phase 2: validate existing rows under SHARE UPDATE EXCLUSIVE (concurrent DML -- is NOT blocked). Run outside the transaction above. ALTER TABLE users VALIDATE CONSTRAINT valid_loyalty_tier; BEGIN; -- Create points history table CREATE TABLE loyalty_points_history ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL REFERENCES users(id), points_change INTEGER NOT NULL, reason VARCHAR(100) NOT NULL, reference_type VARCHAR(50), reference_id UUID, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE INDEX idx_loyalty_history_user ON loyalty_points_history(user_id); CREATE INDEX idx_loyalty_history_created ON loyalty_points_history(created_at DESC); COMMIT; -- Rollback script (save separately) -- BEGIN; -- DROP TABLE IF EXISTS loyalty_points_history; -- ALTER TABLE users DROP COLUMN IF EXISTS loyalty_points; -- ALTER TABLE users DROP COLUMN IF EXISTS loyalty_tier; -- COMMIT; ``` ### Example 3: MongoDB Document Design ```javascript // User document with embedded addresses { _id: ObjectId("..."), email: "user@example.com", profile: { name: "John Doe", avatar_url: "https://..." }, addresses: [ { type: "shipping", street: "123 Main St", city: "Boston", state: "MA", zip: "02101", is_default: true } ], preferences: { newsletter: true, notifications: { email: true, push: false } }, created_at: ISODate("2024-01-15T10:00:00Z") } // Indexes db.users.createIndex({ email: 1 }, { unique: true }); db.users.createIndex({ "addresses.zip": 1 }); db.users.createIndex({ created_at: -1 }); ``` ### Example 4: Data Warehouse Star Schema (PostgreSQL) ```sql -- Dimension: Date (conformed dimension) CREATE TABLE dim_date ( date_key INTEGER PRIMARY KEY, -- YYYYMMDD format full_date DATE NOT NULL, day_of_week INTEGER, day_name VARCHAR(10), month INTEGER, month_name VARCHAR(10), quarter INTEGER, year INTEGER, is_weekend BOOLEAN, is_holiday BOOLEAN ); -- Dimension: Product CREATE TABLE dim_product ( product_key BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- Surrogate key (SQL-standard, replaces legacy SERIAL) product_id VARCHAR(50) NOT NULL, -- Natural key from source product_name VARCHAR(255) NOT NULL, category VARCHAR(100), subcategory VARCHAR(100), brand VARCHAR(100), unit_cost DECIMAL(10, 2), -- SCD Type 2 columns for tracking changes effective_date DATE NOT NULL, expiration_date DATE, is_current BOOLEAN DEFAULT TRUE, UNIQUE(product_id, effective_date) ); CREATE INDEX idx_dim_product_current ON dim_product(product_id) WHERE is_current = TRUE; -- Dimension: Customer CREATE TABLE dim_customer ( customer_key BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id VARCHAR(50) NOT NULL, customer_name VARCHAR(255), customer_segment VARCHAR(50), region VARCHAR(100), country VARCHAR(100), -- SCD Type 1 (overwrite) for most attributes -- Use SCD Type 2 if you need to track segment changes effective_date DATE NOT NULL, expiration_date DATE, is_current BOOLEAN DEFAULT TRUE ); -- Fact: Sales (central fact table) CREATE TABLE fact_sales ( sale_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, date_key INTEGER NOT NULL REFERENCES dim_date(date_key), product_key INTEGER NOT NULL REFERENCES dim_product(product_key), customer_key INTEGER NOT NULL REFERENCES dim_customer(customer_key), -- Measures (additive facts) quantity INTEGER NOT NULL, unit_price DECIMAL(10, 2) NOT NULL, discount_amount DECIMAL(10, 2) DEFAULT 0, tax_amount DECIMAL(10, 2) NOT NULL, total_amount DECIMAL(10, 2) NOT NULL, cost_amount DECIMAL(10, 2) NOT NULL, -- Degenerate dimensions (attributes with no separate dimension table) order_number VARCHAR(50), transaction_time TIMESTAMP NOT NULL ); -- Indexes for typical analytical queries CREATE INDEX idx_fact_sales_date ON fact_sales(date_key); CREATE INDEX idx_fact_sales_product ON fact_sales(product_key); CREATE INDEX idx_fact_sales_customer ON fact_sales(customer_key); CREATE INDEX idx_fact_sales_composite ON fact_sales(date_key, product_key, customer_key); -- Materialized view for pre-aggregated monthly sales CREATE MATERIALIZED VIEW mv_monthly_sales AS SELECT d.year, d.month, p.category, c.region, SUM(f.quantity) AS total_quantity, SUM(f.total_amount) AS total_revenue, SUM(f.cost_amount) AS total_cost, SUM(f.total_amount - f.cost_amount) AS total_profit FROM fact_sales f JOIN dim_date d ON f.date_key = d.date_key JOIN dim_product p ON f.product_key = p.product_key JOIN dim_customer c ON f.customer_key = c.customer_key GROUP BY d.year, d.month, p.category, c.region; CREATE INDEX idx_mv_monthly_sales ON mv_monthly_sales(year, month, category); ``` ### Example 5: Time-Series Database (TimescaleDB) ```sql -- Create hypertable for metrics CREATE TABLE metrics ( time TIMESTAMPTZ NOT NULL, device_id VARCHAR(50) NOT NULL, metric_name VARCHAR(100) NOT NULL, value DOUBLE PRECISION NOT NULL, tags JSONB, PRIMARY KEY (time, device_id, metric_name) ); -- Convert to hypertable with 1-day chunks SELECT create_hypertable('metrics', 'time', chunk_time_interval => INTERVAL '1 day'); -- Create indexes for common query patterns CREATE INDEX idx_metrics_device_time ON metrics(device_id, time DESC); CREATE INDEX idx_metrics_name_time ON metrics(metric_name, time DESC); CREATE INDEX idx_metrics_tags ON metrics USING gin(tags); -- Compression policy (compress chunks older than 7 days). -- Note: TimescaleDB 2.18+ introduces the "hypercore" storage engine as the -- recommended default for new hypertables, unifying hot rowstore and cold -- columnstore and automating conversion. The manual policy below is for older -- versions or fine-grained control. (Moving target — verify against your version.) ALTER TABLE metrics SET ( timescaledb.compress, timescaledb.compress_segmentby = 'device_id, metric_name' ); SELECT add_compression_policy('metrics', INTERVAL '7 days'); -- Retention policy (drop chunks older than 90 days) SELECT add_retention_policy('metrics', INTERVAL '90 days'); -- Continuous aggregate for hourly rollups CREATE MATERIALIZED VIEW metrics_hourly WITH (timescaledb.continuous) AS SELECT time_bucket('1 hour', time) AS hour, device_id, metric_name, AVG(value) AS avg_value, MAX(value) AS max_value, MIN(value) AS min_value, COUNT(*) AS count FROM metrics GROUP BY hour, device_id, metric_name; -- Refresh policy for continuous aggregate SELECT add_continuous_aggregate_policy('metrics_hourly', start_offset => INTERVAL '3 hours', end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '1 hour'); ``` ### Example 6: Event Sourcing Pattern (PostgreSQL) ```sql -- Event store (append-only) CREATE TABLE events ( event_id BIGSERIAL PRIMARY KEY, aggregate_id UUID NOT NULL, aggregate_type VARCHAR(100) NOT NULL, event_type VARCHAR(100) NOT NULL, event_version INTEGER NOT NULL, payload JSONB NOT NULL, metadata JSONB, occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- For optimistic concurrency control sequence_number INTEGER NOT NULL,