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:

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

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:

-- ❌ 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:

-- ❌ 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:

-- ❌ 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:

-- ❌ 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:

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

-- ❌ 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:

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

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

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

When to Load References

Schema Design Process

Load references/schema-design-patterns.md when:

Starting a new database design
Need pattern examples (audit columns, soft deletes, versioning)
Implementing multi-tenancy
Choosing between UUID vs BIGSERIAL
Following naming conventions

Normalization

Load references/normalization-guide.md when:

Schema has data duplication
Unsure what normal form you're in
Need to normalize existing schema
Planning database structure

Relationships

Load references/relationship-patterns.md when:

Defining table relationships
Implementing junction tables
Creating hierarchical structures
Setting up cascade rules

Data Types

Load references/data-types-guide.md when:

Choosing column types
Migrating between PostgreSQL/MySQL
Optimizing storage
Implementing JSON fields

Constraints

Load references/constraints-catalog.md when:

Adding validation rules
Implementing CHECK constraints
Setting up foreign key cascades
Creating unique constraints

Error Prevention

Load references/error-catalog.md when:

Schema review needed
Troubleshooting schema issues
All 12 documented errors with fixes

Complete Setup Checklist

Before Creating Tables:

Normalized to at least 3NF
All relationships identified
Data types chosen appropriately
Cascade rules defined

Every Table Must Have:

Primary key defined
Audit columns (created_at, updated_at)
NOT NULL on required fields
Appropriate VARCHAR lengths (not MAX)
CHECK constraints for enums/ranges

Foreign Keys:

All foreign keys defined with REFERENCES
ON DELETE/UPDATE actions specified
All foreign keys indexed

Indexes:

Foreign keys indexed
Frequently queried columns indexed
Composite indexes for multi-column queries

Validation:

No circular dependencies
No EAV patterns
No polymorphic associations
Proper data types (no dates as strings)

Production Example

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

Known Issues Prevention

All 12 documented errors prevented:

✅ Missing primary key → UUID/BIGSERIAL required
✅ No foreign key constraints → REFERENCES required
✅ VARCHAR(MAX) everywhere → Appropriate lengths
✅ Denormalization without justification → 3NF minimum
✅ Missing NOT NULL constraints → Required fields marked
✅ No indexes on foreign keys → All FKs indexed
✅ Wrong data types → Proper type selection
✅ Missing CHECK constraints → Validation rules
✅ No audit columns → created_at/updated_at required
✅ Circular dependencies → Dependency analysis
✅ Missing ON DELETE/UPDATE cascades → Cascade rules
✅ EAV anti-pattern → Structured schema + JSONB

See: references/error-catalog.md for detailed fixes

Resources

Templates:

templates/basic-schema.sql - Users, products, orders starter
templates/relationships.sql - All relationship types
templates/constraints.sql - Constraint examples
templates/audit-columns.sql - Audit patterns + triggers

References:

references/normalization-guide.md - 1NF through 5NF detailed
references/relationship-patterns.md - Relationship types
references/data-types-guide.md - PostgreSQL vs MySQL types
references/constraints-catalog.md - All constraints
references/schema-design-patterns.md - Best practices
references/error-catalog.md - All 12 errors documented

Official Documentation:

PostgreSQL Data Types: https://www.postgresql.org/docs/current/datatype.html
PostgreSQL Constraints: https://www.postgresql.org/docs/current/ddl-constraints.html
MySQL Data Types: https://dev.mysql.com/doc/refman/8.0/en/data-types.html

Production-tested | 12 errors prevented | MIT License

database-schema-design

database-schema-design

Quick Start (10 Minutes)

Critical Rules

✓ Always Do

✗ Never Do

Top 7 Critical Errors

Error 1: Missing Primary Key

Error 2: No Foreign Key Constraints

Error 3: VARCHAR(MAX) Everywhere

Error 4: Wrong Data Types (Dates as Strings)

Error 5: No Indexes on Foreign Keys

Error 6: Missing Audit Columns

Error 7: EAV Anti-Pattern

Common Schema Patterns

Normalization Quick Reference

Configuration Summary

PostgreSQL Recommended Types

MySQL Differences

When to Load References

Schema Design Process

Normalization

Relationships

Data Types

Constraints

Error Prevention

Complete Setup Checklist

Production Example

Known Issues Prevention

Resources

Similar Skills