database-schema-design

# 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 |
|--------------|--------------|
|