跳到主内容
智客 ZICQ

技能库 智客分类:设计创意 database-schema-designer

Database Schema Designer

为 SQL 和 NoSQL 数据库设计坚固,可扩展的数据库计划. 提供正常化准则、指数化战略、移徙模式、制约设计和业绩优化。 确保数据的完整性,查询性能,以及可维护的数据模型.

4219 安装量

官方网址:skills.sh

技能介绍

先看中文介绍;官方 description 原文单独保留,不改写 SKILL.md。

做什么

为 SQL 和 NoSQL 数据库设计坚固,可扩展的数据库计划. 提供正常化准则、指数化战略、移徙模式、制约设计和业绩优化。 确保数据的完整性,查询性能,以及可维护的数据模型.

何时用

行动

代理如何加载

按 Agent Skills 渐进披露:启动时只加载 name 与 description(约 100 token);任务匹配后才读入整份 SKILL.md 正文;scripts/、references/、assets/ 仅在需要时再读。 本文件正文结构:Database Schema Designer、Quick Start、Triggers、Key Terms、Quick Reference、Process Overview。

文件分析

文件分析:这是一份仅含 SKILL.md 的指令型技能,代理激活后整份正文进入上下文。 正文约 681 行,超过规范建议的 500 行,激活时仍会整份载入。

官方 description(原文)

Design robust, scalable database schemas for SQL and NoSQL databases. Provides normalization guidelines, indexing strategies, migration patterns, constraint design, and performance optimization. Ensures data integrity, query performance, and maintainable data models.

Database Schema DesignerQuick StartTriggersKey TermsQuick ReferenceProcess OverviewCommandsCore PrinciplesAnti-PatternsVerification ChecklistNormal Forms1st Normal Form (1NF)

· 许可:MIT

来源分类:skills.sh agent-skill

SKILL.md 与 Agent 调用

官方规范 ↗
name
database-schema-designer
description
Design robust, scalable database schemas for SQL and NoSQL databases. Provides normalization guidelines, indexing strategies, migration patterns, constraint design, and performance optimization. Ensures data integrity, query performance, and maintainable data models.
许可
MIT
  1. 发现技能客户端向 Agent 提供名称与描述目录。
  2. 匹配与调用用户指定或任务匹配后,载入 SKILL.md 指令。
  3. 按需加载按步骤读取参考文档、使用脚本与素材。

具体调用语法与可用工具以目标 Agent 客户端为准。 查看调用机制说明 ↗

安装这个技能

Skills CLI ↗

先选择目标 Agent 和安装范围,保留技能包的附属文件,安装后检查客户端能否发现该技能。

交给 Agent 安装

复制安装指令给支持 Agent Skills 的代理,确认其中的目标目录与客户端匹配。

把 Agent Skill「database-schema-designer」安装到我的项目:SKILL.md 原文与官方 description 见 https://zicq.com/zh/skills/skl-0bdf1d4a0fc60f08-%E6%95%B0%E6%8D%AE%E5%BA%93Schema-%E8%AE%BE%E8%AE%A1%E5%99%A8.html
请存为 .cursor/skills/database-schema-designer/SKILL.md 或 .claude/skills/database-schema-designer/SKILL.md,frontmatter 的 name 与 description 保持原样,不要改写。

GitHub 完整包 ↗

终端安装 · Skills CLI

需要 Node.js 与 npx。先查看仓库技能列表,确认实际名称。

npx skills add 'https://github.com/softaworks/agent-toolkit' --list

npx skills add 'https://github.com/softaworks/agent-toolkit' --skill 'database-schema-designer'

CLI 会交互选择目标 Agent,默认安装到项目;用户级安装使用 -g。先通过查看命令核对仓库内容,再用 npx skills list 检查已安装技能。

阅读排版
--- name: database-schema-designer description: Design robust, scalable database schemas for SQL and NoSQL databases. Provides normalization guidelines, indexing strategies, migration patterns, constraint design, and performance optimization. Ensures data integrity, query performance, and maintainable data models. license: MIT --- # Database Schema Designer Design production-ready database schemas with best practices built-in. --- ## Quick Start Just describe your data model: ``` design a schema for an e-commerce platform with users, products, orders ``` You'll get a complete SQL schema like: ```sql CREATE TABLE users ( id BIGINT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(id), total DECIMAL(10,2) NOT NULL, INDEX idx_orders_user (user_id) ); ``` **What to include in your request:** - Entities (users, products, orders) - Key relationships (users have orders, orders have items) - Scale hints (high-traffic, millions of records) - Database preference (SQL/NoSQL) - defaults to SQL if not specified --- ## Triggers | 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" | --- ## Key Terms | Term | Definition | |------|------------| | **Normalization** | Organizing data to reduce redundancy (1NF → 2NF → 3NF) | | **3NF** | Third Normal Form - no transitive dependencies between columns | | **OLTP** | Online Transaction Processing - write-heavy, needs normalization | | **OLAP** | Online Analytical Processing - read-heavy, benefits from denormalization | | **Foreign Key (FK)** | Column that references another table's primary key | | **Index** | Data structure that speeds up queries (at cost of slower writes) | | **Access Pattern** | How your app reads/writes data (queries, joins, filters) | | **Denormalization** | Intentionally duplicating data to speed up reads | --- ## Quick Reference | 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 | --- ## Process Overview ``` Your Data Requirements | v +-----------------------------------------------------+ | Phase 1: ANALYSIS | | * Identify entities and relationships | | * Determine access patterns (read vs write heavy) | | * Choose SQL or NoSQL based on requirements | +-----------------------------------------------------+ | v +-----------------------------------------------------+ | Phase 2: DESIGN | | * Normalize to 3NF (SQL) or embed/reference (NoSQL) | | * Define primary keys and foreign keys | | * Choose appropriate data types | | * Add constraints (UNIQUE, CHECK, NOT NULL) | +-----------------------------------------------------+ | v +-----------------------------------------------------+ | Phase 3: OPTIMIZE | | * Plan indexing strategy | | * Consider denormalization for read-heavy queries | | * Add timestamps (created_at, updated_at) | +-----------------------------------------------------+ | v +-----------------------------------------------------+ | Phase 4: MIGRATE | | * Generate migration scripts (up + down) | | * Ensure backward compatibility | | * Plan zero-downtime deployment | +-----------------------------------------------------+ | v Production-Ready Schema ``` --- ## Commands | Command | When to Use | Action | |---------|-------------|--------| | `design schema for {domain}` | Starting fresh | Full schema generation | | `normalize {table}` | Fixing 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 | **Workflow:** Start with `design schema` → iterate with `normalize` → optimize with `add indexes` → evolve with `migration` --- ## Core Principles | Principle | WHY | Implementation | |-----------|-----|----------------| | Model the Domain | UI changes, domain doesn't | Entity names reflect business concepts | | Data Integrity First | Corruption is costly to fix | Constraints at database level | | Optimize for Access Pattern | Can't optimize for both | OLTP: normalized, OLAP: denormalized | | Plan for Scale | Retrofitting is painful | Index strategy + partitioning plan | --- ## Anti-Patterns | Avoid | Why | Instead | |-------|-----|---------| | VARCHAR(255) everywhere | Wastes storage, hides intent | Size appropriately per field | | FLOAT for money | Rounding errors | DECIMAL(10,2) | | Missing FK constraints | Orphaned data | Always define foreign keys | | No indexes on FKs | Slow JOINs | Index every foreign key | | Storing dates as strings | Can't compare/sort | DATE, TIMESTAMP types | | SELECT * in queries | Fetches unnecessary data | Explicit column lists | | Non-reversible migrations | Can't rollback | Always write DOWN migration | | Adding NOT NULL without default | Breaks existing rows | Add nullable, backfill, then constrain | --- ## Verification Checklist After designing a schema: - [ ] Every table has a primary key - [ ] All relationships have foreign key constraints - [ ] ON DELETE strategy defined for each FK - [ ] Indexes exist on all foreign keys - [ ] Indexes exist on frequently queried columns - [ ] Appropriate data types (DECIMAL for money, etc.) - [ ] NOT NULL on required fields - [ ] UNIQUE constraints where needed - [ ] CHECK constraints for validation - [ ] created_at and updated_at timestamps - [ ] Migration scripts are reversible - [ ] Tested on staging with production data ---
Deep Dive: Normalization (SQL) ### Normal Forms | Form | Rule | Violation Example | |------|------|-------------------| | **1NF** | Atomic values, no repeating groups | `product_ids = '1,2,3'` | | **2NF** | 1NF + no partial dependencies | customer_name in order_items | | **3NF** | 2NF + no transitive dependencies | country derived from postal_code | ### 1st Normal Form (1NF) ```sql -- BAD: Multiple values in column CREATE TABLE orders ( id INT PRIMARY KEY, product_ids VARCHAR(255) -- '101,102,103' ); -- GOOD: Separate table for items CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT ); CREATE TABLE order_items ( id INT PRIMARY KEY, order_id INT REFERENCES orders(id), product_id INT ); ``` ### 2nd Normal Form (2NF) ```sql -- BAD: customer_name depends only on customer_id CREATE TABLE order_items ( order_id INT, product_id INT, customer_name VARCHAR(100), -- Partial dependency! PRIMARY KEY (order_id, product_id) ); -- GOOD: Customer data in separate table CREATE TABLE customers ( id INT PRIMARY KEY, name VARCHAR(100) ); ``` ### 3rd Normal Form (3NF) ```sql -- BAD: country depends on postal_code CREATE TABLE customers ( id INT PRIMARY KEY, postal_code VARCHAR(10), country VARCHAR(50) -- Transitive dependency! ); -- GOOD: Separate postal_codes table CREATE TABLE postal_codes ( code VARCHAR(10) PRIMARY KEY, country VARCHAR(50) ); ``` ### When to Denormalize | Scenario | Denormalization Strategy | |----------|-------------------------| | Read-heavy reporting | Pre-calculated aggregates | | Expensive JOINs | Cached derived columns | | Analytics dashboards | Materialized views | ```sql -- Denormalized for performance CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT, total_amount DECIMAL(10,2), -- Calculated item_count INT -- Calculated ); ```
Deep Dive: Data Types ### String Types | Type | Use Case | Example | |------|----------|---------| | CHAR(n) | Fixed length | State codes, ISO dates | | VARCHAR(n) | Variable length | Names, emails | | TEXT | Long content | Articles, descriptions | ```sql -- Good sizing email VARCHAR(255) phone VARCHAR(20) country_code CHAR(2) ``` ### Numeric Types | Type | Range | Use Case | |------|-------|----------| | TINYINT | -128 to 127 | Age, status codes | | SMALLINT | -32K to 32K | Quantities | | INT | -2.1B to 2.1B | IDs, counts | | BIGINT | Very large | Large IDs, timestamps | | DECIMAL(p,s) | Exact precision | Money | | FLOAT/DOUBLE | Approximate | Scientific data | ```sql -- ALWAYS use DECIMAL for money price DECIMAL(10, 2) -- $99,999,999.99 -- NEVER use FLOAT for money price FLOAT -- Rounding errors! ``` ### Date/Time Types ```sql DATE -- 2025-10-31 TIME -- 14:30:00 DATETIME -- 2025-10-31 14:30:00 TIMESTAMP -- Auto timezone conversion -- Always store in UTC created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ``` ### Boolean ```sql -- PostgreSQL is_active BOOLEAN DEFAULT TRUE -- MySQL is_active TINYINT(1) DEFAULT 1 ```
Deep Dive: Indexing Strategy ### When to Create Indexes | Always Index | Reason | |--------------|--------| | Foreign keys | Speed up JOINs | | WHERE clause columns | Speed up filtering | | ORDER BY columns | Speed up sorting | | Unique constraints | Enforced uniqueness | ```sql -- Foreign key index CREATE INDEX idx_orders_customer ON orders(customer_id); -- Query pattern index CREATE INDEX idx_orders_status_date ON orders(status, created_at); ``` ### Index Types | Type | Best For | Example | |------|----------|---------| | B-Tree | Ranges, equality | `price > 100` | | Hash | Exact matches only | `email = '[email protected]'` | | Full-text | Text search | `MATCH AGAINST` | | Partial | Subset of rows | `WHERE is_active = true` | ### Composite Index Order ```sql CREATE INDEX idx_customer_status ON orders(customer_id, status); -- Uses index (customer_id first) SELECT * FROM orders WHERE customer_id = 123; SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending'; -- Does NOT use index (status alone) SELECT * FROM orders WHERE status = 'pending'; ``` **Rule:** Most selective column first, or column most queried alone. ### Index Pitfalls | Pitfall | Problem | Solution | |---------|---------|----------| | Over-indexing | Slow writes | Only index what's queried | | Wrong column order | Unused index | Match query patterns | | Missing FK indexes | Slow JOINs | Always index FKs |
Deep Dive: Constraints ### Primary Keys ```sql -- Auto-increment (simple) id INT AUTO_INCREMENT PRIMARY KEY -- UUID (distributed systems) id CHAR(36) PRIMARY KEY DEFAULT (UUID()) -- Composite (junction tables) PRIMARY KEY (student_id, course_id) ``` ### Foreign Keys ```sql FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE -- Delete children with parent ON DELETE RESTRICT -- Prevent deletion if referenced ON DELETE SET NULL -- Set to NULL when parent deleted ON UPDATE CASCADE -- Update children when parent changes ``` | Strategy | Use When | |----------|----------| | CASCADE | Dependent data (order_items) | | RESTRICT | Important references (prevent accidents) | | SET NULL | Optional relationships | ### Other Constraints ```sql -- Unique email VARCHAR(255) UNIQUE NOT NULL -- Composite unique UNIQUE (student_id, course_id) -- Check price DECIMAL(10,2) CHECK (price >= 0) discount INT CHECK (discount BETWEEN 0 AND 100) -- Not null name VARCHAR(100) NOT NULL ```
Deep Dive: Relationship Patterns ### One-to-Many ```sql CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT NOT NULL REFERENCES customers(id) ); CREATE TABLE order_items ( id INT PRIMARY KEY, order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE, product_id INT NOT NULL, quantity INT NOT NULL ); ``` ### Many-to-Many ```sql -- Junction table CREATE TABLE enrollments ( student_id INT REFERENCES students(id) ON DELETE CASCADE, course_id INT REFERENCES courses(id) ON DELETE CASCADE, enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (student_id, course_id) ); ``` ### Self-Referencing ```sql CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, manager_id INT REFERENCES employees(id) ); ``` ### Polymorphic ```sql -- Approach 1: Separate FKs (stronger integrity) CREATE TABLE comments ( id INT PRIMARY KEY, content TEXT NOT NULL, post_id INT REFERENCES posts(id), photo_id INT REFERENCES photos(id), CHECK ( (post_id IS NOT NULL AND photo_id IS NULL) OR (post_id IS NULL AND photo_id IS NOT NULL) ) ); -- Approach 2: Type + ID (flexible, weaker integrity) CREATE TABLE comments ( id INT PRIMARY KEY, content TEXT NOT NULL, commentable_type VARCHAR(50) NOT NULL, commentable_id INT NOT NULL ); ```
Deep Dive: NoSQL Design (MongoDB) ### Embedding vs Referencing | Factor | Embed | Reference | |--------|-------|-----------| | Access pattern | Read together | Read separately | | Relationship | 1:few | 1:many | | Document size | Small | Approaching 16MB | | Update frequency | Rarely | Frequently | ### Embedded Document ```json { "_id": "order_123", "customer": { "id": "cust_456", "name": "Jane Smith", "email": "[email protected]" }, "items": [ { "product_id": "prod_789", "quantity": 2, "price": 29.99 } ], "total": 109.97 } ``` ### Referenced Document ```json { "_id": "order_123", "customer_id": "cust_456", "item_ids": ["item_1", "item_2"], "total": 109.97 } ``` ### MongoDB Indexes ```javascript // Single field db.users.createIndex({ email: 1 }, { unique: true }); // Composite db.orders.createIndex({ customer_id: 1, created_at: -1 }); // Text search db.articles.createIndex({ title: "text", content: "text" }); // Geospatial db.stores.createIndex({ location: "2dsphere" }); ```
Deep Dive: Migrations ### Migration Best Practices | Practice | WHY | |----------|-----| | Always reversible | Need to rollback | | Backward compatible | Zero-downtime deploys | | Schema before data | Separate concerns | | Test on staging | Catch issues early | ### Adding a Column (Zero-Downtime) ```sql -- Step 1: Add nullable column ALTER TABLE users ADD COLUMN phone VARCHAR(20); -- Step 2: Deploy code that writes to new column -- Step 3: Backfill existing rows UPDATE users SET phone = '' WHERE phone IS NULL; -- Step 4: Make required (if needed) ALTER TABLE users MODIFY phone VARCHAR(20) NOT NULL; ``` ### Renaming a Column (Zero-Downtime) ```sql -- Step 1: Add new column ALTER TABLE users ADD COLUMN email_address VARCHAR(255); -- Step 2: Copy data UPDATE users SET email_address = email; -- Step 3: Deploy code reading from new column -- Step 4: Deploy code writing to new column -- Step 5: Drop old column ALTER TABLE users DROP COLUMN email; ``` ### Migration Template ```sql -- Migration: YYYYMMDDHHMMSS_description.sql -- UP BEGIN; ALTER TABLE users ADD COLUMN phone VARCHAR(20); CREATE INDEX idx_users_phone ON users(phone); COMMIT; -- DOWN BEGIN; DROP INDEX idx_users_phone ON users; ALTER TABLE users DROP COLUMN phone; COMMIT; ```
Deep Dive: Performance Optimization ### Query Analysis ```sql EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending'; ``` | Look For | Meaning | |----------|---------| | type: ALL | Full table scan (bad) | | type: ref | Index used (good) | | key: NULL | No index used | | rows: high | Many rows scanned | ### N+1 Query Problem ```python # BAD: N+1 queries orders = db.query("SELECT * FROM orders") for order in orders: customer = db.query(f"SELECT * FROM customers WHERE id = {order.customer_id}") # GOOD: Single JOIN results = db.query(""" SELECT orders.*, customers.name FROM orders JOIN customers ON orders.customer_id = customers.id """) ``` ### Optimization Techniques | Technique | When to Use | |-----------|-------------| | Add indexes | Slow WHERE/ORDER BY | | Denormalize | Expensive JOINs | | Pagination | Large result sets | | Caching | Repeated queries | | Read replicas | Read-heavy load | | Partitioning | Very large tables |
--- ## Extension Points 1. **Database-Specific Patterns:** Add MySQL vs PostgreSQL vs SQLite variations 2. **Advanced Patterns:** Time-series, event sourcing, CQRS, multi-tenancy 3. **ORM Integration:** TypeORM, Prisma, SQLAlchemy patterns 4. **Monitoring:** Query performance tracking, slow query alerts

相关技能

设计创意

Automation Workflows

设计和实施自动化工作流程,以节省时间和规模化操作作为独家. 用于识别重复任务实现自动化,构建跨工具的工作流程,设置触发器和行动,或优化现有自动化. 包括自动化机会识别,工作流程设计,工具选择(Zapier,Make,n8n),测试,和维护. 触发"自动","自动","工作流程自动…

设计创意

Ui Ux Pro Max

UI/UX设计智能及建筑抛光接口实施指导. 当用户要求UI设计,UX流量,信息架构,视觉风格方向,设计系统/托盘,组件规格,副本/显微镜,可访问性,或生成/critique/refine前端UI(HTML/CSS/JS,React,Next.js,Vue,Svelte,Tailw…

设计创意

Frontend Design

创建美丽现代UI的专家前端设计指南. 在构建起落架页面,仪表板,或任何用户界面时使用.

设计创意

N8n Workflow Automation

设计和输出 n8n 工作流程 JSON 有强力触发器, idempotency, 错误处理, 记录, 重试, 以及 人入"一站"审查队列. 当您需要可审计的自动化时使用.