database-patterns

Database Patterns

Overview

Database design and access patterns for relational and NoSQL databases.

Schema Design

Normalization Levels

Level	Description	Use Case
1NF	Atomic values, no repeating groups	Base requirement
2NF	No partial dependencies	Most applications
3NF	No transitive dependencies	OLTP systems
Denormalized	Redundant data for reads	Read-heavy, analytics

Common Table Patterns

-- Users table
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Soft delete pattern
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL;
CREATE INDEX idx_users_deleted ON users(deleted_at) WHERE deleted_at IS NULL;

-- Audit columns
ALTER TABLE users ADD COLUMN created_by UUID REFERENCES users(id);
ALTER TABLE users ADD COLUMN updated_by UUID REFERENCES users(id);

Relationships

-- One-to-Many
CREATE TABLE orders (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(id),
    total DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_orders_user ON orders(user_id);

-- Many-to-Many
CREATE TABLE order_products (
    order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
    product_id UUID REFERENCES products(id) ON DELETE CASCADE,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- Self-referential (tree/hierarchy)
CREATE TABLE categories (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_id UUID REFERENCES categories(id)
);
CREATE INDEX idx_categories_parent ON categories(parent_id);

Indexing Strategies

Index Types

Type	Use Case	Example
B-tree	Range, equality	Most columns
Hash	Equality only	Exact matches
GIN	Arrays, JSON, full-text	JSONB, text search
GiST	Geometric, range types	PostGIS, IP ranges