Schema Reviewer Skill

Purpose

Reviews database schema design for normalization, constraints, indexes, and best practices.

When to Use

• Database schema review
• Table design review
• Normalization check
• Index planning
• Constraint validation

Project Detection

• Schema files (.sql, schema.prisma)
• Migration files
• Entity definitions
• Database documentation

Workflow

Step 1: Analyze Schema

**Database**: PostgreSQL/MySQL
**Tables**: 15
**Relationships**: 1:N, N:M
**Normalization**: 3NF

Step 2: Select Review Areas

AskUserQuestion:

"Which areas to review?"
Options:
- Full schema review (recommended)
- Normalization and design
- Constraints and integrity
- Index strategy
- Data type optimization
multiSelect: true

Detection Rules

Normalization

| Check | Recommendation | Severity | |-------|----------------|----------| | Repeating groups | Move to separate table | HIGH | | Partial dependency | Apply 2NF | MEDIUM | | Transitive dependency | Apply 3NF | MEDIUM | | Over-normalization | Consider denormalization for reads | LOW |

-- BAD: 1NF violation (repeating groups)
CREATE TABLE orders (
    id INT PRIMARY KEY,
    product1_id INT,
    product1_qty INT,
    product2_id INT,
    product2_qty INT,
    product3_id INT,
    product3_qty INT
);

-- GOOD: Normalized with separate table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT NOT NULL REFERENCES orders(id),
    product_id INT NOT NULL REFERENCES products(id),
    quantity INT NOT NULL CHECK (quantity > 0),
    price DECIMAL(10,2) NOT NULL
);

-- BAD: 2NF violation (partial dependency)
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- Depends only on product_id!
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- GOOD: Product name in products table only
CREATE TABLE order_items (
    order_id INT REFERENCES orders(id),
    product_id INT REFERENCES products(id),
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Constraints

| Check | Recommendation | Severity | |-------|----------------|----------| | Missing PRIMARY KEY | Add primary key | CRITICAL | | Missing FOREIGN KEY | Add for relationships | HIGH | | No NOT NULL | Add where appropriate | MEDIUM | | No CHECK constraints | Validate data at DB level | MEDIUM | | Missing UNIQUE | Add for natural keys | HIGH |

-- BAD: No constraints
CREATE TABLE users (
    id INT,
    email VARCHAR(255),
    status VARCHAR(20)
);

-- GOOD: Proper constraints
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    status VARCHAR(20) NOT NULL DEFAULT 'active'
        CHECK (status IN ('active', 'inactive', 'banned')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- With proper foreign keys
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id)
        ON DELETE RESTRICT  -- Prevent user deletion with orders
        ON UPDATE CASCADE,
    status VARCHAR(20) NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
    total DECIMAL(12,2) NOT NULL CHECK (total >= 0)
);

Data Types

| Check | Recommendation | Severity | |-------|----------------|----------| | VARCHAR for all strings | Use appropriate types | MEDIUM | | INT for monetary values | Use DECIMAL | HIGH | | FLOAT for money | Use DECIMAL | CRITICAL | | TEXT without limit | Consider VARCHAR with limit | LOW | | Missing timezone | Use TIMESTAMPTZ | HIGH |

-- BAD: Poor data type choices
CREATE TABLE products (
    id INT,
    price FLOAT,              -- Precision issues!
    quantity VARCHAR(10),     -- Should be INT
    created_at TIMESTAMP,     -- No timezone!
    description TEXT          -- Unlimited
);

-- GOOD: Appropriate data types
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    quantity INT NOT NULL DEFAULT 0 CHECK (quantity >= 0),
    description TEXT,  -- OK for long text
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Use ENUMs for fixed values (PostgreSQL)
CREATE TYPE order_status AS ENUM (
    'pending', 'processing', 'shipped', 'delivered', 'cancelled'
);

CREATE TABLE orders (
    status order_status NOT NULL DEFAULT 'pending'
);

Index Strategy

| Check | Recommendation | Severity | |-------|----------------|----------| | No index on FK | Add index | HIGH | | No index on filter columns | Add index | HIGH | | Too many single-column indexes | Use composite | MEDIUM | | Missing unique index | Add for unique constraints | HIGH |

-- Index planning
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id),
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Foreign key index (not automatic in PostgreSQL!)
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Composite index for common queries
-- WHERE status = ? ORDER BY created_at DESC
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

-- Partial index for specific cases
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

-- Cover index (includes all needed columns)
CREATE INDEX idx_orders_user_summary ON orders(user_id, status, total);

Relationship Design

| Check | Recommendation | Severity | |-------|----------------|----------| | N:M without junction table | Create junction table | CRITICAL | | Self-reference without depth | Add level/path column | MEDIUM | | Circular references | Redesign relationships | HIGH |

-- N:M relationship with junction table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL
);

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE product_categories (
    product_id INT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    category_id INT NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
    PRIMARY KEY (product_id, category_id)
);

-- Hierarchical data (adjacency list)
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INT REFERENCES categories(id),
    level INT NOT NULL DEFAULT 0,
    path LTREE  -- PostgreSQL ltree extension
);

-- Create index for hierarchical queries
CREATE INDEX idx_categories_path ON categories USING GIST (path);

Audit Columns

-- Standard audit columns
CREATE TABLE entities (
    id SERIAL PRIMARY KEY,
    -- ... business columns ...
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by INT REFERENCES users(id),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_by INT REFERENCES users(id),
    deleted_at TIMESTAMPTZ,  -- Soft delete
    deleted_by INT REFERENCES users(id)
);

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

CREATE TRIGGER trigger_update_updated_at
    BEFORE UPDATE ON entities
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

Response Template

## Database Schema Review Results

**Database**: PostgreSQL 15
**Tables**: 12 | **Relationships**: 8

### Normalization
| Status | Table | Issue |
|--------|-------|-------|
| HIGH | orders | Repeating product columns |

### Constraints
| Status | Table | Issue |
|--------|-------|-------|
| CRITICAL | users | Missing PRIMARY KEY |
| HIGH | orders | Missing FOREIGN KEY to users |

### Data Types
| Status | Table.Column | Issue |
|--------|--------------|-------|
| CRITICAL | products.price | Using FLOAT instead of DECIMAL |

### Indexes
| Status | Table | Issue |
|--------|-------|-------|
| HIGH | orders.user_id | Missing index on foreign key |

### Recommended Changes
```sql
-- Add missing primary key
ALTER TABLE users ADD PRIMARY KEY (id);

-- Fix price data type
ALTER TABLE products
ALTER COLUMN price TYPE DECIMAL(10,2);

-- Add foreign key index
CREATE INDEX idx_orders_user_id ON orders(user_id);

## Best Practices
1. **Normalization**: 3NF default, denormalize for performance
2. **Constraints**: PK, FK, NOT NULL, CHECK, UNIQUE
3. **Data Types**: DECIMAL for money, TIMESTAMPTZ for time
4. **Indexes**: FK columns, filter columns, composites
5. **Audit**: created_at, updated_at, soft delete

## Integration
- `sql-optimizer`: Query performance
- `migration-checker`: Migration safety
- `orm-reviewer`: ORM mapping