Database schema design for PostgreSQL/MySQL with normalization, relationships, constraints. Use for new databases, schema reviews, migrations, or encountering missing PKs/FKs, wrong data types, premature denormalization, EAV anti-pattern.
/plugin marketplace add secondsky/claude-skills/plugin install database-schema-design@claude-skillsThis skill inherits all available tools. When active, it can use any tool Claude has access to.
references/constraints-catalog.mdreferences/data-types-guide.mdreferences/error-catalog.mdreferences/normalization-guide.mdreferences/relationship-patterns.mdreferences/schema-design-patterns.mdtemplates/audit-columns.sqltemplates/basic-schema.sqltemplates/constraints.sqltemplates/relationships.sqlComprehensive database schema design patterns for PostgreSQL and MySQL with normalization, relationships, constraints, and error prevention.
Step 1: Choose your schema pattern from templates:
# 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):
references/normalization-guide.md for detailed examplesStep 3: Add essential elements to every table:
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
);
| 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 |
| 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 |
Symptom: Cannot uniquely identify rows, duplicate data Fix:
-- ❌ 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
);
Symptom: Orphaned records, data inconsistency Fix:
-- ❌ 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);
Symptom: Wasted space, slow indexes, no validation Fix:
-- ❌ 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'))
);
Symptom: No date validation, broken sorting, no date math Fix:
-- ❌ 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
);
Symptom: Extremely slow JOINs, poor query performance Fix:
-- 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);
Symptom: Cannot track when records created/modified Fix:
-- ❌ 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();
Symptom: Complex queries, no type safety, slow performance Fix:
-- ❌ 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.
| 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) |
| 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.
-- 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 BOOLEAN DEFAULT true NOT NULL
-- JSON
attributes JSONB -- Binary, faster, indexable
-- Enum Alternative (preferred over ENUM type)
status VARCHAR(20) NOT NULL
CHECK (status IN ('draft', 'active', 'archived'))
-- MySQL doesn't have:
TIMESTAMPTZ -- Use TIMESTAMP (stored as UTC)
gen_random_uuid() -- Use UUID() function
JSONB -- Use JSON (same performance in 8.0+)
-- MySQL equivalent:
id CHAR(36) PRIMARY KEY DEFAULT (UUID())
-- OR:
id BIGINT AUTO_INCREMENT PRIMARY KEY
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
attributes JSON
Load references/data-types-guide.md for comprehensive type selection guide.
Load references/schema-design-patterns.md when:
Load references/normalization-guide.md when:
Load references/relationship-patterns.md when:
Load references/data-types-guide.md when:
Load references/constraints-catalog.md when:
Load references/error-catalog.md when:
Before Creating Tables:
Every Table Must Have:
Foreign Keys:
Indexes:
Validation:
Before (Multiple issues):
CREATE TABLE users (
email VARCHAR(MAX), -- Issue: No primary key, VARCHAR(MAX)
password VARCHAR(MAX),
created VARCHAR(50) -- Issue: Date as string
);
CREATE TABLE orders (
id UUID PRIMARY KEY,
user_email VARCHAR(MAX), -- Issue: No foreign key
total VARCHAR(20), -- Issue: Money as string
status VARCHAR(MAX) -- Issue: No validation
);
After (Production-ready):
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);
CREATE TABLE orders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
status VARCHAR(20) NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'canceled')),
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
Result: ✅ All constraints enforced, proper types, indexed, auditable
All 12 documented errors prevented:
See: references/error-catalog.md for detailed fixes
Templates:
templates/basic-schema.sql - Users, products, orders startertemplates/relationships.sql - All relationship typestemplates/constraints.sql - Constraint examplestemplates/audit-columns.sql - Audit patterns + triggersReferences:
references/normalization-guide.md - 1NF through 5NF detailedreferences/relationship-patterns.md - Relationship typesreferences/data-types-guide.md - PostgreSQL vs MySQL typesreferences/constraints-catalog.md - All constraintsreferences/schema-design-patterns.md - Best practicesreferences/error-catalog.md - All 12 errors documentedOfficial Documentation:
Production-tested | 12 errors prevented | MIT License
Use when working with Payload CMS projects (payload.config.ts, collections, fields, hooks, access control, Payload API). Use when debugging validation errors, security issues, relationship queries, transactions, or hook behavior.