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