Analyze Requirements: Understand data patterns, volume, access needs
Select Database: Choose SQL vs NoSQL based on requirements
Design Schema: Create data models, relationships, constraints
Optimize Queries: Implement indexes, analyze execution plans
Set Up Operations: Configure backup, replication, monitoring

Database Selection Guide

Type	Best For	ACID	Scale	Examples
Relational	Complex queries, transactions	Full	Vertical	PostgreSQL, MySQL
Document	Flexible schema, JSON	Partial	Horizontal	MongoDB
Key-Value	Caching, sessions	No	Horizontal	Redis
Wide-Column	Time series, analytics	Partial	Horizontal	Cassandra
Graph	Relationships	Varies	Varies	Neo4j
Search	Full-text search	No	Horizontal	Elasticsearch

Decision Tree

Need ACID transactions?
    │
    ├─→ Yes → Complex queries?
    │           ├─→ Yes → PostgreSQL
    │           └─→ No  → MySQL
    │
    └─→ No  → Data type?
              ├─→ Documents/JSON → MongoDB
              ├─→ Key-Value pairs → Redis
              ├─→ Time series → Cassandra/TimescaleDB
              └─→ Full-text search → Elasticsearch

Examples

Example 1: Schema Design

-- E-commerce schema
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()
);

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    stock INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(50) DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for common queries
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

Example 2: Query Optimization

-- Before: Full table scan
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 'abc123';

-- After: Add index
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Optimized query with selected columns
SELECT id, total, status, created_at
FROM orders
WHERE user_id = 'abc123'
ORDER BY created_at DESC
LIMIT 10;

Example 3: Redis Caching

import redis
import json

r = redis.Redis(host='localhost', port=6379, decode_responses=True)

def get_user(user_id: str) -> dict:
    # Try cache first
    cached = r.get(f"user:{user_id}")
    if cached:
        return json.loads(cached)

    # Cache miss - fetch from DB
    user = db.query(User).get(user_id)
    if user:
        r.setex(f"user:{user_id}", 3600, json.dumps(user.dict()))
    return user.dict()

Troubleshooting

Common Issues

Issue	Cause	Solution
Query timeout	Missing index	Run EXPLAIN ANALYZE, add index
Connection refused	Wrong config	Check host, port, credentials
Deadlock	Concurrent updates	Use proper isolation, retry logic
OOM on query	Large result set	Add LIMIT, use cursors

Debug Commands

-- PostgreSQL: Check slow queries
SELECT query, calls, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC LIMIT 10;

-- Check active connections
SELECT * FROM pg_stat_activity WHERE state = 'active';

-- Check table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Test Template

# tests/test_database.py
import pytest
from sqlalchemy import create_engine

class TestDatabaseSchema:
    @pytest.fixture
    def engine(self):
        return create_engine("postgresql://test:test@localhost/testdb")

    def test_users_table_exists(self, engine):
        result = engine.execute("SELECT 1 FROM users LIMIT 1")
        assert result is not None

    def test_foreign_key_constraint(self, engine):
        with pytest.raises(IntegrityError):
            engine.execute(
                "INSERT INTO orders (user_id, total) VALUES ('invalid-uuid', 100)"
            )

References

See references/ directory for:

DATABASE_GUIDE.md - Detailed database patterns
Schema templates and examples

Database Selection Guide

Decision Tree

Examples

Example 1: Schema Design

Example 2: Query Optimization

Example 3: Redis Caching

Troubleshooting

Common Issues

Debug Commands

Test Template

References

Resources

databases

Database Management Skill

Quick Start

Instructions