Schema Design Best Practices - Building Robust Databases

Introduction

You've learned CREATE TABLE, constraints, foreign keys, indexes, and normalization. Now let's put it all together! This lesson covers practical schema design principles for building production-ready databases.

Naming Conventions

Table Names

• ✅ Use plural nouns

CREATE TABLE customers (...);  -- Good
CREATE TABLE customer (...);   -- Avoid

• ✅ Use snake_case

CREATE TABLE order_items (...);  -- Good
CREATE TABLE OrderItems (...);   -- Avoid

Column Names

• ✅ Descriptive and clear

created_at TIMESTAMPTZ  -- Good
dt TIMESTAMPTZ           -- Avoid

• ✅ Avoid reserved words

user_order INTEGER  -- Good
order INTEGER        -- Bad (reserved word!)

Constraint Names

• ✅ Use prefixes: pk_, fk_, uq_, chk_, idx_

CONSTRAINT pk_customers PRIMARY KEY (id)
CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
CONSTRAINT uq_users_email UNIQUE (email)
CONSTRAINT chk_products_price CHECK (price >= 0)
CREATE INDEX idx_orders_customer ON orders(customer_id)

Essential Columns

Every Table Should Have

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,              -- 1. Primary key
    name VARCHAR(200) NOT NULL,            -- 2. Business data
    created_at TIMESTAMPTZ DEFAULT NOW(),  -- 3. Created timestamp
    updated_at TIMESTAMPTZ DEFAULT NOW()   -- 4. Updated timestamp
);

Why:

• id: Unique identifier
• created_at: Track when record was added
• updated_at: Track last modification

Optional Audit Columns

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by INTEGER REFERENCES users(id),  -- Who created?
    updated_by INTEGER REFERENCES users(id),  -- Who last updated?
    deleted_at TIMESTAMPTZ                     -- Soft delete
);

Soft Deletes Pattern

Instead of DELETE, mark as deleted:

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    deleted_at TIMESTAMPTZ
);

-- "Delete" a user
UPDATE users SET deleted_at = NOW() WHERE id = 1;

-- Query active users
SELECT * FROM users WHERE deleted_at IS NULL;

-- Query deleted users
SELECT * FROM users WHERE deleted_at IS NOT NULL;

Benefits:

• Recover accidentally deleted data
• Maintain audit trail
• Preserve foreign key relationships

E-Commerce Schema Example

Complete schema with best practices:

-- Users
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    last_login_at TIMESTAMPTZ,
    deleted_at TIMESTAMPTZ,
    CONSTRAINT chk_users_email CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$')
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_active ON users(active) WHERE deleted_at IS NULL;

-- Addresses
CREATE TABLE addresses (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    address_line1 VARCHAR(255) NOT NULL,
    address_line2 VARCHAR(255),
    city VARCHAR(100) NOT NULL,
    state VARCHAR(50),
    postal_code VARCHAR(20) NOT NULL,
    country CHAR(2) NOT NULL DEFAULT 'US',
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_addresses_user ON addresses(user_id);

-- Categories
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    parent_id INTEGER REFERENCES categories(id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_categories_slug ON categories(slug);
CREATE INDEX idx_categories_parent ON categories(parent_id);

-- Products
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(200) UNIQUE NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    cost DECIMAL(10,2) CHECK (cost >= 0),
    category_id INTEGER REFERENCES categories(id),
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

CREATE INDEX idx_products_slug ON products(slug);
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_active ON products(active) WHERE deleted_at IS NULL;

-- Orders
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
    subtotal DECIMAL(10,2) NOT NULL CHECK (subtotal >= 0),
    tax DECIMAL(10,2) NOT NULL DEFAULT 0 CHECK (tax >= 0),
    shipping DECIMAL(10,2) NOT NULL DEFAULT 0 CHECK (shipping >= 0),
    total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
    shipping_address_id BIGINT REFERENCES addresses(id),
    billing_address_id BIGINT REFERENCES addresses(id),
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    shipped_at TIMESTAMPTZ,
    delivered_at TIMESTAMPTZ
);

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);

-- Order Items
CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
    total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (order_id, product_id)
);

CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);

Schema Versioning

Track schema changes over time:

CREATE TABLE schema_migrations (
    version VARCHAR(20) PRIMARY KEY,
    description TEXT,
    applied_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO schema_migrations (version, description) VALUES
    ('001', 'Initial schema'),
    ('002', 'Add soft delete to users'),
    ('003', 'Add indexes on foreign keys');

Data Type Choices

IDs

SERIAL        -- Up to 2 billion rows
BIGSERIAL     -- Billions+ rows (use this by default!)
UUID          -- Globally unique, distributed systems

Money

DECIMAL(10,2)  -- Exact precision (always use for money!)
NUMERIC(10,2)  -- Same as DECIMAL

Timestamps

TIMESTAMPTZ  -- With timezone (always use this!)
TIMESTAMP    -- Without timezone (avoid)

Strings

VARCHAR(255)  -- Email, names
TEXT          -- Descriptions, content
CHAR(2)       -- Fixed codes (US, CA)

Booleans

BOOLEAN  -- TRUE/FALSE/NULL

Performance Optimization Patterns

Partial Indexes

-- Index only active records
CREATE INDEX idx_products_active ON products(name)
WHERE active = TRUE AND deleted_at IS NULL;

Composite Indexes

-- Optimize common query
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Include Columns (Covering Indexes)

CREATE INDEX idx_orders_user_total ON orders(user_id)
INCLUDE (total, created_at);

Common Anti-Patterns to Avoid

❌ Storing Arrays/JSON Instead of Proper Tables

-- ❌ Bad
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    tags TEXT[]  -- Hard to query, no referential integrity
);

-- ✅ Good
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE
);

CREATE TABLE user_tags (
    user_id INTEGER REFERENCES users(id),
    tag_id INTEGER REFERENCES tags(id),
    PRIMARY KEY (user_id, tag_id)
);

❌ EAV (Entity-Attribute-Value) Pattern

-- ❌ Bad: Generic "attributes" table
CREATE TABLE entity_attributes (
    entity_id INTEGER,
    attribute_name VARCHAR(50),
    attribute_value TEXT
);

-- ✅ Good: Proper columns
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    color VARCHAR(50),
    size VARCHAR(20)
);

❌ No Foreign Keys

-- ❌ Bad: No referential integrity
CREATE TABLE orders (
    customer_id INTEGER  -- No constraint!
);

-- ✅ Good: Foreign key enforced
CREATE TABLE orders (
    customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE
);

Checklist for Production-Ready Schema

• ✅ All tables have primary keys
• ✅ Foreign keys defined with appropriate ON DELETE actions
• ✅ Indexes on all foreign keys
• ✅ Indexes on frequently queried columns
• ✅ Appropriate data types (DECIMAL for money, TIMESTAMPTZ for time)
• ✅ NOT NULL on required fields
• ✅ Unique constraints on business keys (email, username, etc.)
• ✅ CHECK constraints for validation
• ✅ Default values where appropriate
• ✅ created_at and updated_at timestamps
• ✅ Named constraints for clarity
• ✅ Consistent naming conventions
• ✅ Schema versioning/migration system

Key Takeaways

• ✅ Consistent naming: snake_case, plural table names
• ✅ Essential columns: id, created_at, updated_at
• ✅ Soft deletes preserve data
• ✅ Index foreign keys always
• ✅ Use BIGSERIAL for future-proofing
• ✅ TIMESTAMPTZ for timestamps
• ✅ DECIMAL for money
• ✅ Normalize but denormalize when performance demands
• ✅ Version your schema with migrations

Next Steps

Congratulations! You've completed Module 7 and mastered database design fundamentals. In Module 8, we'll apply everything you've learned in a Capstone Project—building a complete database for a real-world application!

Ready to build something amazing!