database-helper

Database Helper Skill

This skill helps with all aspects of database work including schema design, query writing, optimization, and migrations. Use this whenever you need to design data models, write complex queries, or improve database performance.

Database Design Principles

1. Normalization Levels

Normal Form	Rule	Purpose
1NF	Atomic values only	No repeating groups
2NF	1NF + No partial dependencies	Full functional dependency
3NF	2NF + No transitive dependencies	Only key dependencies
BCNF	Every determinant is a candidate key	Stricter 3NF

2. When to Denormalize

• Read-heavy workloads
• Complex aggregations needed frequently
• Performance > storage cost
• Reporting/analytics use cases

3. Data Types Selection

Data Type	PostgreSQL	MySQL	Use Case
Primary Key	BIGSERIAL / UUID	BIGINT AUTO_INCREMENT	Identity
String (short)	VARCHAR(255)	VARCHAR(255)	Names, emails
String (long)	TEXT	TEXT / LONGTEXT	Content
Integer	INTEGER / BIGINT	INT / BIGINT	Counts
Decimal	NUMERIC(10,2)	DECIMAL(10,2)	Money
Boolean	BOOLEAN	TINYINT(1)	Flags
Date/Time	TIMESTAMPTZ	DATETIME / TIMESTAMP	Events
JSON	JSONB	JSON	Flexible data
Enum	CREATE TYPE	ENUM(...)	Fixed options

Schema Design Patterns

Basic Entity Table

-- PostgreSQL
CREATE TABLE users (
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID DEFAULT gen_random_uuid() UNIQUE NOT NULL,
    email           VARCHAR(255) UNIQUE NOT NULL,
    password_hash   VARCHAR(255) NOT NULL,
    name            VARCHAR(100) NOT NULL,
    status          VARCHAR(20) DEFAULT 'pending' NOT NULL,
    
    -- Timestamps
    created_at      TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at      TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    deleted_at      TIMESTAMPTZ,  -- Soft delete
    
    -- Constraints
    CONSTRAINT users_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT users_status_valid CHECK (status IN ('pending', 'active', 'inactive', 'banned'))
);

-- Indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_created_at ON users(created_at DESC);

-- Updated timestamp trigger
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();