database-design_skill

Overview

This skill provides practical guidance for designing efficient, maintainable database schemas and improving performance. It covers the full design flow from requirements and ER modeling to physical optimization like indexing and partitioning. The guidance emphasizes clear naming, sensible relation design, and iterative optimization driven by query patterns.

How this skill works

I inspect the data model needs, identify entities and relationships, and recommend a conceptual-to-physical design path: ER diagrams, normalized table schemas, and index/partition strategies. I analyze common query patterns and suggest indexing, denormalization, and performance techniques (EXPLAIN, pagination, connection pooling, read/write separation). I also flag anti-patterns and propose concrete fixes.

When to use it

• When defining a new application schema or onboarding a legacy data model.
• When query performance is poor or JOINs are slow.
• When planning scaling: partitioning, sharding, or read replicas.
• When deciding where to normalize vs. denormalize for performance.
• When designing many-to-many or hierarchical relationships.

Best practices

• Use clear, consistent naming: lowercase, snake_case, plural table names, singular foreign key names (user_id).
• Model entities with ER diagrams first, then map to normalized tables and add targeted denormalization only when needed.
• Index WHERE and JOIN columns; prefer B-tree for ranges/equality and composite indexes for common multi-column queries.
• Avoid indexing high-update or very low-selectivity columns; use partial indexes for filtered sets.
• Use EXPLAIN to validate plans, avoid SELECT *, and prefer cursor/seek-based pagination over OFFSET.

Example use cases

• Designing a user-orders system with one-to-many (users -> orders) and indexing on user_id for efficient joins.
• Implementing role-based access with a join table for many-to-many user_roles and cascading deletes.
• Optimizing a slow report query by adding a composite index matching the WHERE and ORDER BY clauses.
• Refactoring a ‘wide’ universal table into focused tables to improve maintainability and query speed.
• Planning read/write separation and connection pooling for a high-traffic production database.