Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
The command stays on one line. Scroll horizontally to inspect it before copying.
Prefer a local copy? Download the files currently available to SkillsMP.
File Explorer
15 files
Showing SKILL.md
SKILL.md
Source instructions · Read-only preview
name
database-designer
description
Use when the user asks to design database schemas, plan data migrations, optimize queries, choose between SQL and NoSQL, or model data relationships.
Database Designer - POWERFUL Tier Skill
Overview
A comprehensive database design skill that provides expert-level analysis, optimization, and migration capabilities for modern database systems. This skill combines theoretical principles with practical tools to help architects and developers create scalable, performant, and maintainable database schemas.
Core Competencies
Schema Design & Analysis
Normalization Analysis: Automated detection of normalization levels (1NF through BCNF)
Denormalization Strategy: Smart recommendations for performance optimization
Data Type Optimization: Identification of inappropriate types and size issues
Constraint Analysis: Missing foreign keys, unique constraints, and null checks
Naming Convention Validation: Consistent table and column naming patterns
ERD Generation: Automatic Mermaid diagram creation from DDL
Index Optimization
Index Gap Analysis: Identification of missing indexes on foreign keys and query patterns
Composite Index Strategy: Optimal column ordering for multi-column indexes
Index Redundancy Detection: Elimination of overlapping and unused indexes
Performance Impact Modeling: Selectivity estimation and query cost analysis
Index Type Selection: B-tree, hash, partial, covering, and specialized indexes
Accepts SQL DDL or JSON schema (assets/sample_schema.sql / sample_schema.json). Output includes normalization findings, missing constraints, naming issues, and a Mermaid ERD — show the ERD to the user and fix flagged issues before optimizing.
Write the user's hot queries into a query-patterns JSON first (copy assets/sample_query_patterns.json). Output is a priority-ordered list of CREATE INDEX recommendations plus redundant-index removals.
--zero-downtime emits an expand-contract plan; --validate-only checks feasibility without generating SQL.
4. Verification loop
Re-run step 1 on the target schema and assert the issues found in the first pass are gone; run migration_generator.py --validate-only before handing over the migration.
Database Design Principles
→ See references/database-design-reference.md for details
Best Practices
Schema Design
Use meaningful names: Clear, consistent naming conventions
Choose appropriate data types: Right-sized columns for storage efficiency
Principle of least privilege: Grant minimal necessary permissions
Encrypt sensitive data: At rest and in transit
Audit access patterns: Monitor and log database access
Validate inputs: Prevent SQL injection attacks
Regular security updates: Keep database software current
Query Generation Patterns
SELECT with JOINs
-- INNER JOIN: only matching rowsSELECT o.id, c.name, o.total
FROM orders o
INNERJOIN customers c ON c.id = o.customer_id;
-- LEFT JOIN: all left rows, NULLs for non-matchesSELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFTJOIN orders o ON o.customer_id = c.id
GROUPBY c.name;
-- Self-join: hierarchical data (employees/managers)SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFTJOIN employees m ON m.id = e.manager_id;
Common Table Expressions (CTEs)
-- Recursive CTE for org chartWITHRECURSIVE org AS (
SELECT id, name, manager_id, 1AS depth
FROM employees WHERE manager_id ISNULLUNIONALLSELECT e.id, e.name, e.manager_id, o.depth +1FROM employees e INNERJOIN org o ON o.id = e.manager_id
)
SELECT*FROM org ORDERBY depth, name;
Window Functions
-- ROW_NUMBER for pagination / dedupSELECT*, ROW_NUMBER() OVER (PARTITIONBY customer_id ORDERBY created_at DESC) AS rn
FROM orders;
-- RANK with gaps, DENSE_RANK without gapsSELECT name, score, RANK() OVER (ORDERBY score DESC) AS rank FROM leaderboard;
-- LAG/LEAD for comparing adjacent rowsSELECTdate, revenue,
revenue -LAG(revenue) OVER (ORDERBYdate) AS daily_change
FROM daily_sales;
Aggregation Patterns
-- FILTER clause (PostgreSQL) for conditional aggregationSELECTCOUNT(*) AS total,
COUNT(*) FILTER (WHERE status ='active') AS active,
AVG(amount) FILTER (WHERE amount >0) AS avg_positive
FROM accounts;
-- GROUPING SETS for multi-level rollupsSELECT region, product, SUM(revenue)
FROM sales
GROUPBYGROUPING SETS ((region, product), (region), ());
Migration Patterns
Up/Down Migration Scripts
Every migration must have a reversible counterpart. Name files with a timestamp prefix for ordering:
Use the expand-contract pattern to avoid locking or breaking running code:
Expand — add the new column/table (nullable, with default)
Migrate data — backfill in batches; dual-write from application
Transition — application reads from new column; stop writing to old
Contract — drop old column in a follow-up migration
Data Backfill Strategies
-- Batch update to avoid long-running locksUPDATE users SET email_normalized =LOWER(email)
WHERE id IN (SELECT id FROM users WHERE email_normalized ISNULL LIMIT 5000);
-- Repeat in a loop until 0 rows affected
Rollback Procedures
Always test the down.sql in staging before deploying up.sql to production
Keep rollback window short — if the contract step has run, rollback requires a new forward migration
For irreversible changes (dropping columns with data), take a logical backup first
Performance Optimization
Indexing Strategies
Index Type
Use Case
Example
B-tree (default)
Equality, range, ORDER BY
CREATE INDEX idx_users_email ON users(email);
GIN
Full-text search, JSONB, arrays
CREATE INDEX idx_docs_body ON docs USING gin(to_tsvector('english', body));
GiST
Geometry, range types, nearest-neighbor
CREATE INDEX idx_locations ON places USING gist(coords);
Partial
Subset of rows (reduce size)
CREATE INDEX idx_active ON users(email) WHERE active = true;
Covering
Index-only scans
CREATE INDEX idx_cov ON orders(customer_id) INCLUDE (total, created_at);
EXPLAIN Plan Reading
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;
Key signals to watch:
Seq Scan on large tables — missing index
Nested Loop with high row estimates — consider hash/merge join or add index
Buffers shared read much higher than hit — working set exceeds memory
N+1 Query Detection
Symptoms: application issues one query per row (e.g., fetching related records in a loop).
Fixes:
Use JOIN or subquery to fetch in one round-trip
ORM eager loading (select_related / includes / with)
DataLoader pattern for GraphQL resolvers
Connection Pooling
Tool
Protocol
Best For
PgBouncer
PostgreSQL
Transaction/statement pooling, low overhead
ProxySQL
MySQL
Query routing, read/write splitting
Built-in pool (HikariCP, SQLAlchemy pool)
Any
Application-level pooling
Rule of thumb: Set pool size to (2 * CPU cores) + disk spindles. For cloud SSDs, start with 2 * vCPUs and tune.
Read Replicas and Query Routing
Route all SELECT queries to replicas; writes to primary
Account for replication lag (typically <1s for async, 0 for sync)
Use pg_last_wal_replay_lsn() to detect lag before reading critical data
Multi-Database Decision Matrix
Criteria
PostgreSQL
MySQL
SQLite
SQL Server
Best for
Complex queries, JSONB, extensions
Web apps, read-heavy workloads
Embedded, dev/test, edge
Enterprise .NET stacks
JSON support
Excellent (JSONB + GIN)
Good (JSON type)
Minimal
Good (OPENJSON)
Replication
Streaming, logical
Group replication, InnoDB cluster
N/A
Always On AG
Licensing
Open source (PostgreSQL License)
Open source (GPL) / commercial
Public domain
Commercial
Max practical size
Multi-TB
Multi-TB
~1 TB (single-writer)
Multi-TB
When to choose:
PostgreSQL — default choice for new projects; best extensibility and standards compliance
MySQL — existing MySQL ecosystem; simple read-heavy web applications
SQLite — mobile apps, CLI tools, unit test databases, IoT/edge
SQL Server — mandated by enterprise policy; deep .NET/Azure integration
