Data Modeling Refresher

0. Setup & Environment

Get both an OLTP database (Postgres) and an OLAP/columnar database (ClickHouse) running locally so you can practice modeling concepts hands-on throughout this guide.

Prerequisites

# Docker Desktop
brew install --cask docker

# psql CLI client
brew install libpq && brew link --force libpq

Postgres (OLTP Practice)

docker run -d --name dm-postgres \
  -e POSTGRES_USER=dev -e POSTGRES_PASSWORD=dev -e POSTGRES_DB=modeling \
  -p 5432:5432 postgres:16

Connect:

PGPASSWORD=dev psql -h 127.0.0.1 -p 5432 -U dev -d modeling

ClickHouse (OLAP Practice)

docker run -d --name dm-clickhouse \
  -p 8123:8123 -p 9000:9000 \
  clickhouse/clickhouse-server:latest

Connect via CLI:

docker exec -it dm-clickhouse clickhouse-client

Or via HTTP:

curl 'http://127.0.0.1:8123/?query=SELECT+1'

Sample Schema to Get Started

Normalized OLTP schema in Postgres:

CREATE TABLE customers (
  id        SERIAL PRIMARY KEY,
  name      TEXT,
  email     TEXT UNIQUE
);

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(id),
  total       NUMERIC(10,2),
  ordered_at  TIMESTAMP DEFAULT now()
);

CREATE TABLE order_items (
  id         SERIAL PRIMARY KEY,
  order_id   INT REFERENCES orders(id),
  product    TEXT,
  quantity   INT,
  price      NUMERIC(10,2)
);

Denormalized star schema equivalent in ClickHouse:

CREATE TABLE fact_orders (
  order_id      UInt32,
  customer_name String,
  product       String,
  quantity      UInt16,
  price         Decimal(10,2),
  total         Decimal(10,2),
  ordered_at    DateTime
) ENGINE = MergeTree() ORDER BY ordered_at;

Cleanup

docker stop dm-postgres dm-clickhouse && docker rm dm-postgres dm-clickhouse

1. Data Modeling Fundamentals

Data modeling is the process of defining what data an application stores, how that data is structured, and how the pieces relate to one another. A good model makes queries fast, code obvious, and business rules enforceable at the database layer rather than scattered across application code.

Three Levels of Abstraction

Entity-Relationship (ER) Model

An entity is a distinct thing the business cares about (Customer, Order, Product). An attribute is a fact about an entity (Customer.email). A relationship describes how entities associate.

Cardinality Notation

ASCII ER Diagram — E-Commerce Core

-- Conceptual / Logical ER (crow's foot notation, ASCII)
--
--  CUSTOMER           ORDER              ORDER_ITEM         PRODUCT
--  =========          =======            ==========         =======
--  id (PK)     ||--o{ id (PK)    ||--o{ id (PK)     }o--|| id (PK)
--  email           customer_id (FK)  order_id (FK)         name
--  name            placed_at         product_id (FK)       price
--  created_at      status            quantity              sku
--                  total_cents       unit_price_cents      category_id (FK)
--
--  CATEGORY
--  ========
--  id (PK)
--  name
--  parent_id (FK → CATEGORY)   -- self-referencing (hierarchy)

Data Modeling Lifecycle

1. Requirements gathering — what questions must the system answer? What transactions occur?
2. Conceptual model — entities and relationships, no technical detail
3. Logical model — attributes, keys, cardinality, normalization decisions
4. Physical model — DDL, data types, indexes, partitioning, engine-specific features
5. Implementation & migration — versioned migrations (Alembic, Flyway)
6. Evolution — schema changes driven by new requirements, backward-compatible when possible

2. Guiding Principles

Clarity Over Cleverness

A schema is read far more often than it is written. Prioritize names and structures that are self-documenting. A new engineer should be able to understand the domain by reading the table and column names alone.

-- Bad: ambiguous, abbreviated, inconsistent
CREATE TABLE usr_mst (
  uid      INT,
  fn       VARCHAR(50),
  ln       VARCHAR(50),
  stat     INT,   -- what does 0, 1, 2 mean?
  ts       TIMESTAMP
);

-- Good: self-documenting
CREATE TABLE users (
  id          BIGSERIAL PRIMARY KEY,
  first_name  VARCHAR(100) NOT NULL,
  last_name   VARCHAR(100) NOT NULL,
  status      VARCHAR(20)  NOT NULL DEFAULT 'active'
                CHECK (status IN ('active','suspended','deleted')),
  created_at  TIMESTAMPTZ  NOT NULL DEFAULT now()
);

Naming Conventions

Core Principles Summary

• Model the business, not the application — tables represent domain concepts, not UI screens or API responses
• Query-driven design — know your most important queries before finalizing the schema; index and denormalize accordingly
• Separation of concerns — keep transactional (OLTP) and analytical (OLAP) workloads on separate systems
• Design for change — prefer additive changes (new columns/tables); avoid designs that require table rewrites to extend
• Integrity at the database level — NOT NULL, CHECK, FK constraints are your last line of defense; don't rely only on application validation
• Principle of least surprise — NULL in a column named email is surprising; use NOT NULL unless absence is genuinely meaningful
• Balance normalization — 3NF is usually right for OLTP; over-normalizing creates painful joins, under-normalizing creates update anomalies

3. OLTP vs OLAP

ETL / ELT Pipeline

-- OLTP (source of truth)        ETL/ELT           OLAP (analytics)
-- ─────────────────────         ───────           ────────────────
-- orders (normalized)    ──▶   Extract           fact_orders (wide)
-- order_items            ──▶   Transform  ──▶    dim_customer
-- customers              ──▶   Load              dim_product
-- products               ──▶   (or ELT:          dim_date
--                               load raw,
--                               transform
--                               in warehouse)

When to Use Each

• OLTP only — small team, <1M rows, analytics done in-app or with simple SQL
• OLTP + OLAP — analytics needs differ from transactional needs, analysts need SQL access, ML feature pipelines
• OLAP only — pure analytics product, data lake, BI dashboards on historical data

4. Normalization (OLTP)

Normalization eliminates data redundancy and prevents three types of anomalies:

• Insert anomaly — can't add data without adding unrelated data
• Update anomaly — changing one fact requires updating many rows
• Delete anomaly — deleting one thing accidentally deletes unrelated data

1NF — First Normal Form

Rule: Each column holds atomic (indivisible) values. No repeating groups. Each row is uniquely identifiable.

-- VIOLATES 1NF: multiple values in one column
CREATE TABLE orders_bad (
  order_id   INT,
  items      TEXT  -- "sku1:2,sku2:1,sku3:5" ← NOT atomic
);

-- 1NF compliant: each cell holds one value
CREATE TABLE order_items (
  order_id    INT  NOT NULL,
  product_sku VARCHAR(50) NOT NULL,
  quantity    INT  NOT NULL,
  PRIMARY KEY (order_id, product_sku)
);

2NF — Second Normal Form

Rule: Must be in 1NF. Every non-key attribute must depend on the entire primary key (no partial dependencies). Only relevant when the PK is composite.

-- VIOLATES 2NF: product_name depends only on product_sku,
--               not on (order_id, product_sku) together
CREATE TABLE order_items_bad (
  order_id      INT,
  product_sku   VARCHAR(50),
  product_name  TEXT,    -- ← depends only on product_sku
  quantity      INT,
  PRIMARY KEY (order_id, product_sku)
);

-- 2NF: split product attributes into their own table
CREATE TABLE products (
  sku   VARCHAR(50) PRIMARY KEY,
  name  TEXT NOT NULL
);

CREATE TABLE order_items (
  order_id    INT         NOT NULL REFERENCES orders(id),
  product_sku VARCHAR(50) NOT NULL REFERENCES products(sku),
  quantity    INT         NOT NULL,
  PRIMARY KEY (order_id, product_sku)
);

3NF — Third Normal Form

Rule: Must be in 2NF. No transitive dependencies — non-key attributes must not depend on other non-key attributes.

-- VIOLATES 3NF: zip_code → city, state (transitive dependency)
CREATE TABLE customers_bad (
  id          BIGSERIAL PRIMARY KEY,
  name        TEXT,
  zip_code    CHAR(5),
  city        TEXT,   -- ← determined by zip_code, not by id
  state       CHAR(2) -- ← determined by zip_code, not by id
);

-- 3NF: extract the transitive dependency
CREATE TABLE zip_codes (
  zip_code CHAR(5) PRIMARY KEY,
  city     TEXT    NOT NULL,
  state    CHAR(2) NOT NULL
);

CREATE TABLE customers (
  id       BIGSERIAL PRIMARY KEY,
  name     TEXT   NOT NULL,
  zip_code CHAR(5) REFERENCES zip_codes(zip_code)
);

BCNF — Boyce-Codd Normal Form

Rule: A stricter version of 3NF. For every functional dependency X → Y, X must be a superkey. In practice, BCNF and 3NF differ only when a table has multiple overlapping candidate keys.

4NF — Fourth Normal Form

Rule: Must be in BCNF. No multi-valued dependencies — a table should not store two independent multi-valued facts about an entity in the same row.

-- VIOLATES 4NF: employee skills and employee languages are
-- independent multi-valued facts about an employee
CREATE TABLE employee_facts_bad (
  employee_id  INT,
  skill        TEXT,
  language     TEXT,
  PRIMARY KEY (employee_id, skill, language)
  -- Creates cartesian product: if 3 skills + 2 languages = 6 rows
);

-- 4NF: split into two tables
CREATE TABLE employee_skills    (employee_id INT, skill    TEXT, PRIMARY KEY (employee_id, skill));
CREATE TABLE employee_languages (employee_id INT, language TEXT, PRIMARY KEY (employee_id, language));

5. Denormalization

Denormalization intentionally introduces redundancy to improve read performance. It is a deliberate trade-off, not a shortcut. The core question: is the query latency improvement worth the consistency maintenance burden?

Techniques

Pre-computed / Derived Columns

-- Store a computed value to avoid recalculation on every read
ALTER TABLE orders ADD COLUMN item_count INT NOT NULL DEFAULT 0;

-- Keep in sync with a trigger
CREATE OR REPLACE FUNCTION update_order_item_count()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  UPDATE orders
  SET    item_count = (SELECT COUNT(*) FROM order_items WHERE order_id = NEW.order_id)
  WHERE  id = NEW.order_id;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_order_item_count
AFTER INSERT OR DELETE OR UPDATE ON order_items
FOR EACH ROW EXECUTE FUNCTION update_order_item_count();

Summary / Aggregate Tables

-- Pre-aggregate daily sales to avoid scanning all order_items
CREATE TABLE daily_sales_summary (
  date        DATE        NOT NULL,
  product_id  BIGINT      NOT NULL REFERENCES products(id),
  units_sold  INT         NOT NULL DEFAULT 0,
  revenue     NUMERIC(12,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (date, product_id)
);

-- Populate via scheduled job or CDC pipeline
INSERT INTO daily_sales_summary (date, product_id, units_sold, revenue)
SELECT  DATE_TRUNC('day', o.placed_at)::DATE,
        oi.product_id,
        SUM(oi.quantity),
        SUM(oi.quantity * oi.unit_price_cents) / 100.0
FROM    order_items oi
JOIN    orders o ON o.id = oi.order_id
WHERE   o.placed_at >= CURRENT_DATE - INTERVAL '1 day'
GROUP BY 1, 2
ON CONFLICT (date, product_id) DO UPDATE
  SET units_sold = EXCLUDED.units_sold,
      revenue    = EXCLUDED.revenue;

Copying FK Attributes (Flattening)

-- Store user_email directly on audit_log to avoid a join
-- when the original user row might be deleted
CREATE TABLE audit_log (
  id           BIGSERIAL PRIMARY KEY,
  user_id      BIGINT,         -- may be NULL if user deleted
  user_email   TEXT,           -- denormalized snapshot at time of event
  action       TEXT NOT NULL,
  occurred_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

JSONB for Semi-Structured Denormalization

-- Store varying product attributes without an EAV table
CREATE TABLE products (
  id         BIGSERIAL PRIMARY KEY,
  name       TEXT NOT NULL,
  category   TEXT NOT NULL,
  attributes JSONB,   -- {"color": "red", "weight_kg": 1.2, "size": "M"}
  price      NUMERIC(10,2) NOT NULL
);

-- Index a specific JSONB key for fast filtering
CREATE INDEX idx_products_color ON products ((attributes->>'color'));
SELECT * FROM products WHERE attributes->>'color' = 'red';

Trade-offs

6. OLTP Schema Design Patterns

Primary Key Strategies

-- BIGSERIAL (PostgreSQL)
CREATE TABLE orders (
  id         BIGSERIAL PRIMARY KEY,
  placed_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- UUID v4
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE TABLE events (
  id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL
);

-- UUID v7 (PostgreSQL 17+ or custom function)
-- Stores as UUID but is time-sortable: first 48 bits = millisecond timestamp

Junction Tables (M:N Relationships)

-- Students ↔ Courses  (many-to-many)
CREATE TABLE students (id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE courses  (id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL);

CREATE TABLE enrollments (
  student_id  BIGINT NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  course_id   BIGINT NOT NULL REFERENCES courses(id)  ON DELETE CASCADE,
  enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  grade       CHAR(2),
  PRIMARY KEY (student_id, course_id)  -- composite PK, also ensures uniqueness
);

-- Index the reverse direction for "which students are in course X?"
CREATE INDEX idx_enrollments_course ON enrollments(course_id);

Polymorphic Associations

When a table can belong to one of several other tables. Four main approaches:

-- Pattern 1: Single Table Inheritance (STI)
-- All subtypes in one table, type column discriminates
CREATE TABLE notifications (
  id          BIGSERIAL PRIMARY KEY,
  type        TEXT NOT NULL CHECK (type IN ('email','sms','push')),
  recipient   TEXT NOT NULL,
  subject     TEXT,           -- only used by email
  phone       TEXT,           -- only used by sms
  device_token TEXT,          -- only used by push
  body        TEXT NOT NULL,
  sent_at     TIMESTAMPTZ
);
-- Con: many nullable columns; OK for small number of subtypes

-- Pattern 2: Concrete Table Inheritance (CTI) / Table-Per-Type
CREATE TABLE email_notifications (
  id        BIGSERIAL PRIMARY KEY,
  recipient TEXT NOT NULL,
  subject   TEXT NOT NULL,
  body      TEXT NOT NULL
);
CREATE TABLE sms_notifications (
  id        BIGSERIAL PRIMARY KEY,
  phone     TEXT NOT NULL,
  body      TEXT NOT NULL
);
-- Con: no single FK to "a notification"; queries across types need UNION

-- Pattern 3: Class Table Inheritance (shared PK)
CREATE TABLE notifications_base (
  id      BIGSERIAL PRIMARY KEY,
  type    TEXT NOT NULL,
  body    TEXT NOT NULL,
  sent_at TIMESTAMPTZ
);
CREATE TABLE email_notifications (
  id        BIGINT PRIMARY KEY REFERENCES notifications_base(id),
  recipient TEXT NOT NULL,
  subject   TEXT NOT NULL
);
-- Pro: FK to notifications_base works for any subtype

Self-Referencing Tables (Hierarchies)

-- Pattern 1: Adjacency List (simple, but recursive queries needed)
CREATE TABLE categories (
  id        BIGSERIAL PRIMARY KEY,
  name      TEXT   NOT NULL,
  parent_id BIGINT REFERENCES categories(id)  -- NULL = root
);

-- Query subtree (PostgreSQL recursive CTE)
WITH RECURSIVE subtree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM   categories WHERE id = 5        -- start node
  UNION ALL
  SELECT c.id, c.name, c.parent_id, s.depth + 1
  FROM   categories c
  JOIN   subtree s ON c.parent_id = s.id
)
SELECT * FROM subtree ORDER BY depth;

-- Pattern 2: Closure Table (fast reads, more storage)
CREATE TABLE category_ancestors (
  ancestor_id   BIGINT NOT NULL REFERENCES categories(id),
  descendant_id BIGINT NOT NULL REFERENCES categories(id),
  depth         INT    NOT NULL,
  PRIMARY KEY (ancestor_id, descendant_id)
);
-- All descendants of category 5, any depth:
SELECT descendant_id FROM category_ancestors WHERE ancestor_id = 5;

Soft Deletes

-- Add deleted_at instead of physically removing rows
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;

-- Always filter in queries:
SELECT * FROM users WHERE deleted_at IS NULL;

-- Partial index to keep active-user lookups fast:
CREATE UNIQUE INDEX idx_users_email_active
  ON users(email)
  WHERE deleted_at IS NULL;

-- Pro: recoverable, audit trail preserved
-- Con: every query needs the WHERE clause; use RLS or views to enforce

Audit Columns

-- Standard audit columns on every table
CREATE TABLE products (
  id          BIGSERIAL    PRIMARY KEY,
  name        TEXT         NOT NULL,
  -- ... business columns ...
  created_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
  created_by  BIGINT       REFERENCES users(id),
  updated_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
  updated_by  BIGINT       REFERENCES users(id)
);

-- Auto-update updated_at via trigger
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$;

CREATE TRIGGER trg_products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Multi-Tenancy Patterns

-- Shared schema + Row-Level Security (PostgreSQL)
CREATE TABLE tasks (
  id         BIGSERIAL PRIMARY KEY,
  tenant_id  BIGINT NOT NULL REFERENCES tenants(id),
  title      TEXT   NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- RLS policy: users only see their tenant's rows
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

CREATE POLICY tasks_tenant_isolation ON tasks
  USING (tenant_id = current_setting('app.tenant_id')::BIGINT);

-- Application sets session variable on connection:
-- SET app.tenant_id = '42';

Versioning / Temporal Tables

-- History table pattern: keep full change history
CREATE TABLE product_prices (
  id          BIGSERIAL   PRIMARY KEY,
  product_id  BIGINT      NOT NULL REFERENCES products(id),
  price       NUMERIC(10,2) NOT NULL,
  valid_from  TIMESTAMPTZ NOT NULL DEFAULT now(),
  valid_to    TIMESTAMPTZ,          -- NULL = current record
  CONSTRAINT no_overlap EXCLUDE USING gist (
    product_id WITH =,
    tstzrange(valid_from, valid_to) WITH &&
  )
);

-- Current price:
SELECT price FROM product_prices
WHERE  product_id = 42 AND valid_to IS NULL;

-- Price at a point in time:
SELECT price FROM product_prices
WHERE  product_id = 42
  AND  valid_from <= '2025-01-01' AND (valid_to IS NULL OR valid_to > '2025-01-01');

7. Indexing Strategy

Indexes trade write overhead and storage for read speed. The wrong index strategy can hurt performance more than no indexes at all.

Index Types (PostgreSQL)

Composite Index Column Order

-- Index on (tenant_id, status, created_at)
-- Serves:  WHERE tenant_id = 1
--          WHERE tenant_id = 1 AND status = 'active'
--          WHERE tenant_id = 1 AND status = 'active' AND created_at > '2025-01-01'
-- Does NOT serve: WHERE status = 'active'  (no leftmost prefix)

CREATE INDEX idx_tasks_tenant_status_created
  ON tasks(tenant_id, status, created_at DESC);

Covering Indexes

-- A covering index satisfies the entire query from the index alone
-- (no heap fetch needed = "index-only scan")

-- Query: SELECT email, name FROM users WHERE status = 'active'
CREATE INDEX idx_users_status_covering
  ON users(status)
  INCLUDE (email, name);  -- INCLUDE columns are in leaf pages only, not sort key

Partial Indexes

-- Index only the rows that matter — smaller, faster
CREATE INDEX idx_orders_open ON orders(user_id, placed_at)
  WHERE status != 'completed';    -- only open orders are queried by user_id

CREATE UNIQUE INDEX idx_users_email_active ON users(email)
  WHERE deleted_at IS NULL;       -- unique email constraint only for active users

Expression Indexes

-- Index a function result — the query must use the exact same expression
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Now this uses the index:
SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- But this does NOT:
SELECT * FROM users WHERE email = '[email protected]'; -- case-sensitive, different expression

When NOT to Index

• Write-heavy tables — every index slows INSERT/UPDATE/DELETE
• Low-cardinality columns — boolean, small enum (few distinct values = poor selectivity)
• Small tables — sequential scan is faster than index lookup for tables < ~1000 rows
• Rarely queried columns — index overhead without benefit

Reading EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

-- Key terms to look for:
-- Index Scan       → good, using an index
-- Index Only Scan  → best, no heap fetch
-- Bitmap Heap Scan → good for moderate result sets
-- Seq Scan         → may be fine for small tables or full-table reads
-- Nested Loop      → good for small outer loops
-- Hash Join        → good for larger datasets
-- Sort (external)  → bad, spilling to disk; add work_mem or an index
--
-- "rows=1000 (actual rows=1 width=200)" → estimate vs actual: big discrepancy
--  means stale statistics → run ANALYZE

8. Constraints & Data Integrity

Constraint Reference

CREATE TABLE orders (
  -- NOT NULL: column must have a value
  id          BIGSERIAL    PRIMARY KEY,
  user_id     BIGINT       NOT NULL REFERENCES users(id),

  -- DEFAULT: value when not specified in INSERT
  status      TEXT         NOT NULL DEFAULT 'pending',
  placed_at   TIMESTAMPTZ  NOT NULL DEFAULT now(),

  -- CHECK: arbitrary boolean expression
  total_cents BIGINT       NOT NULL CHECK (total_cents >= 0),
  status_enum TEXT         NOT NULL CHECK (status IN ('pending','processing','shipped','delivered','cancelled')),

  -- UNIQUE: column or combination must be unique
  external_ref TEXT        UNIQUE,      -- inline
  UNIQUE (user_id, placed_at)           -- table-level composite unique
);

-- Named constraints (easier to diagnose violations)
ALTER TABLE orders
  ADD CONSTRAINT chk_orders_total_non_negative CHECK (total_cents >= 0),
  ADD CONSTRAINT chk_orders_status CHECK (status IN ('pending','processing','shipped','delivered','cancelled'));

Foreign Key Actions

-- ON DELETE / ON UPDATE actions:
--   RESTRICT   (default) — error if referenced row exists
--   NO ACTION  — like RESTRICT but checked at end of transaction
--   CASCADE    — delete/update child rows automatically
--   SET NULL   — set FK column to NULL
--   SET DEFAULT — set FK column to its default value

CREATE TABLE order_items (
  id         BIGSERIAL PRIMARY KEY,
  order_id   BIGINT NOT NULL REFERENCES orders(id)   ON DELETE CASCADE,    -- items follow order
  product_id BIGINT         REFERENCES products(id)  ON DELETE SET NULL,   -- keep item if product removed
  quantity   INT    NOT NULL CHECK (quantity > 0)
);

Exclusion Constraints (PostgreSQL)

-- Prevent overlapping reservations for the same resource
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_reservations (
  id       BIGSERIAL PRIMARY KEY,
  room_id  BIGINT NOT NULL,
  reserved TSTZRANGE NOT NULL,
  EXCLUDE USING gist (room_id WITH =, reserved WITH &&)
  -- "room_id must be equal AND reserved ranges must overlap" cannot both be true
);

Domain Types

-- Create a reusable constrained type
CREATE DOMAIN email_address AS TEXT
  CHECK (VALUE ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$');

CREATE DOMAIN positive_money AS NUMERIC(12,2)
  CHECK (VALUE >= 0);

CREATE TABLE invoices (
  id              BIGSERIAL      PRIMARY KEY,
  customer_email  email_address  NOT NULL,
  amount          positive_money NOT NULL
);

Constraint Naming Convention

9. Dimensional Modeling (Data Warehouse)

Dimensional modeling (Ralph Kimball methodology) organizes data warehouse schemas for analytical queries. The goal is fast aggregation, understandable structure, and a single consistent view of business metrics.

Star Schema

-- ASCII Star Schema Diagram
--
--              dim_date
--              ========
--              date_key (PK)
--              full_date
--              year, quarter, month, day_of_week
--                    |
--                    | date_key (FK)
--                    |
-- dim_customer ---fact_sales--- dim_product
-- ============   ==========    ===========
-- customer_key   order_key(DD) product_key (PK)
-- (PK)           date_key(FK)  product_name
-- name           customer_key  category
-- city           product_key   brand
-- segment        store_key     list_price
--                units_sold
--                revenue           dim_store
--                discount          =========
--                    |             store_key (PK)
--                    +-----------> store_name
--                      store_key   region
--                                  country

-- Star schema DDL
CREATE TABLE dim_date (
  date_key    INT PRIMARY KEY,  -- surrogate: YYYYMMDD as int
  full_date   DATE NOT NULL,
  year        SMALLINT NOT NULL,
  quarter     SMALLINT NOT NULL,
  month       SMALLINT NOT NULL,
  month_name  TEXT NOT NULL,
  week_of_year SMALLINT NOT NULL,
  day_of_week SMALLINT NOT NULL,
  day_name    TEXT NOT NULL,
  is_weekend  BOOLEAN NOT NULL,
  is_holiday  BOOLEAN NOT NULL DEFAULT false
);

CREATE TABLE dim_customer (
  customer_key  BIGSERIAL  PRIMARY KEY,      -- surrogate key
  customer_id   BIGINT     NOT NULL,         -- natural/source key
  name          TEXT       NOT NULL,
  email         TEXT       NOT NULL,
  city          TEXT,
  state         TEXT,
  country       TEXT       NOT NULL,
  segment       TEXT,
  effective_from DATE      NOT NULL,
  effective_to   DATE,                       -- NULL = current
  is_current     BOOLEAN   NOT NULL DEFAULT true
);

CREATE TABLE dim_product (
  product_key   BIGSERIAL PRIMARY KEY,
  product_id    BIGINT    NOT NULL,
  name          TEXT      NOT NULL,
  category      TEXT      NOT NULL,
  subcategory   TEXT,
  brand         TEXT,
  list_price    NUMERIC(10,2),
  effective_from DATE     NOT NULL,
  effective_to   DATE,
  is_current     BOOLEAN  NOT NULL DEFAULT true
);

CREATE TABLE fact_sales (
  -- Surrogate key for fact (optional but useful for updates)
  sale_key      BIGSERIAL PRIMARY KEY,
  -- Dimension foreign keys
  date_key      INT    NOT NULL REFERENCES dim_date(date_key),
  customer_key  BIGINT NOT NULL REFERENCES dim_customer(customer_key),
  product_key   BIGINT NOT NULL REFERENCES dim_product(product_key),
  -- Degenerate dimension (no separate dim table needed)
  order_number  TEXT   NOT NULL,
  -- Measures (fully additive)
  quantity      INT    NOT NULL,
  unit_price    NUMERIC(10,2) NOT NULL,
  discount      NUMERIC(10,2) NOT NULL DEFAULT 0,
  revenue       NUMERIC(12,2) NOT NULL,  -- pre-computed: quantity * (unit_price - discount)
  cost          NUMERIC(12,2)
);

Snowflake Schema

A snowflake schema normalizes dimension tables, reducing storage but adding joins. Use when dimension tables are large and shared sub-dimensions (e.g., geography used by both customer and store dimensions) justify the complexity.

-- Snowflaked geography dimension
CREATE TABLE dim_country (
  country_key SMALLINT PRIMARY KEY,
  iso_code    CHAR(2)  NOT NULL,
  name        TEXT     NOT NULL,
  region      TEXT
);

CREATE TABLE dim_state (
  state_key   INT PRIMARY KEY,
  country_key SMALLINT NOT NULL REFERENCES dim_country(country_key),
  name        TEXT     NOT NULL,
  abbreviation CHAR(3)
);

CREATE TABLE dim_customer (
  customer_key BIGSERIAL PRIMARY KEY,
  name         TEXT  NOT NULL,
  state_key    INT   REFERENCES dim_state(state_key),  -- join to state → country
  segment      TEXT
);

Star vs Snowflake

Conformed Dimensions & Bus Architecture

10. Fact Tables

Types of Fact Tables

Transaction Fact Table

One row per discrete business event. The most common type. Naturally large. Fully additive measures.

-- Grain: one row per individual product line item on an order
CREATE TABLE fact_order_line (
  line_key     BIGSERIAL PRIMARY KEY,
  date_key     INT    NOT NULL,
  order_key    BIGINT NOT NULL,  -- degenerate dimension
  customer_key BIGINT NOT NULL,
  product_key  BIGINT NOT NULL,
  quantity     INT    NOT NULL,
  unit_price   NUMERIC(10,2) NOT NULL,
  revenue      NUMERIC(12,2) NOT NULL,
  cost         NUMERIC(12,2)
);

Periodic Snapshot Fact Table

One row per entity per time period, regardless of whether an event occurred. Captures state at regular intervals.

-- Grain: one row per account per day
CREATE TABLE fact_account_daily_balance (
  snapshot_date_key  INT    NOT NULL,
  account_key        BIGINT NOT NULL,
  closing_balance    NUMERIC(14,2) NOT NULL,
  transactions_count INT    NOT NULL DEFAULT 0,
  PRIMARY KEY (snapshot_date_key, account_key)
);
-- Use case: daily balance snapshots, inventory levels, headcount

Accumulating Snapshot Fact Table

One row per business process lifecycle instance. The row is updated as the process progresses through stages. Useful for pipeline/funnel analysis.

-- Grain: one row per order (updated as order progresses)
CREATE TABLE fact_order_lifecycle (
  order_key        BIGINT PRIMARY KEY,
  customer_key     BIGINT NOT NULL,
  -- Each milestone gets its own date FK
  order_date_key       INT,
  payment_date_key     INT,
  fulfillment_date_key INT,
  ship_date_key        INT,
  delivery_date_key    INT,
  return_date_key      INT,
  -- Lag measures (derived from milestones)
  days_to_payment      SMALLINT,
  days_to_ship         SMALLINT,
  days_to_deliver      SMALLINT,
  -- Current state
  current_status       TEXT NOT NULL
);
-- When an order ships, UPDATE the row: SET ship_date_key = ..., days_to_ship = ...

Factless Fact Tables

-- Records that an event occurred (no numeric measures)
-- Use case: student attended class, product was on promotion

CREATE TABLE fact_student_attendance (
  date_key    INT    NOT NULL,
  student_key BIGINT NOT NULL,
  course_key  BIGINT NOT NULL,
  PRIMARY KEY (date_key, student_key, course_key)
);

-- Count is the implied measure: how many students attended course X on date Y?
SELECT c.name, COUNT(*) AS attendees
FROM   fact_student_attendance f
JOIN   dim_course c ON c.course_key = f.course_key
WHERE  f.date_key = 20250301
GROUP BY c.name;

Measure Additivity

-- Non-additive: don't SUM conversion_rate — derive it from components
-- Bad:
SELECT SUM(conversion_rate) FROM fact_campaign;  -- meaningless

-- Good: compute from additive measures
SELECT campaign_key,
       SUM(conversions)::FLOAT / NULLIF(SUM(impressions), 0) AS conversion_rate
FROM   fact_campaign
GROUP BY campaign_key;

11. Dimension Tables & Slowly Changing Dimensions

Slowly Changing Dimensions (SCD) Types

SCD Type 2 — Implementation

-- dim_customer with SCD Type 2
CREATE TABLE dim_customer (
  customer_key   BIGSERIAL PRIMARY KEY,   -- surrogate key
  customer_id    BIGINT    NOT NULL,       -- natural/business key (not unique here!)
  name           TEXT      NOT NULL,
  email          TEXT      NOT NULL,
  city           TEXT,
  customer_segment TEXT,
  effective_from  DATE     NOT NULL,
  effective_to    DATE,                    -- NULL = current record
  is_current      BOOLEAN  NOT NULL DEFAULT true,
  row_hash        TEXT     NOT NULL        -- hash of SCD columns for change detection
);

-- Index for current-record lookups
CREATE INDEX idx_dim_customer_id_current
  ON dim_customer(customer_id) WHERE is_current = true;

-- When a customer changes segment, expire old row and insert new:
BEGIN;
  UPDATE dim_customer
  SET    effective_to = CURRENT_DATE - 1,
         is_current   = false
  WHERE  customer_id = 1001 AND is_current = true;

  INSERT INTO dim_customer (customer_id, name, email, city, customer_segment, effective_from, row_hash)
  VALUES (1001, 'Alice', '[email protected]', 'NYC', 'Premium', CURRENT_DATE,
          md5('[email protected]|NYC|Premium'));
COMMIT;

-- Fact table join at point in time (using accumulating snapshot):
SELECT f.revenue, c.customer_segment
FROM   fact_sales f
JOIN   dim_customer c ON c.customer_key = f.customer_key;
-- The customer_key in fact_sales points to the row that was current at sale time

SCD Type 3 — Previous Value Column

-- Only tracks current and one previous value
CREATE TABLE dim_customer_type3 (
  customer_key          BIGSERIAL PRIMARY KEY,
  customer_id           BIGINT    NOT NULL UNIQUE,
  name                  TEXT      NOT NULL,
  current_segment       TEXT      NOT NULL,
  previous_segment      TEXT,        -- populated when segment changes
  segment_changed_at    DATE
);

Junk Dimensions

Combine low-cardinality flags and indicators from a fact table into a single junk dimension to reduce fact table width.

-- Instead of 5 flag columns in fact_order, create a junk dim
CREATE TABLE dim_order_flags (
  flag_key            SMALLINT PRIMARY KEY,
  is_gift_wrap        BOOLEAN NOT NULL,
  is_expedited        BOOLEAN NOT NULL,
  is_first_order      BOOLEAN NOT NULL,
  payment_method_type TEXT    NOT NULL,
  channel             TEXT    NOT NULL
);
-- 2^3 * 3 * 4 = 96 possible combinations — small, pre-populated table

Role-Playing Dimensions

-- Same dim_date used multiple times in fact_order with different roles
CREATE TABLE fact_order (
  order_key          BIGINT  PRIMARY KEY,
  order_date_key     INT     NOT NULL REFERENCES dim_date(date_key),
  required_date_key  INT     REFERENCES dim_date(date_key),
  ship_date_key      INT     REFERENCES dim_date(date_key),
  -- The same dim_date table serves three different roles
  ...
);

12. Data Vault Modeling

Data Vault is an alternative DW modeling methodology designed for auditability, flexibility, and parallelized loading. It separates business keys, relationships, and descriptive attributes into distinct table types.

Core Table Types

-- HUB: stores business keys + metadata (no business attributes)
CREATE TABLE hub_customer (
  customer_hk     CHAR(32) PRIMARY KEY,  -- hash of business key
  customer_bk     TEXT     NOT NULL,      -- business key (e.g., CRM ID)
  load_date       TIMESTAMPTZ NOT NULL DEFAULT now(),
  record_source   TEXT     NOT NULL       -- which system this came from
);

-- LINK: stores relationships between hubs (many-to-many friendly)
CREATE TABLE link_order_customer (
  order_customer_hk CHAR(32) PRIMARY KEY,  -- hash of both HKs
  order_hk          CHAR(32) NOT NULL REFERENCES hub_order(order_hk),
  customer_hk       CHAR(32) NOT NULL REFERENCES hub_customer(customer_hk),
  load_date         TIMESTAMPTZ NOT NULL DEFAULT now(),
  record_source     TEXT     NOT NULL
);

-- SATELLITE: stores descriptive attributes + full history
CREATE TABLE sat_customer_details (
  customer_hk    CHAR(32) NOT NULL REFERENCES hub_customer(customer_hk),
  load_date      TIMESTAMPTZ NOT NULL DEFAULT now(),
  load_end_date  TIMESTAMPTZ,  -- NULL = current
  record_source  TEXT NOT NULL,
  hash_diff      CHAR(32) NOT NULL,  -- hash of satellite columns (change detection)
  name           TEXT,
  email          TEXT,
  city           TEXT,
  PRIMARY KEY (customer_hk, load_date)
);

Data Vault vs Kimball Comparison

13. Modern Data Modeling Approaches

Medallion Architecture (Lakehouse)

-- Bronze → Silver → Gold

-- BRONZE: Raw ingestion, no transformation, append-only
-- Source system → Bronze (one table per source entity)
-- bronze.raw_orders: exact copy of source, extra load_timestamp column

-- SILVER: Cleaned, conformed, deduplicated, joined
-- Bronze → Silver (business logic applied)
-- silver.orders: valid records, types cast, nulls handled, deduped

-- GOLD: Aggregated, business-ready
-- Silver → Gold (analytical models)
-- gold.fact_daily_revenue: pre-aggregated for BI tools

dbt (data build tool)

-- dbt models are just SELECT statements in .sql files
-- dbt handles materialization (view, table, incremental, ephemeral)

-- models/silver/orders.sql
{{ config(materialized='incremental', unique_key='order_id') }}

SELECT
    o.id                                 AS order_id,
    o.user_id,
    u.email                              AS user_email,
    o.total_cents / 100.0                AS total_amount,
    o.status,
    o.created_at
FROM {{ source('raw', 'orders') }}  o
JOIN {{ source('raw', 'users')  }}  u ON u.id = o.user_id
WHERE o.created_at IS NOT NULL

{% if is_incremental() %}
  AND o.updated_at > (SELECT MAX(created_at) FROM {{ this }})
{% endif %}

ELT & Schema-on-Read

One Big Table (OBT)

Feature Stores for ML

-- Feature store tables: precomputed features at entity + timestamp grain
CREATE TABLE user_features (
  user_id         BIGINT       NOT NULL,
  feature_date    DATE         NOT NULL,
  -- Behavioral features
  orders_last_30d     INT,
  avg_order_value     NUMERIC(10,2),
  days_since_last_order INT,
  preferred_category  TEXT,
  -- Risk features
  failed_payments_90d INT,
  chargeback_count    INT,
  PRIMARY KEY (user_id, feature_date)
);
-- Point-in-time correct joins: feature_date <= event_date prevents leakage

14. Schema Evolution & Migration

Safe vs Breaking Changes

Expand-Contract Pattern

# Rename column safely: old_name → new_name
#
# Phase 1: EXPAND
#   - Add new column (new_name), nullable
#   - Dual-write in application: write both old_name and new_name
#   - Deploy app
#
# Phase 2: BACKFILL
#   - UPDATE table SET new_name = old_name WHERE new_name IS NULL
#   - Add NOT NULL constraint on new_name (after backfill complete)
#
# Phase 3: MIGRATE READS
#   - Update all queries to use new_name
#   - Deploy app (now reading only new_name)
#
# Phase 4: CONTRACT
#   - Stop writing to old_name in application
#   - Deploy app
#   - DROP COLUMN old_name

Alembic (Python) Example

"""Add subscription_tier to users

Revision ID: a3f7c81d2e5b
"""
from alembic import op
import sqlalchemy as sa

def upgrade() -> None:
    # Phase 1: add nullable first (online, no lock)
    op.add_column('users',
        sa.Column('subscription_tier', sa.String(20), nullable=True)
    )
    # Phase 2: backfill existing rows
    op.execute("UPDATE users SET subscription_tier = 'free' WHERE subscription_tier IS NULL")
    # Phase 3: now add NOT NULL constraint
    op.alter_column('users', 'subscription_tier',
        existing_type=sa.String(20),
        nullable=False,
        server_default='free'
    )

def downgrade() -> None:
    op.drop_column('users', 'subscription_tier')

Online Schema Changes

# gh-ost (GitHub): online schema change for MySQL — reads binlog, shadow table
gh-ost \
  --user="root" --password="..." \
  --host="primary-db" \
  --database="myapp" \
  --table="orders" \
  --alter="ADD COLUMN shipment_method VARCHAR(50)" \
  --execute

# pt-online-schema-change (Percona): MySQL
pt-online-schema-change \
  --alter "ADD COLUMN shipment_method VARCHAR(50)" \
  D=myapp,t=orders \
  --execute

# PostgreSQL: most DDL is transactional; use CONCURRENTLY for indexes
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);
-- Takes longer but acquires no lock that blocks reads/writes

15. Data Types & Storage

Choosing the Right Type

UUID v4 vs v7

-- UUID v4: completely random
-- 550e8400-e29b-41d4-a716-446655440000
-- Pro: globally unique, safe to expose
-- Con: random inserts cause B-tree page splits = index fragmentation

-- UUID v7: time-sortable (first 48 bits = millisecond timestamp)
-- 01952dce-a1b2-7xxx-xxxx-xxxxxxxxxxxx
-- Pro: same uniqueness + monotonically increasing = sequential inserts
-- Con: encodes timestamp (minor privacy consideration)
-- Support: PostgreSQL 17+ pg_uuidv7 extension, or generate in app layer

-- Storage comparison for 10M rows:
-- BIGSERIAL: 8 bytes × 10M = 80MB primary key index
-- UUID:     16 bytes × 10M = 160MB primary key index

JSONB vs Relational Columns

-- Use JSONB for:
-- - Varying attributes across rows (product specs, config objects)
-- - Schemaless third-party data you don't control
-- - Rapid prototyping where schema is still evolving

-- Use relational columns for:
-- - Frequently filtered/sorted attributes
-- - Attributes with FK relationships
-- - Data integrity requirements (NOT NULL, CHECK constraints on JSONB are verbose)

-- JSONB query operators
SELECT * FROM products WHERE attributes @> '{"color": "red"}';          -- containment
SELECT * FROM products WHERE attributes ? 'weight_kg';                  -- key exists
SELECT attributes->>'color' FROM products WHERE id = 1;                 -- extract as text
SELECT attributes->'dimensions'->>'height' FROM products WHERE id = 1; -- nested

-- GIN index for JSONB
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- Index a specific path
CREATE INDEX idx_products_color ON products ((attributes->>'color'));

Enums vs Lookup Tables

16. Performance Considerations

Partitioning

Partitioning splits a large table into smaller physical segments. The planner can skip entire partitions (partition pruning), dramatically reducing I/O.

-- Range partitioning (most common for time-series data)
CREATE TABLE events (
  id          BIGSERIAL,
  event_type  TEXT NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL,
  payload     JSONB
) PARTITION BY RANGE (occurred_at);

-- Create monthly partitions
CREATE TABLE events_2025_01 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- Indexes are created per-partition (or on parent, auto-propagated in PG12+)
CREATE INDEX ON events (occurred_at, event_type);

-- List partitioning (discrete values)
CREATE TABLE orders (
  id       BIGSERIAL,
  region   TEXT NOT NULL,
  ...
) PARTITION BY LIST (region);
CREATE TABLE orders_us   PARTITION OF orders FOR VALUES IN ('us');
CREATE TABLE orders_eu   PARTITION OF orders FOR VALUES IN ('eu', 'uk');

-- Hash partitioning (evenly distribute unknown distribution)
CREATE TABLE users (...) PARTITION BY HASH (id);
CREATE TABLE users_0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE users_1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);

Materialized Views

-- Pre-compute expensive aggregation
CREATE MATERIALIZED VIEW mv_daily_revenue AS
SELECT
    DATE_TRUNC('day', o.placed_at)::DATE AS date,
    p.category,
    COUNT(DISTINCT o.id)                 AS order_count,
    SUM(oi.quantity * oi.unit_price)     AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p    ON p.id = oi.product_id
WHERE o.status = 'delivered'
GROUP BY 1, 2
WITH DATA;  -- populate immediately

CREATE UNIQUE INDEX ON mv_daily_revenue(date, category);

-- Refresh strategies:
REFRESH MATERIALIZED VIEW mv_daily_revenue;          -- full refresh, brief lock
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue;  -- no read lock (needs unique index)

Sharding Strategies

Data Archiving

-- Move old data to archive table to keep hot table small
CREATE TABLE orders_archive (LIKE orders INCLUDING ALL);

-- Archive orders older than 2 years
WITH archived AS (
  DELETE FROM orders
  WHERE  placed_at < NOW() - INTERVAL '2 years'
  RETURNING *
)
INSERT INTO orders_archive SELECT * FROM archived;

-- Or use partitioning + detach:
-- DROP the oldest partition (or move it to cheaper storage / cold tier)
ALTER TABLE events DETACH PARTITION events_2022_01;

17. Data Quality & Governance

Data Quality Dimensions

dbt Tests

# models/schema.yml
models:
  - name: orders
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: user_id
        tests:
          - not_null
          - relationships:
              to: ref('users')
              field: id
      - name: status
        tests:
          - accepted_values:
              values: ['pending','processing','shipped','delivered','cancelled']
      - name: total_amount
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: ">= 0"

PII & GDPR/CCPA Schema Considerations

-- Data classification table
COMMENT ON COLUMN users.email      IS 'PII:DIRECT — GDPR Art.4, encrypt at rest';
COMMENT ON COLUMN users.ip_address IS 'PII:INDIRECT — retain max 90 days';

-- Right to erasure: soft-delete + anonymize
UPDATE users
SET  email      = 'deleted-' || id || '@anonymized.invalid',
     first_name = 'Deleted',
     last_name  = 'User',
     phone      = NULL,
     deleted_at = now()
WHERE id = :user_id;

-- Data minimization: store only what you need
-- Bad: store full DOB for age check
-- Good: store only is_adult (boolean) after verification, discard DOB

-- Retention policy enforcement (run via scheduled job)
DELETE FROM user_events
WHERE  occurred_at < NOW() - INTERVAL '90 days'
  AND  event_type IN ('page_view', 'click');  -- ephemeral behavioral data

18. Anti-Patterns & Common Mistakes

Entity-Attribute-Value (EAV)

-- ANTI-PATTERN: EAV — extreme flexibility at the cost of everything else
CREATE TABLE product_attributes (
  product_id  BIGINT,
  attr_name   TEXT,    -- 'color', 'weight', 'size', 'material', ...
  attr_value  TEXT     -- all values as text, no type safety
);
-- Problems:
-- - No data types (everything is TEXT — enforce color is a valid color how?)
-- - No NOT NULL per attribute
-- - No FK constraints
-- - Queries require self-joins or PIVOT to reconstruct a row:
--   SELECT MAX(CASE WHEN attr_name='color' THEN attr_value END) AS color,
--          MAX(CASE WHEN attr_name='weight' THEN attr_value END) AS weight
--   FROM product_attributes WHERE product_id = 1;
-- - Performance: scanning millions of attribute rows for one product

-- BETTER: Use JSONB for varying attributes
ALTER TABLE products ADD COLUMN attributes JSONB;
-- Or: use Class Table Inheritance if structure varies by category

Storing Comma-Separated Values

-- ANTI-PATTERN: CSV in a column
CREATE TABLE posts (
  id   INT,
  tags TEXT  -- 'python,backend,api'  ← violates 1NF
);
-- Cannot index tags efficiently
-- Cannot enforce FK to a tags table
-- Searching requires LIKE '%python%' (full table scan)
-- Counting tags requires string parsing

-- CORRECT: junction table
CREATE TABLE post_tags (
  post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  tag_id  BIGINT NOT NULL REFERENCES tags(id),
  PRIMARY KEY (post_id, tag_id)
);

FLOAT for Money

-- ANTI-PATTERN: FLOAT loses precision
CREATE TABLE bad_payments (
  amount FLOAT  -- 10.10 stored as 10.099999999999999289...
);

-- IEEE 754 cannot represent 0.1 exactly in binary
-- SELECT 0.1 + 0.2 = 0.30000000000000004 in many floating-point systems

-- CORRECT: NUMERIC for exact decimal arithmetic
CREATE TABLE payments (
  amount_cents BIGINT       NOT NULL,  -- store as integer cents (preferred)
  -- OR:
  amount       NUMERIC(12,2) NOT NULL  -- fixed-point decimal
);

Polymorphic Foreign Keys (Unconstrained)

-- ANTI-PATTERN: generic FK that points to any table
CREATE TABLE comments (
  id             BIGSERIAL PRIMARY KEY,
  commentable_id BIGINT NOT NULL,    -- could point to posts, photos, videos...
  commentable_type TEXT NOT NULL,    -- 'Post', 'Photo', 'Video'
  body           TEXT NOT NULL
);
-- Database cannot enforce referential integrity — no real FK
-- The 'type' string is fragile (typos, renames)

-- BETTER: separate FK columns (nullable, only one populated)
CREATE TABLE comments (
  id       BIGSERIAL PRIMARY KEY,
  post_id  BIGINT REFERENCES posts(id),
  photo_id BIGINT REFERENCES photos(id),
  video_id BIGINT REFERENCES videos(id),
  body     TEXT NOT NULL,
  CHECK (
    (post_id IS NOT NULL)::INT +
    (photo_id IS NOT NULL)::INT +
    (video_id IS NOT NULL)::INT = 1  -- exactly one must be set
  )
);

Other Anti-Patterns Quick Reference

19. Tools & Visualization

ER Diagram Tools

Mermaid ER Diagram

-- Mermaid ER diagram syntax (rendered in GitHub, Notion, etc.)
--
-- erDiagram
--     CUSTOMER ||--o{ ORDER : places
--     ORDER ||--|{ ORDER_ITEM : contains
--     PRODUCT ||--o{ ORDER_ITEM : included-in
--     CATEGORY ||--o{ PRODUCT : categorizes
--
--     CUSTOMER {
--         bigint id PK
--         text email
--         text name
--     }
--     ORDER {
--         bigint id PK
--         bigint customer_id FK
--         text status
--         timestamptz placed_at
--     }

dbt Documentation

# Generate and serve dbt docs (includes lineage DAG + schema docs)
dbt docs generate
dbt docs serve --port 8080
# Browse to http://localhost:8080 for interactive schema + lineage explorer

Schema Introspection (PostgreSQL)

-- List all tables and row counts
SELECT relname AS table_name,
       n_live_tup AS approx_row_count
FROM   pg_stat_user_tables
ORDER BY n_live_tup DESC;

-- Show all columns with types and constraints
SELECT c.column_name, c.data_type, c.is_nullable, c.column_default
FROM   information_schema.columns c
WHERE  c.table_schema = 'public'
  AND  c.table_name   = 'orders'
ORDER BY c.ordinal_position;

-- Show all foreign keys
SELECT
    tc.table_name, kcu.column_name,
    ccu.table_name AS references_table,
    ccu.column_name AS references_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
     ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
     ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema = 'public';

-- Show index usage (identify unused indexes)
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM   pg_stat_user_indexes
WHERE  schemaname = 'public'
ORDER BY idx_scan ASC;  -- low scan count = possibly unused

20. Decision Framework

Choosing Your Modeling Approach

-- Decision flowchart (ASCII)
--
-- What is the primary workload?
--   │
--   ├── Transactional (CRUD, low-latency)
--   │     │
--   │     └── Use OLTP: normalize to 3NF, surrogate PKs,
--   │           indexes on FK + filter columns, constraints everywhere
--   │
--   └── Analytical (aggregations, historical trends)
--         │
--         ├── Need auditability + source-system independence?
--         │     └── YES → Consider Data Vault
--         │           (hub/link/sat, regulated industries)
--         │
--         └── NO → Dimensional modeling (Kimball)
--               │
--               ├── Dimensions frequently change?
--               │     └── YES → SCD Type 2 on dims
--               │
--               ├── Multiple source systems, many shared dims?
--               │     └── YES → Conformed dims + bus architecture
--               │
--               └── Simple analytics, small team?
--                     └── Star schema + dbt for transformation

Key Questions Before Designing Any Schema

1. What are the 5 most important queries this system must answer? — Design indexes and grain around these.
2. What is the expected write volume? — High-write systems need fewer indexes and simpler FK chains.
3. How often will the schema evolve? — Frequent changes favor JSONB attributes or ELT patterns; stable schemas favor strict normalization.
4. Who are the consumers? — Application code (needs consistency), analysts (need simplicity), ML pipelines (need feature tables with point-in-time correctness).
5. What is the data volume in 1 year? 3 years? — Drives partitioning, archiving, and whether you need a separate OLAP layer.
6. What are the consistency requirements? — Financial data: ACID + FK constraints required. Event streams: eventual consistency may be acceptable.
7. What are the compliance requirements? — PII → right to erasure design. Financial → audit trail. HIPAA → access logging + encryption.

Schema Design Review Checklist