Database Design Examples: Real-World Schema Patterns

Good database design is the foundation of every successful application. Bad schemas lead to slow queries, data inconsistencies, and painful migrations down the road.

This guide provides practical database design examples for real-world applications. Not abstract theory - actual schemas you can adapt for your projects.

Database Design Fundamentals

Before diving into database design examples, let's establish core principles.

Normalization Basics

First Normal Form (1NF): Each column contains atomic values. No repeating groups.

-- Bad: Repeating group
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    item1 VARCHAR(100),
    item2 VARCHAR(100),
    item3 VARCHAR(100)
);

-- Good: Atomic values
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT
);

CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT REFERENCES orders(id),
    product_id INT,
    quantity INT
);

Second Normal Form (2NF): All non-key columns depend on the entire primary key.

Third Normal Form (3NF): No transitive dependencies - non-key columns don't depend on other non-key columns.

When to Denormalize

Sometimes denormalization improves performance:

Read-heavy workloads: Store computed values
Reporting tables: Pre-aggregate data
Caching hot data: Duplicate frequently accessed fields

Balance normalization with practical query needs.

Key Types

Primary Key: Unique identifier for each row. Use:

Auto-increment integers (simple, sequential)
UUIDs (distributed systems, security)

Foreign Key: References primary key in another table. Enforces referential integrity.

Composite Key: Multiple columns as primary key. Use sparingly.

Database Design Example 1: E-Commerce Platform

Let's build a complete database design example for an e-commerce store.

Core Tables

-- Users/Customers
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Addresses (one user can have multiple)
CREATE TABLE addresses (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    type VARCHAR(20) CHECK (type IN ('shipping', 'billing')),
    street_address VARCHAR(255) NOT NULL,
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(100) NOT NULL,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Product Categories (hierarchical)
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    parent_id INT REFERENCES categories(id),
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    image_url VARCHAR(500),
    sort_order INT DEFAULT 0
);

-- Products
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    compare_at_price DECIMAL(10, 2),
    cost_price DECIMAL(10, 2),
    quantity INT DEFAULT 0,
    weight DECIMAL(10, 2),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Product-Category relationship (many-to-many)
CREATE TABLE product_categories (
    product_id INT REFERENCES products(id) ON DELETE CASCADE,
    category_id INT REFERENCES categories(id) ON DELETE CASCADE,
    PRIMARY KEY (product_id, category_id)
);

-- Product Images
CREATE TABLE product_images (
    id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(id) ON DELETE CASCADE,
    url VARCHAR(500) NOT NULL,
    alt_text VARCHAR(255),
    sort_order INT DEFAULT 0
);

-- Product Variants (size, color, etc.)
CREATE TABLE product_variants (
    id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(id) ON DELETE CASCADE,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(100),
    price_modifier DECIMAL(10, 2) DEFAULT 0,
    quantity INT DEFAULT 0,
    attributes JSONB -- {"size": "L", "color": "Blue"}
);

Order Management

-- Orders
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    status VARCHAR(50) DEFAULT 'pending',
    subtotal DECIMAL(10, 2) NOT NULL,
    tax DECIMAL(10, 2) DEFAULT 0,
    shipping DECIMAL(10, 2) DEFAULT 0,
    discount DECIMAL(10, 2) DEFAULT 0,
    total DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    shipping_address_id INT REFERENCES addresses(id),
    billing_address_id INT REFERENCES addresses(id),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Order Items
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(id) ON DELETE CASCADE,
    product_id INT REFERENCES products(id),
    variant_id INT REFERENCES product_variants(id),
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    product_snapshot JSONB -- Store product data at time of order
);

-- Order Status History
CREATE TABLE order_status_history (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(id) ON DELETE CASCADE,
    status VARCHAR(50) NOT NULL,
    notes TEXT,
    created_by INT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Shopping Cart
CREATE TABLE cart_items (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    product_id INT REFERENCES products(id) ON DELETE CASCADE,
    variant_id INT REFERENCES product_variants(id),
    quantity INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, product_id, variant_id)
);

Reviews and Ratings

CREATE TABLE reviews (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE SET NULL,
    product_id INT REFERENCES products(id) ON DELETE CASCADE,
    rating INT CHECK (rating >= 1 AND rating <= 5),
    title VARCHAR(255),
    content TEXT,
    is_verified_purchase BOOLEAN DEFAULT FALSE,
    is_approved BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, product_id)
);

Essential Indexes for E-Commerce

-- Performance indexes
CREATE INDEX idx_products_slug ON products(slug);
CREATE INDEX idx_products_active ON products(is_active) WHERE is_active = TRUE;
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created ON orders(created_at);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_reviews_product ON reviews(product_id);

Database Design Example 2: SaaS Multi-Tenant Application

Multi-tenant database design requires careful planning. Here's a schema-per-tenant approach simplified into a shared schema with tenant isolation.

Tenant and User Management

-- Organizations/Tenants
CREATE TABLE organizations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    plan VARCHAR(50) DEFAULT 'free',
    settings JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Users
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    avatar_url VARCHAR(500),
    is_active BOOLEAN DEFAULT TRUE,
    email_verified_at TIMESTAMP,
    last_login_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Organization Memberships (users can belong to multiple orgs)
CREATE TABLE organization_members (
    id SERIAL PRIMARY KEY,
    organization_id INT REFERENCES organizations(id) ON DELETE CASCADE,
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    role VARCHAR(50) DEFAULT 'member',
    permissions JSONB DEFAULT '[]',
    invited_by INT REFERENCES users(id),
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(organization_id, user_id)
);

-- Invitations
CREATE TABLE invitations (
    id SERIAL PRIMARY KEY,
    organization_id INT REFERENCES organizations(id) ON DELETE CASCADE,
    email VARCHAR(255) NOT NULL,
    role VARCHAR(50) DEFAULT 'member',
    token VARCHAR(255) UNIQUE NOT NULL,
    invited_by INT REFERENCES users(id),
    expires_at TIMESTAMP NOT NULL,
    accepted_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SaaS-Specific Tables

-- Projects (main resource, tenant-scoped)
CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    organization_id INT REFERENCES organizations(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    status VARCHAR(50) DEFAULT 'active',
    settings JSONB DEFAULT '{}',
    created_by INT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- API Keys
CREATE TABLE api_keys (
    id SERIAL PRIMARY KEY,
    organization_id INT REFERENCES organizations(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    key_hash VARCHAR(255) UNIQUE NOT NULL,
    key_prefix VARCHAR(10) NOT NULL, -- First 8 chars for identification
    permissions JSONB DEFAULT '[]',
    last_used_at TIMESTAMP,
    expires_at TIMESTAMP,
    created_by INT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Subscriptions
CREATE TABLE subscriptions (
    id SERIAL PRIMARY KEY,
    organization_id INT REFERENCES organizations(id) ON DELETE CASCADE,
    plan VARCHAR(50) NOT NULL,
    status VARCHAR(50) DEFAULT 'active',
    stripe_subscription_id VARCHAR(255),
    current_period_start TIMESTAMP,
    current_period_end TIMESTAMP,
    cancel_at TIMESTAMP,
    canceled_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Usage Tracking
CREATE TABLE usage_records (
    id SERIAL PRIMARY KEY,
    organization_id INT REFERENCES organizations(id) ON DELETE CASCADE,
    metric VARCHAR(50) NOT NULL, -- 'api_calls', 'storage', etc.
    quantity INT NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(organization_id, metric, period_start)
);

Row-Level Security (PostgreSQL)

-- Enable RLS
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only see their organization's projects
CREATE POLICY org_isolation ON projects
    FOR ALL
    USING (organization_id = current_setting('app.current_org_id')::INT);

Social applications need to handle relationships, feeds, and high-volume interactions efficiently.

User Profiles and Relationships

-- User Profiles
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(100),
    bio TEXT,
    avatar_url VARCHAR(500),
    cover_url VARCHAR(500),
    website VARCHAR(255),
    location VARCHAR(100),
    is_verified BOOLEAN DEFAULT FALSE,
    is_private BOOLEAN DEFAULT FALSE,
    follower_count INT DEFAULT 0,
    following_count INT DEFAULT 0,
    post_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Follow Relationships
CREATE TABLE follows (
    id SERIAL PRIMARY KEY,
    follower_id INT REFERENCES users(id) ON DELETE CASCADE,
    following_id INT REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(follower_id, following_id),
    CHECK(follower_id != following_id)
);

-- Block Relationships
CREATE TABLE blocks (
    id SERIAL PRIMARY KEY,
    blocker_id INT REFERENCES users(id) ON DELETE CASCADE,
    blocked_id INT REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(blocker_id, blocked_id)
);

Content and Interactions

-- Posts
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    content TEXT,
    media_urls JSONB DEFAULT '[]',
    is_reply BOOLEAN DEFAULT FALSE,
    parent_id INT REFERENCES posts(id) ON DELETE CASCADE,
    repost_of_id INT REFERENCES posts(id) ON DELETE SET NULL,
    like_count INT DEFAULT 0,
    reply_count INT DEFAULT 0,
    repost_count INT DEFAULT 0,
    is_pinned BOOLEAN DEFAULT FALSE,
    visibility VARCHAR(20) DEFAULT 'public',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Likes
CREATE TABLE likes (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    post_id INT REFERENCES posts(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, post_id)
);

-- Bookmarks
CREATE TABLE bookmarks (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    post_id INT REFERENCES posts(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, post_id)
);

-- Hashtags
CREATE TABLE hashtags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    post_count INT DEFAULT 0
);

CREATE TABLE post_hashtags (
    post_id INT REFERENCES posts(id) ON DELETE CASCADE,
    hashtag_id INT REFERENCES hashtags(id) ON DELETE CASCADE,
    PRIMARY KEY(post_id, hashtag_id)
);

-- Mentions
CREATE TABLE mentions (
    id SERIAL PRIMARY KEY,
    post_id INT REFERENCES posts(id) ON DELETE CASCADE,
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Notifications

CREATE TABLE notifications (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    type VARCHAR(50) NOT NULL, -- 'like', 'follow', 'mention', 'reply'
    actor_id INT REFERENCES users(id) ON DELETE CASCADE,
    post_id INT REFERENCES posts(id) ON DELETE CASCADE,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_notifications_user_unread
ON notifications(user_id, is_read)
WHERE is_read = FALSE;

Feed Optimization

For high-volume feeds, consider fan-out on write:

-- Pre-computed feed (fan-out on write)
CREATE TABLE feed_items (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    post_id INT REFERENCES posts(id) ON DELETE CASCADE,
    author_id INT REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP NOT NULL,
    UNIQUE(user_id, post_id)
);

CREATE INDEX idx_feed_user_created ON feed_items(user_id, created_at DESC);

Database Design Example 4: Booking/Reservation System

Booking systems require careful handling of availability and conflicts.

Resources and Availability

-- Resources (rooms, equipment, vehicles, etc.)
CREATE TABLE resources (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL,
    description TEXT,
    capacity INT,
    price_per_hour DECIMAL(10, 2),
    price_per_day DECIMAL(10, 2),
    location VARCHAR(255),
    settings JSONB DEFAULT '{}',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Availability Rules
CREATE TABLE availability_rules (
    id SERIAL PRIMARY KEY,
    resource_id INT REFERENCES resources(id) ON DELETE CASCADE,
    day_of_week INT CHECK (day_of_week >= 0 AND day_of_week <= 6),
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    is_available BOOLEAN DEFAULT TRUE
);

-- Blocked Dates (holidays, maintenance, etc.)
CREATE TABLE blocked_dates (
    id SERIAL PRIMARY KEY,
    resource_id INT REFERENCES resources(id) ON DELETE CASCADE,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    reason VARCHAR(255)
);

Bookings

-- Customers
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    phone VARCHAR(20),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Bookings
CREATE TABLE bookings (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    resource_id INT REFERENCES resources(id),
    status VARCHAR(50) DEFAULT 'pending',
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    total_price DECIMAL(10, 2),
    notes TEXT,
    confirmation_code VARCHAR(20) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Prevent overlapping bookings
    EXCLUDE USING gist (
        resource_id WITH =,
        tstzrange(start_time, end_time) WITH &&
    ) WHERE (status NOT IN ('cancelled', 'rejected'))
);

-- Booking History
CREATE TABLE booking_history (
    id SERIAL PRIMARY KEY,
    booking_id INT REFERENCES bookings(id) ON DELETE CASCADE,
    status VARCHAR(50) NOT NULL,
    changed_by INT,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Payments

CREATE TABLE payments (
    id SERIAL PRIMARY KEY,
    booking_id INT REFERENCES bookings(id),
    amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    status VARCHAR(50) DEFAULT 'pending',
    payment_method VARCHAR(50),
    transaction_id VARCHAR(255),
    paid_at TIMESTAMP,
    refunded_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Database Design Example 5: Content Management System

A flexible CMS database design needs to handle various content types.

Content Structure

-- Content Types
CREATE TABLE content_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    fields JSONB NOT NULL, -- Schema definition
    settings JSONB DEFAULT '{}'
);

-- Content Entries
CREATE TABLE content_entries (
    id SERIAL PRIMARY KEY,
    content_type_id INT REFERENCES content_types(id),
    title VARCHAR(500),
    slug VARCHAR(500),
    data JSONB NOT NULL,
    status VARCHAR(50) DEFAULT 'draft',
    published_at TIMESTAMP,
    author_id INT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(content_type_id, slug)
);

-- Content Versions
CREATE TABLE content_versions (
    id SERIAL PRIMARY KEY,
    entry_id INT REFERENCES content_entries(id) ON DELETE CASCADE,
    version_number INT NOT NULL,
    data JSONB NOT NULL,
    created_by INT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Media Library
CREATE TABLE media (
    id SERIAL PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255),
    mime_type VARCHAR(100),
    size_bytes BIGINT,
    url VARCHAR(500) NOT NULL,
    alt_text VARCHAR(255),
    metadata JSONB DEFAULT '{}',
    folder_id INT,
    uploaded_by INT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Taxonomies (categories, tags, etc.)
CREATE TABLE taxonomies (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE taxonomy_terms (
    id SERIAL PRIMARY KEY,
    taxonomy_id INT REFERENCES taxonomies(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    parent_id INT REFERENCES taxonomy_terms(id),
    UNIQUE(taxonomy_id, slug)
);

CREATE TABLE content_taxonomy_terms (
    entry_id INT REFERENCES content_entries(id) ON DELETE CASCADE,
    term_id INT REFERENCES taxonomy_terms(id) ON DELETE CASCADE,
    PRIMARY KEY(entry_id, term_id)
);

Database Design Best Practices

1. Use Appropriate Data Types

-- Good: Precise types
price DECIMAL(10, 2)      -- Money
email VARCHAR(255)        -- Known max length
status VARCHAR(50)        -- Short enum-like values
created_at TIMESTAMP      -- Date/time
is_active BOOLEAN         -- True/false
settings JSONB            -- Flexible structured data
uuid UUID                 -- Globally unique IDs

-- Avoid: Overused TEXT for everything

2. Index Strategically

-- Index foreign keys
CREATE INDEX idx_orders_user ON orders(user_id);

-- Index commonly queried columns
CREATE INDEX idx_products_slug ON products(slug);

-- Partial indexes for filtered queries
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

-- Composite indexes for multi-column queries
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at DESC);

3. Use Constraints

-- NOT NULL for required fields
email VARCHAR(255) NOT NULL

-- UNIQUE for business keys
username VARCHAR(50) UNIQUE

-- CHECK for validation
rating INT CHECK (rating >= 1 AND rating <= 5)

-- Foreign keys for referential integrity
user_id INT REFERENCES users(id) ON DELETE CASCADE

4. Handle Soft Deletes

-- Soft delete pattern
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    -- ... other columns
    deleted_at TIMESTAMP
);

-- Query active records
SELECT * FROM posts WHERE deleted_at IS NULL;

-- Partial index for efficiency
CREATE INDEX idx_posts_active ON posts(id) WHERE deleted_at IS NULL;

5. Audit Trails

-- Generic audit log
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    record_id INT NOT NULL,
    action VARCHAR(20) NOT NULL, -- INSERT, UPDATE, DELETE
    old_data JSONB,
    new_data JSONB,
    user_id INT,
    ip_address INET,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Summary

These database design examples demonstrate patterns for common applications:

E-commerce: Products, orders, inventory, reviews
SaaS multi-tenant: Organizations, memberships, subscriptions
Social media: Users, relationships, posts, interactions
Booking systems: Resources, availability, reservations
CMS: Flexible content types, versions, media

Key principles across all database design examples:

Normalize appropriately, denormalize when needed
Use proper data types and constraints
Index strategically for query patterns
Plan for growth and maintainability

Start with these patterns and adapt them to your specific requirements.

Need help designing your database schema or optimizing existing databases? Let's talk about your data architecture needs.

Database Design Examples: Real-World Schema Patterns

Database Design Fundamentals

Normalization Basics

When to Denormalize

Key Types

Database Design Example 1: E-Commerce Platform

Core Tables

Order Management

Reviews and Ratings

Essential Indexes for E-Commerce

Database Design Example 2: SaaS Multi-Tenant Application

Tenant and User Management

SaaS-Specific Tables

Row-Level Security (PostgreSQL)

User Profiles and Relationships

Content and Interactions

Notifications

Feed Optimization

Database Design Example 4: Booking/Reservation System

Resources and Availability

Bookings

Payments

Database Design Example 5: Content Management System

Content Structure

Database Design Best Practices

1. Use Appropriate Data Types

2. Index Strategically

3. Use Constraints

4. Handle Soft Deletes

5. Audit Trails

Summary

Let's discuss your project

MySQL Optimization: Complete Performance Tuning Guide

Microservices vs Monolithic: How to Choose the Right Architecture

What is MVP in Business? A Practical Guide for Startups

Database Design Examples: Real-World Schema Patterns

Database Design Fundamentals

Normalization Basics

When to Denormalize

Key Types

Database Design Example 1: E-Commerce Platform

Core Tables

Order Management

Reviews and Ratings

Essential Indexes for E-Commerce

Database Design Example 2: SaaS Multi-Tenant Application

Tenant and User Management

SaaS-Specific Tables

Row-Level Security (PostgreSQL)

Database Design Example 3: Social Media Application

User Profiles and Relationships

Content and Interactions

Notifications

Feed Optimization

Database Design Example 4: Booking/Reservation System

Resources and Availability

Bookings

Payments

Database Design Example 5: Content Management System

Content Structure

Database Design Best Practices

1. Use Appropriate Data Types

2. Index Strategically

3. Use Constraints

4. Handle Soft Deletes

5. Audit Trails

Summary

Let's discuss your project

MySQL Optimization: Complete Performance Tuning Guide

Microservices vs Monolithic: How to Choose the Right Architecture

What is MVP in Business? A Practical Guide for Startups