Now liveThe Skillselion MCP - thousands of ranked skills, loaded into your agent mid-task. No install.Get it →
alirezarezvani avatar

Database Designer

alirezarezvani/claude-skills

  • 708 installs
  • 23.5k repo stars
  • Updated July 17, 2026
  • alirezarezvani/claude-skills

database-designer is an agent skill that designs normalized database schemas and optimized queries for application features for developers who need structured relational data models before implementation.

About

database-designer is a Claude agent skill from alirezarezvani/claude-skills for designing normalized relational schemas and tuned queries aligned to real application access patterns. Developers describe features and expected query workloads—lookups, joins, pagination, aggregations—and receive table definitions, relationships, indexes, and SQL tuned for selectivity and execution cost. The skill analyzes query frequency, where conditions, join paths, and average execution characteristics to prioritize indexing and normalization choices. Reach for database-designer when starting a new feature's persistence layer, refactoring an overloaded schema, or optimizing slow queries before writing migrations or ORM models.

  • Query pattern analysis with selectivity and frequency
  • Index recommendations
  • Pagination and filtering patterns

Database Designer by the numbers

  • 708 all-time installs (skills.sh)
  • Ranked #102 of 911 Databases skills by installs in the Skillselion catalog
  • Security screen: MEDIUM risk (skills.sh audit)
  • Data as of Jul 31, 2026 (Skillselion catalog sync)
npx skills add https://github.com/alirezarezvani/claude-skills --skill database-designer

Add your badge

Show developers this skill is listed on Skillselion. Paste this into your README.

Listed on Skillselion
Installs708
repo stars★ 23.5k
Security audit2 / 3 scanners passed
Last updatedJuly 17, 2026
Repositoryalirezarezvani/claude-skills ↗

How do you design normalized schemas and optimized SQL queries?

Design normalized database schemas and optimized queries for application features.

Who is it for?

Backend developers defining relational schemas and query plans for new features or performance-critical database refactors.

Skip if: Teams needing NoSQL document modeling, data warehouse ETL design, or production DBA capacity planning without application context.

When should I use this skill?

The user needs normalized database schema design, index strategy, or query optimization for an application feature.

What you get

Normalized table definitions, relationship diagrams, index recommendations, and optimized SQL query statements.

  • Normalized schema design
  • Index recommendations
  • Optimized SQL queries

Files

SKILL.mdMarkdownGitHub ↗

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

Migration Management

  • Zero-Downtime Migrations: Expand-contract pattern implementation
  • Schema Evolution: Safe column additions, deletions, and type changes
  • Data Migration Scripts: Automated data transformation and validation
  • Rollback Strategy: Complete reversal capabilities with validation
  • Execution Planning: Ordered migration steps with dependency resolution

Tool Workflow (run these — do not analyze schemas by hand)

All paths relative to this skill folder; sample inputs in assets/.

1. Analyze the schema

python3 schema_analyzer.py --input schema.sql --generate-erd --output-format json -o analysis.json

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.

2. Optimize indexes against real query patterns

python3 index_optimizer.py --schema assets/sample_schema.json --queries assets/sample_query_patterns.json --analyze-existing --format json -o indexes.json

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.

3. Generate the migration

python3 migration_generator.py --current current_schema.json --target target_schema.json --zero-downtime --format sql -o migration.sql

--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

1. Use meaningful names: Clear, consistent naming conventions 2. Choose appropriate data types: Right-sized columns for storage efficiency 3. Define proper constraints: Foreign keys, check constraints, unique indexes 4. Consider future growth: Plan for scale from the beginning 5. Document relationships: Clear foreign key relationships and business rules

Performance Optimization

1. Index strategically: Cover common query patterns without over-indexing 2. Monitor query performance: Regular analysis of slow queries 3. Partition large tables: Improve query performance and maintenance 4. Use appropriate isolation levels: Balance consistency with performance 5. Implement connection pooling: Efficient resource utilization

Security Considerations

1. Principle of least privilege: Grant minimal necessary permissions 2. Encrypt sensitive data: At rest and in transit 3. Audit access patterns: Monitor and log database access 4. Validate inputs: Prevent SQL injection attacks 5. Regular security updates: Keep database software current

Query Generation Patterns

SELECT with JOINs

-- INNER JOIN: only matching rows
SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;

-- LEFT JOIN: all left rows, NULLs for non-matches
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

-- Self-join: hierarchical data (employees/managers)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

Common Table Expressions (CTEs)

-- Recursive CTE for org chart
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e INNER JOIN org o ON o.id = e.manager_id
)
SELECT * FROM org ORDER BY depth, name;

Window Functions

-- ROW_NUMBER for pagination / dedup
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders;

-- RANK with gaps, DENSE_RANK without gaps
SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM leaderboard;

-- LAG/LEAD for comparing adjacent rows
SELECT date, revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change
FROM daily_sales;

Aggregation Patterns

-- FILTER clause (PostgreSQL) for conditional aggregation
SELECT
  COUNT(*) 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 rollups
SELECT region, product, SUM(revenue)
FROM sales
GROUP BY GROUPING 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:

migrations/
├── 20260101_000001_create_users.up.sql
├── 20260101_000001_create_users.down.sql
├── 20260115_000002_add_users_email_index.up.sql
└── 20260115_000002_add_users_email_index.down.sql

Zero-Downtime Migrations (Expand/Contract)

Use the expand-contract pattern to avoid locking or breaking running code:

1. Expand — add the new column/table (nullable, with default) 2. Migrate data — backfill in batches; dual-write from application 3. Transition — application reads from new column; stop writing to old 4. Contract — drop old column in a follow-up migration

Data Backfill Strategies

-- Batch update to avoid long-running locks
UPDATE users SET email_normalized = LOWER(email)
WHERE id IN (SELECT id FROM users WHERE email_normalized IS NULL 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 TypeUse CaseExample
B-tree (default)Equality, range, ORDER BYCREATE INDEX idx_users_email ON users(email);
GINFull-text search, JSONB, arraysCREATE INDEX idx_docs_body ON docs USING gin(to_tsvector('english', body));
GiSTGeometry, range types, nearest-neighborCREATE INDEX idx_locations ON places USING gist(coords);
PartialSubset of rows (reduce size)CREATE INDEX idx_active ON users(email) WHERE active = true;
CoveringIndex-only scansCREATE 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

ToolProtocolBest For
PgBouncerPostgreSQLTransaction/statement pooling, low overhead
ProxySQLMySQLQuery routing, read/write splitting
Built-in pool (HikariCP, SQLAlchemy pool)AnyApplication-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

CriteriaPostgreSQLMySQLSQLiteSQL Server
Best forComplex queries, JSONB, extensionsWeb apps, read-heavy workloadsEmbedded, dev/test, edgeEnterprise .NET stacks
JSON supportExcellent (JSONB + GIN)Good (JSON type)MinimalGood (OPENJSON)
ReplicationStreaming, logicalGroup replication, InnoDB clusterN/AAlways On AG
LicensingOpen source (PostgreSQL License)Open source (GPL) / commercialPublic domainCommercial
Max practical sizeMulti-TBMulti-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

NoSQL Considerations

DatabaseModelUse When
MongoDBDocumentSchema flexibility, rapid prototyping, content management
RedisKey-value / cacheSession store, rate limiting, leaderboards, pub/sub
DynamoDBWide-columnServerless AWS apps, single-digit-ms latency at any scale
Use SQL as default. Reach for NoSQL only when the access pattern clearly benefits from it.

---

Sharding & Replication

Horizontal vs Vertical Partitioning

  • Vertical partitioning: Split columns across tables (e.g., separate BLOB columns). Reduces I/O for narrow queries.
  • Horizontal partitioning (sharding): Split rows across databases/servers. Required when a single node cannot hold the dataset or handle the throughput.

Sharding Strategies

StrategyHow It WorksProsCons
Hashshard = hash(key) % NEven distributionResharding is expensive
RangeShard by date or ID rangeSimple, good for time-seriesHot spots on latest shard
GeographicShard by user regionData locality, complianceCross-region queries are hard

Replication Patterns

PatternConsistencyLatencyUse Case
SynchronousStrongHigher write latencyFinancial transactions
AsynchronousEventualLow write latencyRead-heavy web apps
Semi-synchronousAt-least-one replica confirmedModerateBalance of safety and speed

---

Cross-References

  • sql-database-assistant — query writing, optimization, and debugging for day-to-day SQL work
  • database-schema-designer — ERD modeling, normalization analysis, and schema generation
  • migration-architect — large-scale migration planning across database engines or major schema overhauls
  • senior-backend — application-layer patterns (connection pooling, ORM best practices)
  • senior-devops — infrastructure provisioning for database clusters and replicas

Related skills

open.feishu.cnLark BaseLet their coding agents read from, write to, and query structured data stored in Lark Base (Feishu) spreadsheets and databases.493kmicrosoftAzure KustoRun fast KQL queries against Azure Data Explorer for log analytics, telemetry, and time-series data.484k1.3klarksuiteLark SheetsCreate, read, update, and export spreadsheets inside Lark (Feishu) directly from their AI coding agent.389k15.9ksupabaseSupabase Postgres Best PracticesAutomatically apply Supabase Postgres performance rules during query writing, schema design, and database optimization.313k2.4kfirebaseFirebase Data ConnectGenerate complete, production-ready Firebase Data Connect GraphQL schemas and queries for complex relational data models.112k392firebaseFirebase FirestoreAdd Cloud Firestore read/write capabilities to an Android Kotlin app using the official Firebase SDK.77k392

How it compares

Use database-designer for application-centric relational schema and query design; use DBA-focused tools for production cluster tuning and replication.

FAQ

What does database-designer produce?

database-designer produces normalized table definitions, relationship mappings, index recommendations, and optimized SQL queries aligned to documented access patterns such as authentication lookups, category search, and paginated result sets.

When should developers use database-designer?

Developers use database-designer when starting a feature persistence layer or refactoring slow queries. The skill analyzes join paths, selectivity, and query frequency to recommend schemas and indexes before writing migrations.

Is Database Designer safe to install?

skills.sh reports 2 of 3 security scanners passed. Review the Security Audits panel on this page before installing in production.

Databasesbackend
Browse by category
AI & AgentsFrontend & DesignBackend & DataDev ToolsAutomationTesting & ReviewSecurityMonitoring & CloudDocs & Planning
Design & frontend
Design systemsAnimation & motionAccessibility (a11y)CSS & TailwindUI designUXDesign to codeshadcn/uiLanding pagesBest design skills
Most searched skills
find-skillscavemanfrontend-designbrainstormingsystematic-debuggingstop-slopemil-design-engui-ux-pro-maxkarpathy-guidelinesweb-design-guidelinesreact-best-practicesgrill-meimprovemicrosoft-foundryagent-browser
Browse all listings
All agent skillsAll MCP serversAll marketplacesAll plugins

This week in AI coding

Five minutes, every Monday - the tools, releases and tactics for developers.

unsubscribe anytime.