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:

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