数据库设计

---
name: database-design
description: "When to activate: schema design, normalization, ERD, soft delete, temporal tables, audit log, database modeling"
origin: zakr
metadata:
  namespace: zakr/devtools-pack
  version: 1.0.0
  category: databases
  language: sql
  platforms: [claude-code, codex, gemini-cli, cursor, aider, windsurf]
---
# Database Design Patterns

## Normalization vs Denormalization

```sql
-- 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

```sql
-- 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)

```sql
-- 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

```sql
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

```sql
-- 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