database-schema-designer_skill

Overview

This skill designs robust, scalable database schemas for SQL and NoSQL systems with production-ready constraints, indexes, and migration guidance. It produces normalized SQL schemas or document models, indexing strategies, and reversible migration patterns tailored to your access patterns and scale. The output focuses on data integrity, query performance, and maintainability.

How this skill works

Describe entities, relationships, scale hints, and database preference (SQL or NoSQL). The skill analyzes access patterns, chooses normalization or embedding strategies, assigns keys and data types, and generates CREATE statements, index recommendations, and migration steps. It also produces constraint designs (UNIQUE, CHECK, FK behaviors) and a verification checklist for production readiness.

When to use it

• Starting a new application schema (SQL or NoSQL)
• Refactoring or normalizing an existing table or collection
• Improving query performance with index recommendations
• Designing migrations with up/down reversible scripts
• Choosing primary key strategies for distributed systems
• Planning denormalization for read-heavy analytics

Best practices

• Model the domain first; name entities after business concepts
• Normalize to 3NF for OLTP; denormalize selectively for OLAP or heavy reads
• Always define primary keys and foreign key constraints with ON DELETE/ON UPDATE strategies
• Index foreign keys and frequently filtered/sorted columns; avoid over-indexing
• Use appropriate data types (DECIMAL for money, TIMESTAMP in UTC, CHAR for fixed codes)
• Create reversible migrations and test against staging data

Example use cases

• Design an e-commerce schema with users, products, orders, order_items, and indexes for high-traffic reads
• Model a multi-tenant SaaS database with tenancy isolation and FK policies
• Create a migration plan to add a NOT NULL column without downtime (nullable → backfill → constrain)
• Design a MongoDB catalog using embedding for frequently-read product details and referencing for large arrays
• Optimize reporting by suggesting materialized views or denormalized summary tables