{"id":"e4bf0fa0-bb59-4382-a611-52db74b9121c","shortId":"ktpG7n","kind":"skill","title":"database-design","tagline":"Database schema design, optimization, and migration patterns for PostgreSQL, MySQL, and NoSQL databases. Use for designing schemas, writing migrations, or optimizing queries.","description":"# Database Design\n\n## Schema Design Principles\n\n### Normalization Guidelines\n```sql\n-- 1NF: Atomic values, no repeating groups\n-- 2NF: No partial dependencies on composite keys\n-- 3NF: No transitive dependencies\n\n-- Users table (normalized)\nCREATE TABLE users (\n  id SERIAL PRIMARY KEY,\n  email VARCHAR(255) UNIQUE NOT NULL,\n  created_at TIMESTAMPTZ DEFAULT NOW()\n);\n\n-- Addresses table (separate entity)\nCREATE TABLE addresses (\n  id SERIAL PRIMARY KEY,\n  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,\n  street VARCHAR(255),\n  city VARCHAR(100),\n  country VARCHAR(100),\n  is_primary BOOLEAN DEFAULT false\n);\n```\n\n### Denormalization for Performance\n```sql\n-- When read performance matters more than write consistency\nCREATE TABLE order_summaries (\n  id SERIAL PRIMARY KEY,\n  order_id INTEGER REFERENCES orders(id),\n  customer_name VARCHAR(255),  -- Denormalized from customers\n  total_amount DECIMAL(10,2),\n  item_count INTEGER,\n  last_updated TIMESTAMPTZ DEFAULT NOW()\n);\n```\n\n## Index Design\n\n### Common Index Patterns\n```sql\n-- B-tree (default) for equality and range queries\nCREATE INDEX idx_users_email ON users(email);\n\n-- Composite index (order matters!)\nCREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);\n\n-- Partial index for specific conditions\nCREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL;\n\n-- GIN index for array/JSONB columns\nCREATE INDEX idx_posts_tags ON posts USING GIN(tags);\n\n-- Covering index (includes additional columns)\nCREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE (total, status);\n```\n\n### Index Analysis\n```sql\n-- Check index usage\nSELECT\n  schemaname, tablename, indexname,\n  idx_scan, idx_tup_read, idx_tup_fetch\nFROM pg_stat_user_indexes\nORDER BY idx_scan DESC;\n\n-- Find missing indexes\nSELECT\n  relname, seq_scan, seq_tup_read,\n  idx_scan, idx_tup_fetch\nFROM pg_stat_user_tables\nWHERE seq_scan > idx_scan\nORDER BY seq_tup_read DESC;\n```\n\n## Migration Patterns\n\n### Safe Migration Template\n```sql\n-- Always use transactions\nBEGIN;\n\n-- Add column with default (non-blocking in PG 11+)\nALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';\n\n-- Create index concurrently (doesn't lock table)\nCREATE INDEX CONCURRENTLY idx_users_status ON users(status);\n\n-- Backfill data in batches\nUPDATE users SET status = 'active' WHERE status IS NULL AND id BETWEEN 1 AND 10000;\n\nCOMMIT;\n```\n\n### Zero-Downtime Migrations\n```\n1. Add new column (nullable)\n2. Deploy code that writes to both columns\n3. Backfill old data\n4. Deploy code that reads from new column\n5. Remove old column\n```\n\n## Query Optimization\n\n### EXPLAIN Analysis\n```sql\n-- Always use EXPLAIN ANALYZE\nEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)\nSELECT * FROM orders WHERE user_id = 123 AND status = 'pending';\n\n-- Key metrics to watch:\n-- - Seq Scan vs Index Scan\n-- - Actual rows vs Estimated rows\n-- - Buffers: shared hit vs read\n```\n\n### Common Optimizations\n```sql\n-- Use EXISTS instead of IN for large sets\nSELECT * FROM users u\nWHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);\n\n-- Pagination with keyset (cursor) instead of OFFSET\nSELECT * FROM posts\nWHERE created_at < '2024-01-01'\nORDER BY created_at DESC\nLIMIT 20;\n\n-- Use CTEs for complex queries\nWITH active_users AS (\n  SELECT id FROM users WHERE last_login > NOW() - INTERVAL '30 days'\n)\nSELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users);\n```\n\n## Constraints & Data Integrity\n\n```sql\n-- Primary key\nALTER TABLE users ADD PRIMARY KEY (id);\n\n-- Foreign key with cascade\nALTER TABLE orders ADD CONSTRAINT fk_orders_user\n  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;\n\n-- Check constraint\nALTER TABLE products ADD CONSTRAINT chk_price_positive\n  CHECK (price >= 0);\n\n-- Unique constraint\nALTER TABLE users ADD CONSTRAINT uniq_users_email UNIQUE (email);\n\n-- Exclusion constraint (no overlapping ranges)\nALTER TABLE reservations ADD CONSTRAINT excl_no_overlap\n  EXCLUDE USING gist (room_id WITH =, tsrange(start_time, end_time) WITH &&);\n```\n\n## Best Practices\n\n- Use UUIDs for public-facing IDs, SERIAL/BIGSERIAL for internal\n- Always add `created_at` and `updated_at` timestamps\n- Use soft deletes (`deleted_at`) for important data\n- Design for eventual consistency in distributed systems\n- Document schema decisions in migration files\n- Test migrations on production-size data before deploying","tags":["database","design","agent","skills","moizibnyousaf","agent-skills","claude-code","cli","codex","cursor","developer-tools","productivity"],"capabilities":["skill","source-moizibnyousaf","skill-database-design","topic-agent-skills","topic-claude-code","topic-cli","topic-codex","topic-cursor","topic-developer-tools","topic-productivity"],"categories":["Ai-Agent-Skills"],"synonyms":[],"warnings":[],"endpointUrl":"https://skills.sh/MoizIbnYousaf/Ai-Agent-Skills/database-design","protocol":"skill","transport":"skills-sh","auth":{"type":"none","details":{"cli":"npx skills add MoizIbnYousaf/Ai-Agent-Skills","source_repo":"https://github.com/MoizIbnYousaf/Ai-Agent-Skills","install_from":"skills.sh"}},"qualityScore":"0.700","qualityRationale":"deterministic score 0.70 from registry signals: · indexed on github topic:agent-skills · 1044 github stars · SKILL.md body (4,360 chars)","verified":false,"liveness":"unknown","lastLivenessCheck":null,"agentReviews":{"count":0,"score_avg":null,"cost_usd_avg":null,"success_rate":null,"latency_p50_ms":null,"narrative_summary":null,"summary_updated_at":null},"enrichmentModel":"deterministic:skill-github:v1","enrichmentVersion":1,"enrichedAt":"2026-05-02T18:52:54.757Z","embedding":null,"createdAt":"2026-04-18T21:56:14.787Z","updatedAt":"2026-05-02T18:52:54.757Z","lastSeenAt":"2026-05-02T18:52:54.757Z","tsv":"'-01':483,484 '0':571 '1':363,371,461 '10':142 '100':97,100 '10000':365 '11':320 '123':420 '1nf':34 '2':143,376 '20':328,491 '2024':482 '255':63,94,135 '2nf':40 '3':384 '30':510 '3nf':47 '4':388 '5':396 'activ':200,330,355,498,522 'actual':433 'add':311,324,372,533,544,564,577,592,622 'addit':228 'address':72,78 'alter':321,530,541,561,574,589 'alway':307,405,621 'amount':140 'analysi':243,403 'analyz':408,410 'array/jsonb':213 'atom':35 'b':159 'b-tree':158 'backfil':347,385 'batch':350 'begin':310 'best':609 'block':317 'boolean':103 'buffer':411,438 'cascad':91,540,558 'check':245,559,569 'chk':566 'citi':95 'code':378,390 'column':214,229,312,325,374,383,395,399 'commit':366 'common':154,443 'complex':495 'composit':45,175 'concurr':333,340 'condit':196 'consist':117,640 'constraint':524,545,560,565,573,578,585,593 'count':145 'countri':98 'cover':225,234 'creat':54,67,76,118,167,179,189,197,215,230,331,338,480,487,623 'ctes':493 'cursor':472 'custom':132,138 'data':348,387,525,636,656 'databas':2,4,16,26 'database-design':1 'date':184 'day':511 'decim':141 'decis':646 'default':70,104,150,161,314,329 'delet':90,206,557,631,632 'denorm':106,136 'depend':43,50 'deploy':377,389,658 'desc':191,269,300,489 'design':3,6,19,27,29,153,637 'distribut':642 'document':644 'doesn':334 'downtim':369 'email':61,171,174,204,581,583 'end':606 'entiti':75 'equal':163 'estim':436 'eventu':639 'excl':594 'exclud':597 'exclus':584 'exist':447,459 'explain':402,407,409 'face':616 'fals':105 'fetch':259,284 'file':649 'find':270 'fk':546 'foreign':537,549 'format':412 'gin':210,223 'gist':599 'group':39 'guidelin':32 'hit':440 'id':57,79,84,88,122,127,131,188,238,361,419,467,502,517,520,536,552,555,601,617 'idx':169,181,199,217,232,252,254,257,267,280,282,293,341 'import':635 'includ':227,239 'index':152,155,168,176,180,193,198,211,216,226,231,242,246,264,272,332,339,431 'indexnam':251 'instead':448,473 'integ':85,128,146 'integr':526 'intern':620 'interv':509 'item':144 'key':46,60,82,125,424,529,535,538,550 'keyset':471 'larg':452 'last':147,506 'limit':490 'lock':336 'login':507 'matter':113,178 'metric':425 'migrat':9,22,301,304,370,648,651 'miss':271 'mysql':13 'name':133 'new':373,394 'non':316 'non-block':315 'normal':31,53 'nosql':15 'null':66,209,359 'nullabl':375 'o':464 'o.user':466 'offset':475 'old':386,398 'optim':7,24,401,444 'order':120,126,130,177,182,186,233,236,265,295,416,463,485,514,543,547 'overlap':587,596 'pagin':469 'partial':42,192 'pattern':10,156,302 'pend':423 'perform':108,112 'pg':261,286,319 'posit':568 'post':218,221,478 'postgresql':12 'practic':610 'price':567,570 'primari':59,81,102,124,528,534 'principl':30 'product':563,654 'production-s':653 'public':615 'public-fac':614 'queri':25,166,400,496 'rang':165,588 'read':111,256,279,299,392,442 'refer':86,129,553 'relnam':274 'remov':397 'repeat':38 'reserv':591 'room':600 'row':434,437 'safe':303 'scan':253,268,276,281,292,294,429,432 'schema':5,20,28,645 'schemanam':249 'select':248,273,414,454,460,476,501,512,519 'separ':74 'seq':275,277,291,297,428 'serial':58,80,123 'serial/bigserial':618 'set':353,453 'share':439 'size':655 'skill' 'skill-database-design' 'soft':630 'source-moizibnyousaf' 'specif':195 'sql':33,109,157,244,306,404,445,527 'start':604 'stat':262,287 'status':241,326,343,346,354,357,422 'street':92 'summari':121 'system':643 'tabl':52,55,73,77,119,289,322,337,531,542,562,575,590 'tablenam':250 'tag':219,224 'templat':305 'test':650 'text':413 'time':605,607 'timestamp':628 'timestamptz':69,149 'topic-agent-skills' 'topic-claude-code' 'topic-cli' 'topic-codex' 'topic-cursor' 'topic-developer-tools' 'topic-productivity' 'total':139,240 'transact':309 'transit':49 'tree':160 'tsrang':603 'tup':255,258,278,283,298 'u':457 'u.id':468 'uniq':579 'uniqu':64,572,582 'updat':148,351,626 'usag':247 'use':17,222,308,406,446,492,598,611,629 'user':51,56,83,87,170,173,183,187,201,203,237,263,288,323,342,345,352,418,456,499,504,516,523,532,548,551,554,576,580 'uuid':612 'valu':36 'varchar':62,93,96,99,134,327 'vs':430,435,441 'watch':427 'write':21,116,380 'zero':368 'zero-downtim':367","prices":[{"id":"30884f2c-93de-4ca6-949a-a1839d4194aa","listingId":"e4bf0fa0-bb59-4382-a611-52db74b9121c","amountUsd":"0","unit":"free","nativeCurrency":null,"nativeAmount":null,"chain":null,"payTo":null,"paymentMethod":"skill-free","isPrimary":true,"details":{"org":"MoizIbnYousaf","category":"Ai-Agent-Skills","install_from":"skills.sh"},"createdAt":"2026-04-18T21:56:14.787Z"}],"sources":[{"listingId":"e4bf0fa0-bb59-4382-a611-52db74b9121c","source":"github","sourceId":"MoizIbnYousaf/Ai-Agent-Skills/database-design","sourceUrl":"https://github.com/MoizIbnYousaf/Ai-Agent-Skills/tree/main/skills/database-design","isPrimary":false,"firstSeenAt":"2026-04-18T21:56:14.787Z","lastSeenAt":"2026-05-02T18:52:54.757Z"}],"details":{"listingId":"e4bf0fa0-bb59-4382-a611-52db74b9121c","quickStartSnippet":null,"exampleRequest":null,"exampleResponse":null,"schema":null,"openapiUrl":null,"agentsTxtUrl":null,"citations":[],"useCases":[],"bestFor":[],"notFor":[],"kindDetails":{"org":"MoizIbnYousaf","slug":"database-design","github":{"repo":"MoizIbnYousaf/Ai-Agent-Skills","stars":1044,"topics":["agent-skills","claude-code","cli","codex","cursor","developer-tools","productivity"],"license":"mit","html_url":"https://github.com/MoizIbnYousaf/Ai-Agent-Skills","pushed_at":"2026-04-13T19:04:12Z","description":"my curated agent skills library ","skill_md_sha":"47e79964662156a8fd2d548a3f49e748da97211b","skill_md_path":"skills/database-design/SKILL.md","default_branch":"main","skill_tree_url":"https://github.com/MoizIbnYousaf/Ai-Agent-Skills/tree/main/skills/database-design"},"layout":"multi","source":"github","category":"Ai-Agent-Skills","frontmatter":{"name":"database-design","license":"MIT","description":"Database schema design, optimization, and migration patterns for PostgreSQL, MySQL, and NoSQL databases. Use for designing schemas, writing migrations, or optimizing queries."},"skills_sh_url":"https://skills.sh/MoizIbnYousaf/Ai-Agent-Skills/database-design"},"updatedAt":"2026-05-02T18:52:54.757Z"}}