メニュー
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.
デフォルトでは、最初にソースを確認する Prompt が選択されています。直接コマンドに切り替えるか、ローカルコピーをダウンロードすることもできます。
インストールを決める前に、SKILL.md と SkillsMP に表示されている付属ファイルをお読みください。
Codex または Claude でインストール この Prompt をコピーして Codex、Claude、または他のアシスタントに貼り付けると、Skill ページを確認してインストールできます。
直接コマンドでは確認用 Prompt が省略されます。実行前にソースを確認してください。
npx skills add https://github.com/kumaran-is/claude-code-onboarding --skill database-schema-designerコマンドは1行のまま表示されます。コピー前に横へスクロールして全体を確認してください。
ローカルで確認しますか？SkillsMP が現在取得できるファイルをダウンロードできます。
SOC 職業分類に基づく
SKILL.md を表示中
| name | database-schema-designer |
| description | 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. |
| argument-hint | [domain or requirements] |
| allowed-tools | Read |
| agent | database-designer |
| context | fork |
| metadata | {"triggers":"database schema, schema design, SQL design, NoSQL design, ERD, database design, create tables, model data, data modeling","related-skills":"architecture-design, java-spring-api, nestjs-api, python-dev","domain":"infrastructure","role":"architect","scope":"design","output-format":"document"} |
| last-reviewed | 2026-03-14 |
NO SCHEMA MIGRATION WITHOUT A DOWN (ROLLBACK) SCRIPT AND AN INDEX ON EVERY FOREIGN KEY — unindexed FKs cause full table scans; irreversible migrations cause production incidents
Design production-ready database schemas with best practices built-in.
| Trigger | Example |
|---|---|
design schema | "design a schema for user authentication" |
database design | "database design for multi-tenant SaaS" |
create tables | "create tables for a blog system" |
schema for | "schema for inventory management" |
model data | "model data for real-time analytics" |
I need a database | "I need a database for tracking orders" |
design NoSQL | "design NoSQL schema for product catalog" |
| Task | Approach | Key Consideration |
|---|---|---|
| New schema | Normalize to 3NF first | Domain modeling over UI |
| SQL vs NoSQL | Access patterns decide | Read/write ratio matters |
| Primary keys | INT or UUID | UUID for distributed systems |
| Foreign keys | Always constrain | ON DELETE strategy critical |
| Indexes | FKs + WHERE columns | Column order matters |
| Migrations | Always reversible | Backward compatible first |
reference/data-types-reference.md for type guidesreference/constraints-and-relationships.md for patternsRead reference/normalization-guide.md for 1NF/2NF/3NF rules and examples.
reference/indexing-strategy.md for when to index and composite index rulesRead reference/migration-patterns.md for zero-downtime patterns and rollback strategies.
Read reference/expand-contract-migrations.md for zero-downtime Expand-Contract strategy, CONCURRENTLY index operations, and batched SKIP LOCKED backfills.
For MongoDB, Firestore, and other document databases, read reference/nosql-design-patterns.md for embedding vs referencing patterns and Firestore-specific design rules.
| Command | When to Use |
|---|---|
design schema for {domain} | Start fresh -- full schema generation |
normalize {table} | Fix existing table -- apply normalization rules |
add indexes for {table} | Performance issues -- generate index strategy |
migration for {change} | Schema evolution -- create reversible migration |
review schema | Code review -- audit existing schema |
NEVER use these types — they have silent, hard-to-debug failure modes:
| Forbidden type | Problem | Use instead |
|---|---|---|
timestamp (no tz) | Stores local time, breaks across timezones | timestamptz |
timetz | Doesn't handle DST — reports wrong time after clock changes | timestamptz |
char(n) / varchar(n) | char pads with spaces, silently breaks equality checks | text |
money | Locale-dependent formatting, rounding errors across servers | numeric |
serial | Sequence ownership breaks on pg_dump/restore | generated always as identity |
float for money | Binary floating-point rounding errors | numeric(10,2) |
| Avoid | Why | Instead |
|---|---|---|
| VARCHAR(255) everywhere | Wastes storage, hides intent | Size appropriately per field |
| Missing FK constraints | Orphaned data | Always define foreign keys |
| No indexes on FKs | Slow JOINs | Index every foreign key |
| Storing dates as strings | Cannot compare/sort | DATE, TIMESTAMPTZ types |
| Non-reversible migrations | Cannot rollback | Always write DOWN migration |
After designing a schema, run through reference/schema-design-checklist.md to verify completeness.
Before generating schemas or queries, consult these sources:
| Source | URL / Tool | Purpose |
|---|---|---|
| PostgreSQL | PostgreSQL MCP server | Schema-aware SQL, introspection, admin-safe workflows |
| Firebase Firestore | Firebase MCP server | Document design, rules, indexes for NoSQL schemas |
| File | Contents |
|---|---|
reference/schema-design-checklist.md | Pre-design, table design, and deployment checklist |
reference/normalization-guide.md | 1NF/2NF/3NF explanations, examples, denormalization guide |
reference/data-types-reference.md | String, numeric, date/time, JSON type guides |
reference/indexing-strategy.md | When to index, composite indexes, B-tree vs hash, EXPLAIN |
reference/constraints-and-relationships.md | PKs, FKs, CHECK, UNIQUE, relationship patterns |
reference/nosql-design-patterns.md | MongoDB/Firestore embedding vs referencing |
reference/migration-patterns.md | Zero-downtime migrations, rollback strategies |
reference/expand-contract-migrations.md | Expand-Contract pattern, CONCURRENTLY index ops, SKIP LOCKED backfills |
assets/templates/migration-template.sql | SQL migration file template |
reference/postgresql-review-checklist.md | PostgreSQL review checklist (used by postgresql-database-reviewer agent) |
Migration conflicts: When migrations fail, check for column type mismatches or missing dependent migrations. Never modify an applied migration — create a new corrective one.
Index creation failures: Verify the column exists and data types support the index type. For large tables, use CREATE INDEX CONCURRENTLY.
DROP TABLE or DROP COLUMN without explicit human approvalusers, orders, payments)security-review-checklist.md §8)After writing SQL/migration code, dispatch this reviewer agent:
postgresql-database-reviewer — query optimization, schema correctness, index coverage, security