database-design_skill

Overview

This skill provides a practical guide to database schema design, indexing, and SQL performance optimization for production systems. It distills naming conventions, field type selection, primary/foreign key strategies, normalization vs. denormalization, and partitioning patterns into actionable rules. The focus is on maintainability, query performance, and operational stability for Java-backend services.

How this skill works

It inspects schema design choices and recommends concrete adjustments: consistent naming, appropriate column types, and primary key strategy for single-node or distributed systems. It analyzes index use and common index pitfalls, suggests query and pagination optimizations, and outlines when to apply foreign keys or skip them for performance. It also covers standard audit fields, optimistic locking, soft delete, and basic sharding approaches.

When to use it

• Designing new database schemas for microservices or monoliths
• Refactoring existing tables to improve query performance
• Preparing schemas for horizontal scaling or sharding
• Optimizing slow queries, pagination, or COUNT operations
• Defining standards for team-wide database conventions

Best practices

• Use lowercase underscore naming for tables and columns; use plural table names
• Pick appropriate types (BIGINT for IDs, DECIMAL for money, DATETIME/TIMESTAMP for timestamps)
• Prefer surrogate primary keys (auto-increment, Snowflake, or UUID) over business fields
• Create indexes for frequent WHERE, JOIN and ORDER BY columns; avoid indexing high-cardinality or frequently-updated fields
• Avoid SELECT *; use narrow projections and coverage indexes for hot queries
• Standardize charset (utf8mb4), timezone (UTC), and required audit fields (id, created_at, updated_at)

Example use cases

• Create an orders table with id BIGINT, created_at, updated_at, and a redundant user_name for read-heavy reports
• Replace OFFSET pagination with ID-range queries for deep paging in large result sets
• Add composite index following left-most prefix rule for queries filtering by name and age
• Use FOREIGN KEY constraints in low-concurrency admin databases; omit them in high-throughput distributed stores
• Implement optimistic locking with a version column for concurrent updates