database-schema-designlisted
Install: claude install-skill secondsky/claude-skills
# 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 f