database-schema-design

What this skill does

# database-schema-design

Comprehensive database schema design patterns for PostgreSQL and MySQL with normalization, relationships, constraints, and error prevention.

---

## Quick Start (10 Minutes)

**Step 1**: Choose your schema pattern from templates:
```bash
# Basic schema with users, products, orders
cat templates/basic-schema.sql

# Relationship patterns (1:1, 1:M, M:M)
cat templates/relationships.sql

# Constraint examples
cat templates/constraints.sql

# Audit patterns
cat templates/audit-columns.sql
```

**Step 2**: Apply normalization rules (at minimum 3NF):
- **1NF**: No repeating groups, atomic values
- **2NF**: No partial dependencies on composite keys
- **3NF**: No transitive dependencies
- **Load** `references/normalization-guide.md` for detailed examples

**Step 3**: Add essential elements to every table:
```sql
CREATE TABLE your_table (
  -- Primary key (required)
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Business columns with proper types
  name VARCHAR(200) NOT NULL,  -- Use appropriate lengths

  -- Audit columns (always include)
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);
```

---

## Critical Rules

### ✓ Always Do

| Rule | Reason |
|------|--------|
| **Every table has PRIMARY KEY** | Ensures row uniqueness, enables relationships |
| **Foreign keys defined explicitly** | Enforces referential integrity, prevents orphans |
| **Index all foreign keys** | Prevents slow JOINs, critical for performance |
| **NOT NULL on required fields** | Data integrity, prevents NULL pollution |
| **Audit columns (created_at, updated_at)** | Track changes, debugging, compliance |
| **Appropriate data types** | Storage efficiency, validation, indexing |
| **Check constraints for enums** | Enforces valid values at database level |
| **ON DELETE/UPDATE rules specified** | Prevents accidental data loss or orphans |

### ✗ Never Do

| Anti-Pattern | Why It's Bad |
|--------------|--------------|
| **VARCHAR(MAX) everywhere** | Wastes space, slows indexes, no validation |
| **Dates as VARCHAR** | No date math, no validation, sorting broken |
| **Missing foreign keys** | No referential integrity, orphaned records |
| **Premature denormalization** | Hard to maintain, data anomalies |
| **EAV (Entity-Attribute-Value)** | Query complexity, no type safety, slow |
| **Polymorphic associations** | No foreign key integrity, complex queries |
| **Circular dependencies** | Impossible to populate, breaks CASCADE |
| **No indexes on foreign keys** | Extremely slow JOINs, performance killer |

---

## Top 7 Critical Errors

### Error 1: Missing Primary Key
**Symptom**: Cannot uniquely identify rows, duplicate data
**Fix**:
```sql
-- ❌ Bad
CREATE TABLE users (
  email VARCHAR(255),
  name VARCHAR(100)
);

-- ✅ Good
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL
);
```

### Error 2: No Foreign Key Constraints
**Symptom**: Orphaned records, data inconsistency
**Fix**:
```sql
-- ❌ Bad
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID  -- No constraint!
);

-- ✅ Good
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE
);

-- Index the foreign key
CREATE INDEX idx_orders_user_id ON orders(user_id);
```

### Error 3: VARCHAR(MAX) Everywhere
**Symptom**: Wasted space, slow indexes, no validation
**Fix**:
```sql
-- ❌ Bad
CREATE TABLE products (
  name VARCHAR(MAX),
  sku VARCHAR(MAX),
  status VARCHAR(MAX)
);

-- ✅ Good
CREATE TABLE products (
  name VARCHAR(200) NOT NULL,
  sku VARCHAR(50) UNIQUE NOT NULL,
  status VARCHAR(20) NOT NULL
    CHECK (status IN ('draft', 'active', 'archived'))
);
```

### Error 4: Wrong Data Types (Dates as Strings)
**Symptom**: No date validation, broken sorting, no date math
**Fix**:
```sql
-- ❌ Bad
CREATE TABLE events (
  event_date VARCHAR(50)  -- '2025-12-15' or 'Dec 15, 2025'?
);

-- ✅ Good
CREATE TABLE events (
  event_date DATE NOT NULL,  -- Validated, sortable
  event_time TIMESTAMPTZ     -- With timezone
);
```

### Error 5: No Indexes on Foreign Keys
**Symptom**: Extremely slow JOINs, poor query performance
**Fix**:
```sql
-- Always index foreign keys
CREATE TABLE order_items (
  order_id UUID NOT NULL REFERENCES orders(id),
  product_id UUID NOT NULL REFERENCES products(id)
);

-- ✅ Required indexes
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
```

### Error 6: Missing Audit Columns
**Symptom**: Cannot track when records created/modified
**Fix**:
```sql
-- ❌ Bad
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name VARCHAR(200)
);

-- ✅ Good
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

-- Auto-update trigger (PostgreSQL)
CREATE TRIGGER products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
```

### Error 7: EAV Anti-Pattern
**Symptom**: Complex queries, no type safety, slow performance
**Fix**:
```sql
-- ❌ Bad (EAV)
CREATE TABLE product_attributes (
  product_id UUID,
  attribute_name VARCHAR(100),  -- 'color', 'size', 'price'
  attribute_value TEXT           -- Everything as text!
);

-- ✅ Good (Structured + JSONB)
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  price DECIMAL(10,2) NOT NULL,  -- Required fields as columns
  color VARCHAR(50),              -- Common attributes as columns
  size VARCHAR(20),
  attributes JSONB                -- Optional/dynamic attributes
);

-- Index JSONB
CREATE INDEX idx_products_attributes ON products USING GIN(attributes);
```

**Load** `references/error-catalog.md` for all 12 errors with detailed fixes.

---

## Common Schema Patterns

| Pattern | Use Case | Template |
|---------|----------|----------|
| **Basic CRUD** | Standard users/products/orders | `templates/basic-schema.sql` |
| **One-to-One** | User → Profile | `templates/relationships.sql` (lines 7-17) |
| **One-to-Many** | User → Orders | `templates/relationships.sql` (lines 23-34) |
| **Many-to-Many** | Students ↔ Courses | `templates/relationships.sql` (lines 40-60) |
| **Hierarchical** | Categories tree, org chart | `templates/relationships.sql` (lines 66-83) |
| **Soft Delete** | Mark deleted, keep history | `templates/audit-columns.sql` (lines 55-80) |
| **Versioning** | Track changes over time | `templates/audit-columns.sql` (lines 86-108) |
| **Multi-Tenant** | Isolated data per organization | `references/schema-design-patterns.md` (lines 228-258) |

---

## Normalization Quick Reference

| Form | Rule | Example |
|------|------|---------|
| **1NF** | Atomic values, no repeating groups | `phone1, phone2` → `phones` table |
| **2NF** | 1NF + no partial dependencies | Composite key dependency → separate table |
| **3NF** | 2NF + no transitive dependencies | `user.city` → `city.id` reference |
| **BCNF** | 3NF + every determinant is candidate key | Rare edge cases |
| **4NF** | BCNF + no multi-valued dependencies | Complex many-to-many |
| **5NF** | 4NF + no join dependencies | Very rare, academic |

**Recommendation**: Design to 3NF, denormalize only with measured performance data.

**Load** `references/normalization-guide.md` for detailed examples with before/after.

---

## Configuration Summary

### PostgreSQL Recommended Types

```sql
-- Primary Keys
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
-- OR for performance-critical:
id BIGSERIAL PRIMARY KEY

-- Text
name VARCHAR(200) NOT NULL
description TEXT
code CHAR(10)  -- Fixed-length codes only

-- Numbers
price DECIMAL(10,2) NOT NULL  -- Money: NEVER use FLOAT
quantity INT NOT NULL
rating DECIMAL(3,2)  -- 0.00 to 9.99

-- Dates/Times
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL  -- With timezone
event_date DATE
duration INTERVAL

-- Boolean
is_active BOOL