Database design

Database Design Patterns

Normalization vs Denormalization

-- 3NF: separate concerns, eliminate redundancy
CREATE TABLE users    (id BIGSERIAL PRIMARY KEY, name TEXT, email TEXT UNIQUE);
CREATE TABLE products (id BIGSERIAL PRIMARY KEY, name TEXT, price DECIMAL(10,2));
CREATE TABLE orders   (id BIGSERIAL PRIMARY KEY, user_id BIGINT REFERENCES users, created_at TIMESTAMPTZ);
CREATE TABLE order_items (order_id BIGINT REFERENCES orders, product_id BIGINT REFERENCES products,
                          qty INT, unit_price DECIMAL(10,2));

-- Denormalize for read performance (snapshot price at order time — already above)
-- Materialized: store user_name on orders for fast listing without JOIN
ALTER TABLE orders ADD COLUMN user_name TEXT;  -- updated via trigger or app

Soft Delete

-- Pattern 1: deleted_at timestamp
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;
CREATE INDEX idx_users_active ON users (id) WHERE deleted_at IS NULL;

-- All queries must include WHERE deleted_at IS NULL
-- Use a view to enforce
CREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL;

-- Soft delete
UPDATE users SET deleted_at = NOW() WHERE id = $1;

-- Pattern 2: is_deleted boolean (simpler, less info)
ALTER TABLE users ADD COLUMN is_deleted BOOLEAN NOT NULL DEFAULT FALSE;

Temporal Tables (Audit History)

-- System-period temporal table (valid time via trigger)
CREATE TABLE products (
  id         BIGSERIAL PRIMARY KEY,
  name       TEXT NOT NULL,
  price      DECIMAL(10,2),
  valid_from TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  valid_to   TIMESTAMPTZ NOT NULL DEFAULT 'infinity'
);

CREATE TABLE products_history (LIKE products INCLUDING ALL);

-- Trigger to move old version to history on update
CREATE OR REPLACE FUNCTION products_history_trigger() RETURNS trigger AS $$
BEGIN
  INSERT INTO products_history SELECT OLD.*;
  NEW.valid_from := NOW();
  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER products_versioning
BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION products_history_trigger();

-- Query at a point in time
SELECT * FROM products_history
WHERE id = 42 AND valid_from <= '2024-06-01' AND valid_to > '2024-06-01';

Audit Log Pattern

CREATE TABLE audit_log (
  id          BIGSERIAL PRIMARY KEY,
  table_name  TEXT NOT NULL,
  record_id   BIGINT NOT NULL,
  action      TEXT NOT NULL CHECK (action IN ('INSERT','UPDATE','DELETE')),
  old_data    JSONB,
  new_data    JSONB,
  changed_by  BIGINT,  -- user id
  changed_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION audit_trigger() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log (table_name, record_id, action, old_data, new_data)
  VALUES (TG_TABLE_NAME,
          COALESCE(NEW.id, OLD.id),
          TG_OP,
          CASE WHEN TG_OP != 'INSERT' THEN to_jsonb(OLD) END,
          CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) END);
  RETURN NEW;
END $$ LANGUAGE plpgsql;

Common Schema Patterns

-- UUID primary keys (globally unique, safe for distributed systems)
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ...
);

-- Polymorphic association (with type discriminator)
CREATE TABLE attachments (
  id            BIGSERIAL PRIMARY KEY,
  resource_type TEXT NOT NULL,   -- 'Post', 'Comment', 'User'
  resource_id   BIGINT NOT NULL,
  url           TEXT NOT NULL,
  UNIQUE (resource_type, resource_id, url)
);
CREATE INDEX ON attachments (resource_type, resource_id);

-- ULID-style sortable IDs (time-ordered UUIDs)
-- Use pg_ulid extension or encode timestamp into UUID v7

Design Checklist

• Every table has id, created_at, updated_at
• Foreign keys have indexes
• Soft delete uses partial index on deleted_at IS NULL
• Enums as CHECK constraints or lookup tables, not magic strings
• Monetary values as DECIMAL(19,4), never FLOAT
• Time stored as TIMESTAMPTZ (UTC), not TIMESTAMP
• Audit log on all business-critical tables