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

✗ Never Do

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

Normalization Quick Reference

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