database-design

name	database-design
description	Database schema design and optimization
license	MIT
compatibility	opencode
metadata	{"audience":"developers","category":"database"}

What I do

• Design normalized database schemas
• Choose appropriate data types
• Implement proper constraints
• Create effective indexes
• Handle many-to-many relationships
• Design for scalability
• Implement soft deletes and auditing
• Handle temporal data

When to use me

When designing database schemas or optimizing queries.

Schema Design Principles

-- Normal Form Examples

-- 1NF: Atomic values, no repeating groups
-- BAD: tags VARCHAR stored as "tag1,tag2,tag3"
-- GOOD: Separate tags table

-- 2NF: No partial dependencies (no composite key dependencies)
-- BAD: Orders table with customer_name (depends on customer_id, not order_id)
-- GOOD: Separate customers table

-- 3NF: No transitive dependencies
-- BAD: users table with department_name (depends on department_id)
-- GOOD: Separate departments table

Schema Design

-- Users table
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    uuid UUID NOT NULL DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL,
    username VARCHAR(50) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(200),
    avatar_url VARCHAR(500),
    bio TEXT,
    role VARCHAR(50) NOT NULL DEFAULT 'user',
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    is_verified BOOLEAN NOT NULL DEFAULT FALSE,
    email_verified_at TIMESTAMPTZ,
    last_login_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT users_email_uq UNIQUE (email),
    CONSTRAINT users_username_uq UNIQUE (username),
    CONSTRAINT users_email_check CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT users_role_check CHECK (role IN ('admin', 'moderator', , ))
);

 INDEX idx_users_email  users(email);
 INDEX idx_users_uuid  users(uuid);
 INDEX idx_users_role  users(role)  is_active  ;


 posts (
    id BIGSERIAL ,
    author_id    users(id)   RESTRICT,
    title () ,
    slug () ,
    content TEXT ,
    excerpt TEXT,
    status ()   ,
    published_at TIMESTAMPTZ,
    deleted_at TIMESTAMPTZ,  
    created_at TIMESTAMPTZ   NOW(),
    updated_at TIMESTAMPTZ   NOW(),

     posts_status_check  (status  (, , )),
     posts_slug_uq  (author_id, slug),
     posts_deleted_at_nz  (deleted_at    deleted_at  created_at)
);

 INDEX idx_posts_author  posts(author_id)  deleted_at  ;
 INDEX idx_posts_status  posts(status)  deleted_at  ;
 INDEX idx_posts_published  posts(published_at)  deleted_at    status  ;


 tags (
    id BIGSERIAL ,
    name () ,
    slug () ,
    description TEXT,
    created_at TIMESTAMPTZ   NOW(),

     tags_name_uq  (name),
     tags_slug_uq  (slug)
);

 post_tags (
    post_id    posts(id)   CASCADE,
    tag_id    tags(id)   CASCADE,
    created_at TIMESTAMPTZ   NOW(),

     (post_id, tag_id)
);


 comments (
    id BIGSERIAL ,
    author_id    users(id)   CASCADE,
    content TEXT ,
    parent_id   comments(id)   CASCADE,
    
    
    commentable_type () ,
    commentable_id  ,
    
    created_at TIMESTAMPTZ   NOW(),
    updated_at TIMESTAMPTZ   NOW(),

     comments_commentable_check  (
        (commentable_type     (   posts  id  commentable_id)) 
        (commentable_type     (   users  id  commentable_id))
    )
);

 INDEX idx_comments_polymorphic  comments(commentable_type, commentable_id);

Audit Trail

-- Audit logging table
CREATE TABLE audit_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
    action VARCHAR(50) NOT NULL,
    entity_type VARCHAR(100) NOT NULL,
    entity_id BIGINT,
    old_values JSONB,
    new_values JSONB,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT audit_logs_action_check CHECK (
        action IN ('create', 'update', 'delete', 'view', 'login', 'logout')
    )
);

CREATE INDEX idx_audit_entity ON audit_logs(entity_type, entity_id);
CREATE INDEX idx_audit_user ON audit_logs(user_id);
CREATE INDEX idx_audit_created ON audit_logs(created_at DESC);

-- Function to automatically log changes
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_logs (
        user_id,
        action,
        entity_type,
        entity_id,
        old_values,
        new_values
    )
    VALUES (
        current_setting('app.current_user_id', )::,
        TG_OP,
        TG_TABLE_NAME,
        (OLD.id, NEW.id),
          TG_OP    to_jsonb()   ,
          TG_OP  (, )  to_jsonb()   
    );
     ;
;
$$  plpgsql;


  audit_posts
AFTER       posts
     audit_trigger_func();

Data Types

-- UUID for public identifiers
-- Use for APIs, external references
-- Internally use BIGSERIAL for performance

-- Timestamps
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
-- Always use TIMESTAMPTZ (with timezone), never DATE or TIME alone

-- JSONB for flexible data
metadata JSONB DEFAULT '{}'::jsonb
-- For semi-structured data, configuration, settings

-- Arrays for simple lists
tags TEXT[] DEFAULT '{}'
-- For simple, non-relational lists

-- ENUM or check constraints for status
status VARCHAR(20) NOT NULL DEFAULT 'draft'
CHECK (status IN ('draft', 'published', 'archived'))

-- Use appropriate numeric types
balance DECIMAL(20, 8)  -- For precise monetary values
price DECIMAL(10, 2)    -- For prices
quantity INTEGER         -- For counts
ratio DOUBLE PRECISION   -- For ratios, no precision needed

-- Use INET for IP addresses
ip_address INET NOT NULL
-- Supports proper comparison and CIDR matching

Performance Patterns

-- Partitioning for large tables
CREATE TABLE events (
    id BIGSERIAL,
    event_type VARCHAR(50) NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Monthly partitions
CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Materialized views for complex queries
CREATE MATERIALIZED VIEW user_stats AS
SELECT
    author_id,
    COUNT(*) as post_count,
    MIN(created_at) as first_post,
    MAX(created_at) as last_post
FROM posts
WHERE deleted_at IS NULL
GROUP BY author_id;

-- Refresh on change
CREATE UNIQUE INDEX idx_user_stats  user_stats(author_id);

Migrations

# Alembic migration example
from alembic import op
import sqlalchemy as sa


def upgrade():
    op.create_table(
        'posts',
        sa.Column('id', sa.BigInteger(), autoincrement=True, nullable=False),
        sa.Column('title', sa.String(length=500), nullable=False),
        sa.Column('content', sa.Text(), nullable=False),
        sa.Column('author_id', sa.BigInteger(), nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('NOW()'), nullable=False),
        sa.ForeignKeyConstraint(['author_id'], ['users.id'], ondelete='RESTRICT'),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('author_id', 'slug')
    )

    op.create_index('idx_posts_author', 'posts', ['author_id'])
    op.create_index('idx_posts_status', 'posts', ['status'])


def downgrade():
    op.drop_index('idx_posts_status')
    op.drop_index('idx_posts_author')
    op.drop_table('posts')