mcpField
D
database-schema-design

database-schema-design

github.com/seb1n数据与数据库236144 次安装12 周前更新
代码仓库

关于 database-schema-design

database-schema-design 是面向 AI Agent 的数据库模式设计技能，可根据应用需求建立规范化的关系型数据库结构，并生成可执行的 SQL DDL。它涵盖关系建模、数据完整性约束和索引设计。

主要功能

  1. 01分析实体、属性与关系
  2. 02设计主键和外键
  3. 03规范化至 3NF
  4. 04定义约束并设计索引
  5. 05生成 SQL DDL 脚本
  6. 06验证并迭代模式

适用场景

  1. 01从应用需求设计关系型数据库
  2. 02为现有模式添加新功能
  3. 03生成 PostgreSQL 或 MySQL DDL

安装

复制下面的命令，在终端中运行。

$npx skills add https://github.com/seb1n/awesome-ai-agent-skills --skill database-schema-design

安装后在 Claude Code 中输入/database-schema-design确认已加载。

详细介绍

数据库架构设计

此技能可让 AI Agent 根据应用需求设计健壮、规范化的关系数据库架构。Agent 会分析实体，使用适当的数据类型和约束定义表，建立关系（一对一、一对多、多对多），将规范化应用到 3NF，为查询性能创建索引，并生成可直接执行的完整 SQL DDL 脚本。

工作流

  1. 收集并分析需求： 采访用户或解析规格说明文档，以识别所有实体、实体属性及其相互关系。明确基数（1:1、1:N、M:N）、必填字段与可选字段，以及领域特定约束，例如邮箱唯一、价格为正或状态必须属于枚举值。继续之前，明确记录所有假设。

  2. 对实体和关系建模： 将需求转换为逻辑数据模型。将每个实体定义为一张表，选择适当的主键（优先使用代理整数键或 UUID 键以确保稳定性），并映射关系。一对多关系中，在“多”端添加外键。多对多关系中，创建一个引用两个父表的联结表，并使用复合主键。一对一关系中，使用共享主键或唯一外键。

  3. 应用规范化： 根据各范式检查架构。确保每个非键列都依赖于整个主键（2NF），且只依赖于主键（3NF）。拆分包含传递依赖的表。只有在读操作较多的查询模式确有需要时才有策略地反规范化，并记录其中的权衡。

  4. 定义约束和索引： 添加 NOT NULL、UNIQUE、CHECK 和 DEFAULT 约束，在数据库层面强制保证数据完整性。在外键列、WHERE 子句中使用的列，以及用于排序或分组的列上创建索引。针对多列查询模式，可考虑使用复合索引。

  5. 生成 SQL DDL 脚本： 生成完整的 CREATE TABLE 语句，包含所有列、类型、约束和索引。使用 IF NOT EXISTS 确保幂等性。安排语句顺序，确保先创建被引用的表，再创建引用这些表的表。

  6. 验证并迭代： 根据原始需求检查架构。确认所有实体均已表示、所有关系均已正确建模，且不存在数据完整性缺口。根据反馈进行调整。

支持的技术

  • 关系数据库： PostgreSQL、MySQL、MariaDB、SQLite、SQL Server、Oracle
  • 架构工具： dbdiagram.io、pgModeler、MySQL Workbench、DBeaver
  • 迁移框架： Flyway、Liquibase、Alembic、Prisma Migrate、Knex

使用方法

请提供应用领域的描述及其数据需求。包括主要实体、实体属性，以及它们之间的关系。Agent 会生成规范化架构和完整的 DDL。你可以指定数据库（例如，使用 PostgreSQL 或 MySQL 语法），也可以要求修改架构，例如添加审计列或软删除功能。

示例

示例 1：电子商务应用架构

请求： 为包含用户、商品、订单和订单项的电子商务应用设计架构。

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    sku VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
    total_amount NUMERIC(12, 2) NOT NULL CHECK (total_amount >= 0),
    shipping_address TEXT NOT NULL,
    ordered_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price >= 0),
    UNIQUE (order_id, product_id)
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
示例 2：通过架构迁移添加评论功能

请求： 在现有电子商务架构中添加商品评论表。用户可以为每件商品留一条评论，并给出评分和可选评论内容。

CREATE TABLE reviews (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    UNIQUE (user_id, product_id)
);

CREATE INDEX idx_reviews_product_id ON reviews(product_id);
CREATE INDEX idx_reviews_user_id ON reviews(user_id);
CREATE INDEX idx_reviews_rating ON reviews(rating);

通过 UNIQUE 约束，这可以确保每位用户对每件商品只能留一条评论；评分限制在 1 到 5 之间；删除用户或商品时，相关评论也会级联删除。

最佳实践

  • 始终显式定义外键，在数据库层面强制实施引用完整性，而不要仅依赖应用代码。
  • 使用 CHECK 约束定义域规则，例如价格必须为正数、状态枚举值必须有效、评分必须在指定范围内，以防止无效数据进入数据库。
  • 为所有外键列创建索引，因为这些列用于 JOIN 和查找；未建立索引的外键会在级联操作期间导致全表扫描。
  • 主键优先使用代理键，而非自然键，以避免自然值发生变化时带来问题（例如电子邮件地址或 SKU）。
  • 为所有表添加 created_at 和 updated_at 时间戳，以便审计和调试；使用数据库默认值确保一致性。
  • 明确记录反规范化决策，当出于性能考虑而偏离范式时，应清楚说明，以便后续开发者理解其中的权衡。

边界情况

  • 循环外键依赖： 当两个表相互引用时，先创建一个不带外键的表，再创建第二个表，然后使用 ALTER TABLE 添加缺失的外键。在 PostgreSQL 中使用延迟约束处理事务中的循环插入。
  • 自引用关系： 对于层级数据（例如包含子类别的类别），使用可为空的 parent_id 列引用同一张表。添加 CHECK 约束或触发器，防止某行将自身设为父级。
  • 多态关联： 当多个表需要引用一个共享实体（例如同时为帖子和产品添加评论）时，优先使用单独的外键列，并通过 CHECK 约束确保恰好一个列不为空，而不要使用通用的 entity_type + entity_id 模式，因为该模式无法强制实施引用完整性。
  • 大型文本或二进制数据： 将 BLOB 和大型文本存储在通过外键关联的单独表中，以缩小主表的行大小，避免拖慢不需要这些大型数据的查询。
  • 多租户架构： 在包含 tenant_id 列的共享表（更简单）与每个租户使用单独架构（隔离性更强）之间做出选择。将 tenant_id 添加到所有索引中，并通过行级安全策略强制实施。

评论

支持 Markdown