database-schema-designer_skill

Overview

This skill helps design robust, scalable database schemas for both relational (SQL) and document (NoSQL) databases. It provides practical guidance on normalization, denormalization trade-offs, indexing, constraints, migration patterns, and performance tuning. Use it to create maintainable data models that scale with your application.

How this skill works

The skill inspects access patterns, data relationships, and operational requirements to recommend schema layouts: normalized relational designs for transactional workloads, denormalized documents for read-heavy use cases, and hybrid approaches when needed. It suggests appropriate data types, index types and ordering, constraint usage, partitioning and caching strategies, and stepwise, reversible migration patterns for zero-downtime deployments.

When to use it

• Designing a new database schema for SQL or NoSQL
• Refactoring or migrating an existing schema safely
• Choosing between normalization and denormalization based on access patterns
• Defining indexing and partitioning strategies for scale
• Creating reversible, backward-compatible migrations

Best practices

• Model the domain, not the UI—let business entities drive structure
• Optimize for primary access pattern: OLTP → normalize, OLAP/read-heavy → denormalize
• Favor data integrity with constraints and validations; optimize performance later
• Index selectively: index foreign keys, frequent WHERE/ORDER BY columns, and use composite indexes with column order in mind
• Separate schema and data migrations; test migrations on production-like data and aim for reversible steps

Example use cases

• Design an e-commerce schema: normalized orders, order_items, customers with denormalized aggregates for fast reads
• Model hierarchical data in MongoDB by embedding when child data is accessed with parent
• Create many-to-many join table for students and courses with a composite primary key
• Migrate adding a required column using three-step approach: nullable add, backfill, set NOT NULL
• Add partial or text indexes for selective queries and full-text search