Skip to content
Skills Library
Programming · Building & coding

database-design

/database-design

Database schema design, optimization, and migration patterns for PostgreSQL, MySQL, and NoSQL databases. Use for designing schemas, writing migrations, or optimizing queries.

Licence
MIT
Last repository change
21.09.2026

Assessment and security

100/ 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.
Content35 / 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
1
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 21.09.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, optimization, and migration patterns for PostgreSQL, MySQL, and NoSQL databases. Use for designing schemas, writing migrations, or optimizing queries.

Original author description

Database schema design, optimization, and migration patterns for PostgreSQL, MySQL, and NoSQL databases. Use for designing schemas, writing migrations, or optimizing queries.

How it works

  1. Schema Design Principles
  2. Index Design
  3. Migration Patterns
  4. Query Optimization
  5. Constraints & Data Integrity
  6. Best Practices
Original SKILL.md headings · 172 lines, 620 words
SKILL.md preview
---
name: database-design
description: Database schema design, optimization, and migration patterns for PostgreSQL, MySQL, and NoSQL databases. Use for designing schemas, writing migrations, or optimizing queries.
source: wshobson/agents
license: MIT
version: 4.1.0
---

# Database Design

## Schema Design Principles

### Normalization Guidelines
```sql
-- 1NF: Atomic values, no repeating groups
-- 2NF: No partial dependencies on composite keys
-- 3NF: No transitive dependencies

-- Users table (normalized)
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Addresses table (separate entity)
CREATE TABLE addresses (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  street VARCHAR(255),
  city VARCHAR(100),
  country VARCHAR(100),
  is_primary BOOLEAN DEFAULT false
);
```

### Denormalization for Performance
```sql
-- When read performance matters more than write consistency
CREATE TABLE order_summaries (
  id SERIAL PRIMARY KEY,
  order_id INTEGER REFERENCES orders(id),
  customer_name VARCHAR(255),  -- Denormalized from customers
  total_amount DECIMAL(10,2),
  item_count INTEGER,
  last_updated TIMESTAMPTZ DEFAULT NOW()
);
```

## Index Design

### Common Index Patterns
```sql
-- B-tree (default) for equality and range queries
CREATE INDEX idx_users_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Partial index for specific conditions
CREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL;

-- GIN index for array/JSONB columns
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);

-- Covering index (includes additional columns)
CREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE (total, status);
```

### Index Analysis
```sql
-- Check index usage
SELECT
  schemaname, tablename, indexname,
  idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Find missing indexes
SELECT
  relname, seq_scan, seq_tup_read,
  idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
ORDER BY seq_tup_read DESC;
```

## Migration Patterns

### Safe Migration Template
```sql
-- Always use transactions
BEGIN;

-- Add column with default (non-blocking in PG 11+)
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- Create index concurrently (doesn't lock table)
CREATE INDEX CONCURRENTLY idx_users_status ON users(status);

-- Backfill data in batches
UPDATE users SET status = 'active' WHERE status IS NULL AND id BETWEEN 1 AND 10000;

COMMIT;
```

### Zero-Downtime Migrations
```
1. Add new column (nullable)
2. Deploy code that writes to both columns
3. Backfill old data
4. Deploy code that reads from new column
5. Remove old column
```

## Query Optimization

### EXPLAIN Analysis
```sql
-- Always use EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

-- Key metrics to watch:
-- - Seq Scan vs Index Scan
-- - Actual rows vs Estimated rows
-- - Buffers: shared hit vs read
```

### Common Optimizations
```sql
-- Use EXISTS instead of IN for large sets
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- Pagination with keyset (cursor) instead of OFFSET
SELECT * FROM posts
WHERE created_at < '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;

-- Use CTEs for complex queries
WITH active_users AS (
  SELECT id FROM users WHERE last_login > NOW() - INTERVAL '30 days'
)
SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users);
```

## Constraints & Data Integrity

```sql
-- Primary key
ALTER TABLE users ADD PRIMARY KEY (id);

-- Foreign key with cascade
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- Check constraint
ALTER TABLE products ADD CONSTRAINT chk_price_positive
  CHECK (price >= 0);

-- Unique constraint
ALTER TABLE users ADD CONSTRAINT uniq_users_email UNIQUE (email);

-- Exclusion constraint (no overlapping ranges)
ALTER TABLE reservations ADD CONSTRAINT excl_no_overlap
  EXCLUDE USING gist (room_id WITH =, tsrange(start_time, end_time) WITH &&);
```

## Best Practices

- Use UUIDs for public-facing IDs, SERIAL/BIGSERIAL for internal
- Always add `created_at` and `updated_at` timestamps
- Use soft deletes (`deleted_at`) for important data
- Design for eventual consistency in distributed systems
- Document schema decisions in migration files
- Test migrations on production-size data before deploying

Versions

RepositoryScoreLicence
This page · MoizIbnYousaf/ai-agent-skills100MIT
Reprograme/redevkit100MIT
svngoku/coding-agents-skills100MIT
projanvil/mindforge100MIT
krzysztofsurdy/code-virtuoso100MIT
projanvil/mindforge100MIT
RainingDaemons/agents100MIT
cloudai-x/claude-workflow-v2100MIT
HK-hub/AgentSkills100MIT

Database snapshot: 13.11.2026 · package version 5a690a248e87 (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

  • 90database-engineer
    Database architecture design, schema optimization, query performance tuning, and data migration. Handles PostgreSQL, MySQL, MongoDB, Redis. Use when user asks to design database, optimize queries, create migrations, or improve database performance. Do NOT use for frontend or application logic.
  • 100database-schema-designer
    Use this skill when designing or reviewing database schemas for SQL and NoSQL databases. Covers normalization, indexing strategy, migration management with rollback safety, query optimization, and multi-tenant patterns. Supports PostgreSQL, MySQL, SQLite, MongoDB, and Vitess.
  • 95database-pro
    Use when writing or optimizing SQL queries, designing database schemas, planning database migrations, or administering PostgreSQL/MySQL. Invoke for EXPLAIN analysis, indexing, JSONB, replication, VACUUM, schema changes, data backfills, zero-downtime deployment, monitoring, or partitioning.
  • 100database-designer
    Use when the user asks to design database schemas, plan data migrations, optimize queries, choose between SQL and NoSQL, or model data relationships.
  • 90database-designer
    Database design with schema analysis, index optimization, and migration generation for PostgreSQL, MySQL, MongoDB, and DynamoDB. Use when designing schemas, optimizing queries, planning migrations, or analyzing database performance.
  • 100db-migrator
    Database schema migration, versioning, and rollback management for SQL and NoSQL databases. Use when: (1) creating database migrations, (2) modifying table schemas, (3) adding/removing columns, (4) creating indexes, (5) data migrations, (6) rollback operations, (7)…

Feedback and reports

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