📖 技能介绍

name: 数据库模式设计 slug: "database-schema-design" version: "1.0.0" displayName: "数据库模式设计" summary: "为任何应用领域设计规范化的数据库模式，包括表、关系、索引和约束。" description: 为任何应用领域设计规范化的数据库模式，包括表、关系、索引和约束。 license: MIT metadata: author: AI Agent Skills Community version: 1.0.0

Database Schema Design

此技能使AI代理能够根据应用需求设计稳健、规范化的关联数据库模式。该代理分析实体，定义具有适当数据类型和约束的表，建立关系（一对一、一对多、多对多），应用规范化至3NF，为查询性能创建索引，并生成完整的SQL DDL脚本以供执行。

Workflow

收集并分析需求： 与用户访谈或解析规范文档，识别所有实体、其属性以及它们之间的关系。明确基数（1:1, 1:N, M:N）、必需字段与可选字段，以及任何特定领域的约束，如唯一邮箱、正数价格或枚举状态。在继续之前明确记录假设。
建模实体和关系： 将需求转化为逻辑数据模型。将每个实体定义为表，选择适当的主键（优先使用代理整数或UUID键以确保稳定性），并映射关系。对于一对多，在“多”端添加外键；对于多对多，创建一个连接表，其复合主键引用两个父表；对于一对一，使用共享主键或唯一外键。
应用规范化： 按照范式审查模式。确保每个非键列依赖于整个主键（2NF）且仅依赖于主键（3NF）。拆分包含传递依赖的表。仅在读操作密集的查询模式下合理地反规范化，并记录权衡。
定义约束和索引： 添加NOT NULL、UNIQUE、CHECK和DEFAULT约束以在数据库级别强制数据完整性。在外键列、WHERE子句中使用的列以及用于排序或分组的列上创建索引。考虑为多列查询模式创建复合索引。
生成SQL DDL脚本： 生成完整的CREATE TABLE语句，包含所有列、类型、约束和索引。使用IF NOT EXISTS确保幂等性。按顺序排列语句，使被引用的表在引用表之前创建。
验证并迭代： 根据原始需求审查模式。确认所有实体都被表示，所有关系都正确建模，且没有数据完整性漏洞。根据反馈进行调整。

支持的技术

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

Usage

提供您的应用领域及其数据需求的描述。包括主要实体、其属性以及它们之间的关系。代理将生成一个规范化的模式和完整的DDL。您可以请求特定数据库（例如，PostgreSQL vs. MySQL语法）或要求对模式进行修改，如添加审计列或软删除。

Examples

示例 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时间戳 用于审计和调试；使用数据库默认值确保一致性。
明确记录反规范化的决策 当你偏离范式以提升性能时，以便未来的开发者理解其中的权衡。

Edge Cases

循环外键依赖： 当两个表相互引用时，先创建一个表而不包含FK，再创建第二个表，然后ALTER TABLE添加缺失的FK。在PostgreSQL中使用延迟约束处理事务中的循环插入。
自引用关系： 对于层次结构数据（如具有子分类的类别），使用可为空的parent_id列引用同一张表。添加CHECK约束或触发器以防止某行成为自己的父级。
多态关联： 当多个表需要引用共享实体时（例如，对帖子和产品的评论），优先使用独立的FK列并配合CHECK约束确保恰好有一个非空，而不是通用的entity_type + entity_id模式，因为后者无法强制参照完整性。
大文本或二进制数据： 将BLOB和大文本存储在单独的表中并通过外键链接，以保持主表行大小较小并避免不需要这些大数据的查询变慢。
多租户模式： 决定是使用带有tenant_id列的共享表（更简单）还是为每个租户使用独立的模式（更强隔离性）。在所有索引中添加tenant_id，并通过行级安全策略强制执行。