database-expert

🗄️ Database Expert — Production-Grade Database Engineering

Philosophy: Your database is the foundation of your application. A bad schema is technical debt that compounds with every row inserted. Design it right, index it early, migrate it safely.

1. When to Use This Skill

• Designing database schemas for new features
• Optimizing slow queries
• Planning index strategies
• Writing and reviewing migrations
• Choosing between SQL and NoSQL
• Debugging database performance issues
• Planning data model changes for existing systems

2. Schema Design Principles

Normalize First, Denormalize When Proven Necessary

-- GOOD: Normalized schema — single source of truth
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    role VARCHAR(20) NOT NULL DEFAULT 'member'
        CHECK (role IN ('member', 'admin', 'viewer')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE teams (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE team_members (
    team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    joined_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (team_id, user_id)
);

-- BAD: User data duplicated in every table that references them
CREATE TABLE orders (
    id UUID PRIMARY KEY,
    user_name VARCHAR(100),  -- duplicated from users table
    user_email VARCHAR(255), -- will drift from source
    -- ...
);

Use Proper Types

Data	❌ Bad Type	✅ Good Type	Why
Primary key	INT AUTO_INCREMENT	UUID	Distributed-safe, no enumeration attacks
Money	FLOAT	NUMERIC(12,2)	Float arithmetic is imprecise
Timestamps	TIMESTAMP	TIMESTAMPTZ	Always store with timezone
Status/enum	VARCHAR	VARCHAR + CHECK	Enforce valid values at DB level
JSON blobs	TEXT	JSONB (PostgreSQL)	Indexable, queryable, validated
Boolean	INT(1)	BOOLEAN	Semantic correctness
IP address	VARCHAR(45)	INET (PostgreSQL)	Built-in validation + operators