Database Design Patterns

A comprehensive guide to database design patterns, covering normalisation, indexing, consistency models, scaling strategies, and optimisation techniques.

---

Overview

Database design patterns are proven solutions for common data storage and retrieval challenges. Choosing the right patterns impacts performance, scalability, and maintainability of your applications.

⤢ zoom

---

Normalisation vs Denormalisation

Key Concepts

Concept	Description
Normalisation	Process of organising data to reduce redundancy and improve integrity
Denormalisation	Intentionally adding redundancy to improve read performance
Normal Forms	1NF, 2NF, 3NF, BCNF - progressive levels of normalisation
Functional Dependency	Relationship where one attribute determines another

Normal Forms

⤢ zoom

Common Patterns

Normalised Design (3NF)

-- Separate tables for each entity
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date DATE,
    total_amount DECIMAL(10,2)
);

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10,2)
);

Denormalised Design

-- Combined table for read performance
CREATE TABLE order_summary (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100),      -- Duplicated from customers
    customer_email VARCHAR(255),     -- Duplicated from customers
    order_date DATE,
    total_amount DECIMAL(10,2),
    item_count INT,                  -- Pre-calculated
    product_names TEXT[]             -- Embedded array
);

When to Use

Scenario	Recommendation
OLTP systems with many writes	Normalise
OLAP/reporting systems	Denormalise
Data warehouses	Star/Snowflake schema (denormalised)
High-consistency requirements	Normalise
Read-heavy workloads	Denormalise

---

Indexing Strategies

Key Concepts

Index Type	Description	Best For
B-Tree	Balanced tree structure	Range queries, sorting
Hash	Hash table lookup	Equality comparisons
GiST	Generalised Search Tree	Geometric/full-text data
GIN	Generalised Inverted Index	Arrays, JSONB, full-text
BRIN	Block Range Index	Large sequential datasets

Common Index Patterns

Single Column Index

-- Basic index for frequent lookups
CREATE INDEX idx_customers_email ON customers(email);

-- Unique index for constraints
CREATE UNIQUE INDEX idx_users_username ON users(username);

Composite Index

-- Multi-column index (order matters!)
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date DESC);

-- Covering index (includes all needed columns)
CREATE INDEX idx_orders_covering
ON orders(customer_id, order_date)
INCLUDE (total_amount, status);

Partial Index

-- Index only active records
CREATE INDEX idx_active_users
ON users(email)
WHERE status = 'active';

-- Index recent orders only
CREATE INDEX idx_recent_orders
ON orders(order_date)
WHERE order_date > '2024-01-01';

Expression Index

-- Index on computed value
CREATE INDEX idx_users_lower_email
ON users(LOWER(email));

-- Index on JSON field
CREATE INDEX idx_data_category
ON products((data->>'category'));

Index Maintenance

-- Analyse index usage (PostgreSQL)
SELECT
    schemaname,
    tablename,
    indexname,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0;  -- Unused indexes

-- Rebuild fragmented index
REINDEX INDEX idx_customers_email;

-- Update statistics
ANALYSE customers;

---

ACID vs BASE Principles

Key Concepts

⤢ zoom

ACID Transactions

-- ACID-compliant transaction
BEGIN TRANSACTION;

-- Debit from account A
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 'A';

-- Credit to account B
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 'B';

-- Insert audit record
INSERT INTO transfers (from_account, to_account, amount, timestamp)
VALUES ('A', 'B', 100, NOW());

COMMIT;

Isolation Levels

Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	Possible	Possible	Possible
Read Committed	No	Possible	Possible
Repeatable Read	No	No	Possible
Serialisable	No	No	No

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- PostgreSQL: Row-level locking
SELECT * FROM accounts
WHERE account_id = 'A'
FOR UPDATE;

BASE in NoSQL

// Eventually consistent read (DynamoDB)
const params = {
    TableName: 'Orders',
    Key: { orderId: '12345' },
    ConsistentRead: false  // Eventually consistent (faster)
};

// Strongly consistent read
const strongParams = {
    TableName: 'Orders',
    Key: { orderId: '12345' },
    ConsistentRead: true   // Strongly consistent (slower)
};

Comparison

Aspect	ACID	BASE
Consistency	Strong, immediate	Eventual
Availability	May sacrifice for consistency	Prioritised
Use Cases	Banking, inventory	Social media, analytics
Database Types	RDBMS	NoSQL, distributed
Scalability	Vertical preferred	Horizontal preferred

---

Sharding and Partitioning

Key Concepts

Concept	Description
Partitioning	Dividing table within single database instance
Sharding	Distributing data across multiple database instances
Shard Key	Column(s) used to determine data placement
Consistent Hashing	Algorithm for even data distribution

Sharding Architecture

⤢ zoom

Sharding Strategies

Range-Based Sharding

-- Shard by date range
-- Shard 1: 2023 data
-- Shard 2: 2024 data
-- Shard 3: 2025 data

-- Router logic (pseudo-code)
IF order_date.year = 2023 THEN
    route_to_shard_1()
ELSIF order_date.year = 2024 THEN
    route_to_shard_2()
ELSE
    route_to_shard_3()

Hash-Based Sharding

-- Consistent hash sharding
-- shard_id = hash(customer_id) % num_shards

-- Example: Vitess sharding configuration
CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=InnoDB
PARTITION BY HASH(customer_id)
PARTITIONS 4;

Directory-Based Sharding

-- Lookup table for shard mapping
CREATE TABLE shard_directory (
    customer_id INT PRIMARY KEY,
    shard_id INT NOT NULL
);

-- Query shard location
SELECT shard_id FROM shard_directory
WHERE customer_id = 12345;

Table Partitioning (PostgreSQL)

-- Range partitioning by date
CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (order_date);

-- Create partitions
CREATE TABLE orders_2024_q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- List partitioning
CREATE TABLE customers (
    customer_id INT,
    region VARCHAR(20),
    name VARCHAR(100)
) PARTITION BY LIST (region);

CREATE TABLE customers_emea PARTITION OF customers
    FOR VALUES IN ('UK', 'DE', 'FR');

CREATE TABLE customers_apac PARTITION OF customers
    FOR VALUES IN ('JP', 'AU', 'SG');

---

Caching Strategies

Key Concepts

Strategy	Description	Use Case
Cache-Aside	Application manages cache	General purpose
Read-Through	Cache loads data on miss	Read-heavy workloads
Write-Through	Writes go to cache and DB	Data consistency
Write-Behind	Async writes to DB	Write-heavy workloads
Refresh-Ahead	Proactive cache refresh	Predictable access patterns

Caching Patterns

⤢ zoom

Cache-Aside Implementation

import redis
import json

cache = redis.Redis(host='localhost', port=6379)

def get_user(user_id):
    # Check cache first
    cache_key = f"user:{user_id}"
    cached = cache.get(cache_key)

    if cached:
        return json.loads(cached)

    # Cache miss - query database
    user = db.query("SELECT * FROM users WHERE id = %s", user_id)

    if user:
        # Populate cache with TTL
        cache.setex(cache_key, 3600, json.dumps(user))

    return user

def update_user(user_id, data):
    # Update database
    db.execute("UPDATE users SET ... WHERE id = %s", user_id)

    # Invalidate cache
    cache.delete(f"user:{user_id}")

Write-Through Pattern

def save_user(user_id, data):
    cache_key = f"user:{user_id}"

    # Write to cache and database together
    cache.setex(cache_key, 3600, json.dumps(data))
    db.execute("INSERT INTO users ... ON CONFLICT UPDATE ...", data)

Cache Invalidation Strategies

# Time-based expiration
cache.setex("key", 3600, "value")  # Expires in 1 hour

# Event-based invalidation
def on_user_updated(user_id):
    cache.delete(f"user:{user_id}")
    cache.delete(f"user_profile:{user_id}")

# Tag-based invalidation
cache.sadd("tag:user:123", "user:123", "orders:user:123")
def invalidate_user_data(user_id):
    keys = cache.smembers(f"tag:user:{user_id}")
    cache.delete(*keys)

Distributed Caching

# Redis Cluster configuration
from rediscluster import RedisCluster

startup_nodes = [
    {"host": "redis1", "port": 6379},
    {"host": "redis2", "port": 6379},
    {"host": "redis3", "port": 6379}
]

cache = RedisCluster(
    startup_nodes=startup_nodes,
    decode_responses=True
)

---

Backup and Recovery Best Practices

Key Concepts

Concept	Description
RPO	Recovery Point Objective - maximum acceptable data loss
RTO	Recovery Time Objective - maximum acceptable downtime
Full Backup	Complete database copy
Incremental	Changes since last backup
Differential	Changes since last full backup
WAL/Binlog	Transaction logs for point-in-time recovery

Backup Strategies

⤢ zoom

PostgreSQL Backup Commands

# Logical backup (pg_dump)
pg_dump -h localhost -U postgres -F c -b -v -f backup.dump mydb

# Restore from dump
pg_restore -h localhost -U postgres -d mydb -v backup.dump

# Continuous archiving setup (postgresql.conf)
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'

# Base backup for PITR
pg_basebackup -h localhost -D /backup/base -Ft -z -P

# Point-in-time recovery (recovery.conf)
restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2024-03-15 14:30:00'

MySQL Backup Commands

# Logical backup
mysqldump -u root -p --all-databases --single-transaction \
    --routines --triggers > full_backup.sql

# Physical backup with Percona XtraBackup
xtrabackup --backup --target-dir=/backup/full

# Incremental backup
xtrabackup --backup --target-dir=/backup/inc1 \
    --incremental-basedir=/backup/full

# Prepare and restore
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --copy-back --target-dir=/backup/full

Best Practices

Practice	Description
3-2-1 Rule	3 copies, 2 media types, 1 offsite
Test Restores	Regularly verify backup integrity
Encrypt Backups	Protect sensitive data at rest
Monitor Jobs	Alert on backup failures
Document Procedures	Maintain runbooks for recovery
Retention Policy	Define backup lifecycle

# Encrypted backup
pg_dump mydb | gzip | openssl enc -aes-256-cbc -salt \
    -out backup.sql.gz.enc -pass file:/path/to/keyfile

# Verify backup integrity
pg_restore --list backup.dump > /dev/null && echo "Backup valid"

---

Common Query Optimisation Techniques

Key Concepts

Technique	Description
Query Plan Analysis	Understanding execution strategy
Index Optimisation	Ensuring proper index usage
Query Rewriting	Restructuring for better performance
Statistics Updates	Keeping optimiser informed

Query Analysis

-- PostgreSQL: Explain Analyse
EXPLAIN (ANALYSE, BUFFERS, FORMAT TEXT)
SELECT c.name, COUNT(o.order_id)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date > '2024-01-01'
GROUP BY c.name;

-- MySQL: Explain
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE customer_id = 100;

-- Identify slow queries (PostgreSQL)
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Optimisation Techniques

Avoid SELECT *

-- Bad: Fetches all columns
SELECT * FROM orders WHERE customer_id = 100;

-- Good: Fetch only needed columns
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 100;

Use Appropriate JOINs

-- Bad: Correlated subquery
SELECT name, (
    SELECT COUNT(*) FROM orders
    WHERE orders.customer_id = customers.customer_id
) as order_count
FROM customers;

-- Good: JOIN with aggregation
SELECT c.name, COUNT(o.order_id) as order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

Pagination Optimisation

-- Bad: OFFSET for deep pagination
SELECT * FROM products
ORDER BY created_at
LIMIT 20 OFFSET 10000;

-- Good: Keyset pagination
SELECT * FROM products
WHERE created_at < '2024-03-15 10:30:00'
ORDER BY created_at DESC
LIMIT 20;

Batch Operations

-- Bad: Individual inserts
INSERT INTO logs (message) VALUES ('msg1');
INSERT INTO logs (message) VALUES ('msg2');
INSERT INTO logs (message) VALUES ('msg3');

-- Good: Batch insert
INSERT INTO logs (message) VALUES
    ('msg1'), ('msg2'), ('msg3');

-- Good: COPY for bulk loading (PostgreSQL)
COPY logs (message) FROM '/path/to/data.csv' CSV;

Optimise IN Clauses

-- Bad: Long IN list
SELECT * FROM products
WHERE category_id IN (1, 2, 3, ..., 1000);

-- Good: Use EXISTS or JOIN
SELECT p.* FROM products p
WHERE EXISTS (
    SELECT 1 FROM temp_categories tc
    WHERE tc.id = p.category_id
);

Query Hints

-- PostgreSQL: Force index usage
SET enable_seqscan = off;
SELECT * FROM orders WHERE customer_id = 100;
SET enable_seqscan = on;

-- MySQL: Index hints
SELECT * FROM orders USE INDEX (idx_customer_date)
WHERE customer_id = 100 AND order_date > '2024-01-01';

-- PostgreSQL: Parallel query control
SET max_parallel_workers_per_gather = 4;

---

Quick Reference

Pattern	When to Use	Trade-offs
Normalisation	OLTP, data integrity critical	More JOINs, slower reads
Denormalisation	Read-heavy, reporting	Data redundancy, update complexity
B-Tree Index	Range queries, ordering	Write overhead, storage
Hash Index	Equality lookups only	No range support
Covering Index	Avoid table lookups	Larger index size
ACID	Financial, transactional	Limited scalability
BASE	Distributed, high availability	Eventual consistency
Range Sharding	Time-series, sequential	Hot spots possible
Hash Sharding	Even distribution	No range queries
Cache-Aside	General caching	Cache inconsistency risk
Write-Through	Consistency important	Higher write latency
Full Backup	Complete recovery	Time and storage
Incremental	Frequent backups	Complex recovery

---

Common Issues and Solutions

Issue: Slow Query Performance

Symptoms: Queries taking seconds to complete, high CPU usage

Solutions:

-- Check for missing indexes
EXPLAIN ANALYSE SELECT * FROM orders
WHERE customer_id = 100;
-- Look for "Seq Scan" on large tables

-- Add appropriate index
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Update statistics
ANALYSE orders;

Issue: Lock Contention

Symptoms: Transactions waiting, deadlocks

Solutions:

-- Identify blocking queries (PostgreSQL)
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
WHERE NOT blocked_locks.granted;

-- Use row-level locking
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

-- Reduce transaction scope
BEGIN;
-- Do minimal work
COMMIT;

Issue: Cache Stampede

Symptoms: Database overload when cache expires

Solutions:

# Probabilistic early expiration
import random

def get_with_early_expiry(key, ttl, beta=1):
    value, expiry = cache.get_with_expiry(key)

    # Probabilistically refresh before expiry
    if value and random.random() < beta * (time.time() - expiry + ttl) / ttl:
        # Refresh cache in background
        refresh_cache_async(key)

    return value

# Locking to prevent stampede
def get_with_lock(key):
    value = cache.get(key)
    if value:
        return value

    # Try to acquire lock
    if cache.set(f"lock:{key}", "1", nx=True, ex=10):
        value = fetch_from_db(key)
        cache.setex(key, 3600, value)
        cache.delete(f"lock:{key}")
        return value

    # Wait for other process
    time.sleep(0.1)
    return cache.get(key)

Issue: Uneven Shard Distribution

Symptoms: Some shards overloaded, others underutilised

Solutions:

# Use consistent hashing with virtual nodes
class ConsistentHash:
    def __init__(self, nodes, virtual_nodes=150):
        self.ring = {}
        for node in nodes:
            for i in range(virtual_nodes):
                key = hash(f"{node}:{i}")
                self.ring[key] = node

    def get_node(self, key):
        h = hash(key)
        # Find nearest node clockwise
        for k in sorted(self.ring.keys()):
            if k >= h:
                return self.ring[k]
        return self.ring[min(self.ring.keys())]

Issue: Backup Restoration Failures

Symptoms: Corrupted backups, incomplete restores

Solutions:

# Verify backup after creation
pg_restore --list backup.dump > /dev/null
echo "Exit code: $?"

# Test restore to separate instance
pg_restore -d test_db backup.dump

# Compare row counts
psql -c "SELECT 'orders', COUNT(*) FROM orders
         UNION ALL
         SELECT 'customers', COUNT(*) FROM customers" \
    -d production -d test_db

# Implement checksums
sha256sum backup.dump > backup.dump.sha256

Issue: N+1 Query Problem

Symptoms: Many small queries instead of one efficient query

Solutions:

# Bad: N+1 queries
customers = db.query("SELECT * FROM customers")
for customer in customers:
    orders = db.query(
        "SELECT * FROM orders WHERE customer_id = %s",
        customer.id
    )  # N additional queries

# Good: Eager loading with JOIN
results = db.query("""
    SELECT c.*, o.*
    FROM customers c
    LEFT JOIN orders o ON c.id = o.customer_id
""")

# Good: Batch loading
customer_ids = [c.id for c in customers]
orders = db.query(
    "SELECT * FROM orders WHERE customer_id = ANY(%s)",
    customer_ids
)

---

Related Topics

The following topics would complement this Database Design Patterns cheatsheet:

1. PostgreSQL Administration - Deep dive into PostgreSQL-specific features, configuration tuning, and operational best practices

2. Redis and In-Memory Databases - Comprehensive coverage of Redis data structures, clustering, and caching patterns

3. MongoDB and Document Databases - Schema design patterns, aggregation pipelines, and scaling strategies for document stores

4. Database Migration Strategies - Zero-downtime migrations, schema versioning, and data transformation techniques

5. Distributed Systems Patterns - CAP theorem, consensus algorithms, and patterns for building reliable distributed databases

6. SQL Performance Tuning - Advanced query optimisation, execution plan analysis, and database profiling techniques