Skip to content
Skills Library
Programming · Building & coding

database-design-patterns

/database-design-patterns

Database schema design patterns and optimization strategies for relational and NoSQL databases. Use when designing database schemas, optimizing query performance, or implementing data persistence layers at scale.

Licence
MIT
Last repository change
28.06.2026

Assessment and security

90/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content25 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
7
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 28.06.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

Database schema design patterns and optimization strategies for relational and NoSQL databases. Use when designing database schemas, optimizing query performance, or implementing data persistence layers at scale.

Original author description

Database schema design patterns and optimization strategies for relational and NoSQL databases. Use when designing database schemas, optimizing query performance, or implementing data persistence layers at scale.

How it works

  1. When to Use This Skill
  2. Core Concepts
  3. Quick Reference
  4. Workflow
  5. Common Mistakes
  6. Best Practices
  7. Resources
Original SKILL.md headings · 118 lines, 637 words
SKILL.md preview
---
name: database-design-patterns
description: Database schema design patterns and optimization strategies for relational and NoSQL databases. Use when designing database schemas, optimizing query performance, or implementing data persistence layers at scale.
keywords:
  - NoSQL design
  - data modeling
  - database design
  - database patterns
  - denormalization
  - foreign key
  - indexing
  - normalization
  - relational database
  - schema design
file_patterns:
  - '**/*ddl*.*'
  - '**/database/**'
  - '**/migrations/**'
  - '**/schema/**'
confidence: 0.86
---

# Database Design Patterns

Expert guidance for designing scalable database schemas, optimizing query performance, and implementing robust data persistence layers across relational and NoSQL databases.

## When to Use This Skill

- Designing database schemas for new applications
- Optimizing slow queries and database performance
- Choosing between normalization and denormalization strategies
- Implementing partitioning, sharding, or replication strategies
- Migrating between database technologies (SQL to NoSQL or vice versa)
- Designing for high availability and disaster recovery
- Implementing caching strategies and read replicas
- Scaling databases horizontally or vertically
- Ensuring data consistency in distributed systems

## Core Concepts

### Data Modeling
Design schemas that reflect business domain, access patterns, and consistency requirements. Balance normalization (data integrity) with denormalization (read performance) based on workload characteristics.

### ACID vs. BASE
- **ACID** (Relational): Atomicity, Consistency, Isolation, Durability - strong guarantees
- **BASE** (NoSQL): Basically Available, Soft state, Eventually consistent - flexibility

### CAP Theorem
Distributed systems choose two of three: Consistency, Availability, Partition Tolerance.

### Polyglot Persistence
Use the right database for each use case: PostgreSQL for transactions, MongoDB for documents, Redis for caching, Elasticsearch for search, Cassandra for time-series, Neo4j for graphs.

## Quick Reference

| Task | Load reference |
| --- | --- |
| Core database principles (ACID, BASE, CAP) | `skills/database-design-patterns/references/core-principles.md` |
| Schema patterns (normalization, star schema, documents) | `skills/database-design-patterns/references/schema-design-patterns.md` |
| Index types and strategies (B-tree, hash, covering) | `skills/database-design-patterns/references/indexing-strategies.md` |
| Partitioning and sharding approaches | `skills/database-design-patterns/references/partitioning-patterns.md` |
| Replication modes (primary-replica, multi-leader) | `skills/database-design-patterns/references/replication-patterns.md` |
| Query optimization and caching | `skills/database-design-patterns/references/query-optimization.md` |

## Workflow

### Phase 1: Requirements Analysis
1. Identify access patterns (read-heavy vs. write-heavy)
2. Determine consistency requirements (strong vs. eventual)
3. Estimate data volume and growth rate
4. Define SLA requirements (latency, availability)

### Phase 2: Schema Design
1. Model entities and relationships
2. Choose normalization level based on workload
3. Design for query patterns, not just storage
4. Consider data distribution strategy (partitioning/sharding)

### Phase 3: Performance Optimization
1. Analyze query execution plans (`EXPLAIN ANALYZE`)
2. Add indexes for frequent queries
3. Implement caching where appropriate
4. Configure connection pooling
5. Monitor and iterate

### Phase 4: Scaling Strategy
1. Implement read replicas for read scaling
2. Consider partitioning for large tables (>100M rows)
3. Plan sharding strategy for horizontal scaling
4. Design for high availability with replication

## Common Mistakes

**Over-normalization**: Too many joins slow down reads. Denormalize for read-heavy workloads.

**Missing indexes**: Analyze query patterns and add indexes for frequent WHERE/JOIN columns.

**Wrong index type**: Use composite indexes with correct column order (equality first, then range).

**Ignoring replication lag**: Handle eventual consistency with read-your-writes pattern.

**Poor partitioning key**: Choose keys that distribute data evenly and align with query patterns.

**N+1 queries**: Use JOINs or batch loading instead of querying in loops.

**Inefficient pagination**: Use keyset pagination instead of OFFSET for large datasets.

**Connection exhaustion**: Implement connection pooling sized for your workload.

## Best Practices

1. **Model for access patterns** - Design schemas around how data will be queried
2. **Index strategically** - Index frequently queried columns, avoid over-indexing
3. **Partition large tables** - Use for tables >100M rows or time-series data
4. **Replicate for reads** - Primary-replica for read scaling, multi-leader for geo-distribution
5. **Optimize queries** - Analyze execution plans, avoid N+1, use proper pagination
6. **Cache hot data** - Application-level caching with appropriate TTLs
7. **Pool connections** - Size connection pools based on workload
8. **Monitor continuously** - Track query performance, index usage, replication lag
9. **Plan for growth** - Design for 3x current load
10. **Choose consistency wisely** - Match consistency level to business requirements

## Resources

**Books**:
- "Designing Data-Intensive Applications" (Kleppmann)
- "High Performance MySQL" (Schwartz)

**Sites**:
- use-the-index-luke.com
- PostgreSQL documentation
- MongoDB documentation

**Tools**:
- EXPLAIN ANALYZE
- pg_stat_statements
- Percona Toolkit
- pt-query-digest
Files in this package (7)
  • SKILL.md 5 KB
  • references/core-principles.md 2 KB
  • references/indexing-strategies.md 3 KB
  • references/partitioning-patterns.md 3 KB
  • references/query-optimization.md 4 KB
  • references/replication-patterns.md 3 KB
  • references/schema-design-patterns.md 5 KB

Versions

RepositoryScoreLicence
This page · NickCrew/Claude-Cortex90MIT
erichowens/some_claude_skills98MIT

Identical content appears in 1 other GitHub folders. We show one copy with the highest score.

Database snapshot: 13.11.2026 · package version f59b7aa5d134 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100database-architect
    Designs relational and NoSQL database schemas, indexing strategies, migration plans, and data modeling patterns. Use when designing a database or when the user mentions database architecture, schema design, or data modeling.
  • 100database-design
    Designs database schemas, indexing strategies, query optimization, and migration patterns for SQL and NoSQL databases. Use when designing tables, optimizing queries, fixing N+1 problems, planning migrations, or when asked about database performance, normalization, ORMs, or data modeling.
  • 100database-schema-designer
    This skill should be used when designing database schemas for SQL or NoSQL databases. It provides normalization guidelines, indexing strategies, migration patterns, and performance optimization.
  • 100database-design
    Database schema design, optimization, and migration patterns for PostgreSQL, MySQL, and NoSQL databases. Use for designing schemas, writing migrations, or optimizing queries.
  • 95database-expert
    Advanced database design and administration for PostgreSQL, MongoDB, and Redis. Use when designing schemas, optimizing queries, managing database performance, or implementing data patterns.
  • 90sql-optimization-patterns
    Provides SQL optimization patterns for query performance, indexing strategies, schema design, and database tuning. Use when optimizing slow queries, designing indexes, or tuning database performance.

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.