database-design
Schema design, normalization, indexing, migrations, and query optimization for SQL and NoSQL
ソース情報
- リポジトリ
- cosmicstack-labs/mercury-agent-skills
- ソースの最終更新活動
- 2026年5月10日 06:29
- 検出された SKILL.md の言語
- 英語
- スター
- 476
- フォーク
- 63
インストール方法
デフォルトでは、最初にソースを確認する Prompt が選択されています。直接コマンドに切り替えるか、ローカルコピーをダウンロードすることもできます。
ソースファイルを確認
インストールを決める前に、SKILL.md と SkillsMP に表示されている付属ファイルをお読みください。
SKILL.md を表示中
SKILL.md
ソースの指示 · 読み取り専用プレビュー- name
- database-design
- description
- Schema design, normalization, indexing, migrations, and query optimization for SQL and NoSQL
- metadata
- {"author":"cosmicstack-labs","version":"1.0.0","category":"backend","tags":["database","sql","nosql","schema","indexing","migrations"]}
# Database Design
Design efficient, scalable database schemas.
## Normalization
| Normal Form | Rule | Violation Example |
|-------------|------|-------------------|
| 1NF | Atomic columns, no repeating groups | `phone_numbers: "555-0100,555-0200"` |
| 2NF | 1NF + all non-key cols depend on full PK | Orders table with product details |
| 3NF | 2NF + no transitive dependencies | Employee with department_name (dept info in dept table) |
**When to denormalize**: Read-heavy workloads, reporting, caching layers.
## Indexing Strategy
### Index Types
| Index | Use Case | Tradeoff |
|-------|----------|----------|
| B-Tree | General purpose, equality + range | Default, usually best |
| Hash | Equality only | Fast lookups, no sorting |
| GIN | Array/JSON | Slightly slower writes |
| BRIN | Huge sorted tables | Very small size |
### Rules
- Index columns used in WHERE, JOIN, ORDER BY
- Index foreign keys
- Covering indexes for frequent queries
- Don't over-index (slows writes, uses space)
- Drop unused indexes (use pg_stat_user_indexes)
## Migrations
- One file per change, sequential naming
- Always reversible (up/down pair)
- Test on staging before production
- Run in transactions where possible
- Avoid locking tables on large tables (use pt-online-schema-change)
## Query Optimization
- EXPLAIN ANALYZE every slow query
- Look for: Seq scans, nested loops, temp files
- Common fixes: missing index, bad join order, OR conditions
- N+1 problem: batch find / includes / joins
- Limit offsets for pagination (use cursor-based)
GitHubで見る