🚀 快速安装

复制以下命令并运行，立即安装此 Skill：

npx @anthropic-ai/skills install supercent-io/skills-template/database-schema-design

💡 提示：需要 Node.js 和 NPM

数据库模式设计

何时使用此技能

列出应触发此技能的具体情况：

新项目：为新应用程序设计数据库模式
模式重构：为性能或可扩展性重新设计现有模式
关系定义：在表之间实现 1:1、1:N、N:M 关系
迁移：安全地应用模式变更
性能问题：索引和模式优化以解决慢查询

输入格式

需要从用户收集的必需和可选输入信息：

必需信息

数据库类型：PostgreSQL、MySQL、MongoDB、SQLite 等
领域描述：将存储什么数据（例如，电子商务、博客、社交媒体）
关键实体：核心数据对象（例如，用户、产品、订单）

可选信息

预期数据量：小（<1万行）、中（1万-100万）、大（>100万）（默认：中）
读写比例：读密集、写密集、均衡（默认：均衡）
事务要求：是否需要 ACID（默认：是）
分片/分区：是否需要大数据分布（默认：否）

输入示例

为电子商务平台设计一个数据库：
- 数据库：PostgreSQL
- 实体：用户、产品、订单、评论
- 关系：
  - 一个用户可以有多个订单
  - 一个订单包含多个产品（多对多）
  - 评论关联到一个用户和一个产品
- 预期数据：10万用户，1万产品
- 读密集（频繁的产品查找）

指示

指定要精确遵循的逐步任务序列。

步骤 1：定义实体和属性

识别核心数据对象及其属性。

任务：

从业务需求中提取名词 → 实体
列出每个实体的属性（列）
确定数据类型（VARCHAR、INTEGER、TIMESTAMP、JSON 等）
指定主键（UUID 与自增 ID）

示例（电子商务）：

用户表
- id：UUID 主键
- email：VARCHAR(255) 唯一 非空
- username：VARCHAR(50) 唯一 非空
- password_hash：VARCHAR(255) 非空
- created_at：时间戳 默认 当前时间
- updated_at：时间戳 默认 当前时间

产品表
- id：UUID 主键
- name：VARCHAR(255) 非空
- description：文本
- price：十进制(10， 2) 非空
- stock：整数 默认 0
- category_id：UUID 外键 引用 分类表(id)
- created_at：时间戳 默认 当前时间

订单表
- id：UUID 主键
- user_id：UUID 外键 引用 用户表(id)
- total_amount：十进制(10， 2) 非空
- status：VARCHAR(20) 默认 'pending'
- created_at：时间戳 默认 当前时间

订单项表（关联表）
- id：UUID 主键
- order_id：UUID 外键 引用 订单表(id) 级联删除
- product_id：UUID 外键 引用 产品表(id)
- quantity：整数 非空
- price：十进制(10， 2) 非空

步骤 2：设计关系和规范化

定义表之间的关系并应用规范化。

任务：

1:1 关系：外键 + 唯一约束
1:N 关系：外键
N:M 关系：创建关联表
确定规范化级别（第一范式 ~ 第三范式）

决策标准：

OLTP 系统 → 规范化到第三范式（数据完整性）
OLAP/分析系统 → 允许反规范化（查询性能）
读密集 → 通过部分反规范化最小化 JOIN 操作
写密集 → 完全规范化以消除冗余

示例（实体关系图 Mermaid）：

erDiagram
    用户表 ||--o{ 订单表 : 下达
    订单表 ||--|{ 订单项表 : 包含
    产品表 ||--o{ 订单项表 : "被订购于"
    分类表 ||--o{ 产品表 : 分类
    用户表 ||--o{ 评论表 : 撰写
    产品表 ||--o{ 评论表 : "被评论"

    用户表 {
        uuid id 主键
        string email 唯一键
        string username 唯一键
        string password_hash
        timestamp created_at
    }

    产品表 {
        uuid id 主键
        string name
        decimal price
        int stock
        uuid category_id 外键
    }

    订单表 {
        uuid id 主键
        uuid user_id 外键
        decimal total_amount
        string status
        timestamp created_at
    }

    订单项表 {
        uuid id 主键
        uuid order_id 外键
        uuid product_id 外键
        int quantity
        decimal price
    }

步骤 3：建立索引策略

为查询性能设计索引。

任务：

主键自动创建索引
在 WHERE 子句中频繁使用的列 → 添加索引
在 JOIN 中使用的外键 → 添加索引
考虑复合索引（WHERE 列1 = ? AND 列2 = ?）
唯一索引（email、username 等）

检查清单：

对频繁查询的列建立索引
对外键列建立索引
复合索引顺序优化（高选择性列在前）
避免过多索引（会降低 INSERT/UPDATE 性能）

示例（PostgreSQL）：

-- 主键（自动索引）
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,  -- 唯一 = 自动索引
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 外键 + 显式索引
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- 复合索引（status 和 created_at 经常一起查询）
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

-- 产品表
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    category_id UUID REFERENCES categories(id),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_price ON products(price);  -- 价格范围搜索
CREATE INDEX idx_products_name ON products(name);    -- 产品名称搜索

-- 全文搜索（PostgreSQL）
CREATE INDEX idx_products_name_fts ON products USING GIN(to_tsvector('english', name));
CREATE INDEX idx_products_description_fts ON products USING GIN(to_tsvector('english', description));

步骤 4：设置约束和触发器

添加约束以确保数据完整性。

任务：

非空：必需的列
唯一：必须唯一的列
检查：值范围约束（例如，价格 >= 0）
外键 + 级联选项
设置默认值

示例：

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    discount_percent INTEGER CHECK (discount_percent >= 0 AND discount_percent <= 100),
    category_id UUID REFERENCES categories(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 触发器：自动更新 updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

步骤 5：编写迁移脚本

编写能安全应用模式变更的迁移脚本。

任务：

向上迁移：应用变更
向下迁移：回滚
包裹在事务中
防止数据丢失（小心使用 ALTER TABLE）

示例（SQL 迁移）：

-- migrations/001_create_initial_schema.up.sql
BEGIN;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) UNIQUE NOT NULL,
    parent_id UUID REFERENCES categories(id)
);

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    category_id UUID REFERENCES categories(id),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_price ON products(price);

COMMIT;

-- migrations/001_create_initial_schema.down.sql
BEGIN;

DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS categories CASCADE;
DROP TABLE IF EXISTS users CASCADE;

COMMIT;

输出格式

定义可交付成果应遵循的确切格式。

基本结构

项目目录/
├── database/
│   ├── schema.sql                    # 完整模式
│   ├── migrations/
│   │   ├── 001_create_users.up.sql
│   │   ├── 001_create_users.down.sql
│   │   ├── 002_create_products.up.sql
│   │   └── 002_create_products.down.sql
│   ├── seeds/
│   │   └── sample_data.sql           # 测试数据
│   └── docs/
│       ├── ERD.md                     # Mermaid 实体关系图
│       └── SCHEMA.md                  # 模式文档
└── README.md

实体关系图（Mermaid 格式）

# 数据库模式

## 实体关系图

\`\`\`mermaid
erDiagram
    用户表 ||--o{ 订单表 : 下达
    订单表 ||--|{ 订单项表 : 包含
    产品表 ||--o{ 订单项表 : "被订购于"

    用户表 {
        uuid id 主键
        string email 唯一键
        string username 唯一键
    }

    产品表 {
        uuid id 主键
        string name
        decimal price
    }
\`\`\`

## 表描述

### 用户表
- **目的**：存储用户账户信息
- **索引**：email， username
- **预估行数**：100，000

### 产品表
- **目的**：产品目录
- **索引**：category_id， price, name
- **预估行数**：10，000

约束条件

指定强制性规则和禁止行为。

强制性规则（必须遵守）

必须定义主键：每个表都必须定义一个主键
- 确保记录唯一标识
- 保证引用完整性
显式定义外键：有关系的表必须定义外键
- 指定 ON DELETE CASCADE/SET NULL 选项
- 防止出现孤记录
适当使用非空约束：必需的列必须设置为 NOT NULL
- 明确指定可空列与非空列
- 推荐提供默认值

禁止行为（不得违反）

避免滥用实体-属性-值模式：仅在特殊情况下使用实体-属性-值模式
- 查询复杂度会急剧增加
- 导致性能下降
避免过度反规范化：为性能进行反规范化时要谨慎
- 可能导致数据一致性问题
- 存在更新异常风险
严禁明文存储敏感数据：切勿以明文形式存储密码、卡号等
- 必须进行哈希/加密
- 存在法律风险

安全规则

最小权限原则：仅授予应用数据库账户必要的权限
防止 SQL 注入：使用预编译语句/参数化查询
加密敏感列：考虑对静态存储的个人身份信息进行加密

示例

通过真实用例演示如何应用此技能。

示例 1：博客平台模式

场景：为类似 Medium 的博客平台设计数据库

用户请求：

为博客平台设计一个 PostgreSQL 模式：
- 用户可以撰写多篇文章
- 文章可以有多个标签（多对多）
- 用户可以点赞和收藏文章
- 评论功能（支持嵌套回复）

最终结果：

-- 用户表
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    bio TEXT,
    avatar_url VARCHAR(500),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 文章表
CREATE TABLE posts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    content TEXT NOT NULL,
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_posts_published ON posts(published_at);
CREATE INDEX idx_posts_slug ON posts(slug);

-- 标签表
CREATE TABLE tags (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL
);

-- 文章-标签关系（多对多）
CREATE TABLE post_tags (
    post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
    tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

CREATE INDEX idx_post_tags_post ON post_tags(post_id);
CREATE INDEX idx_post_tags_tag ON post_tags(tag_id);

-- 点赞表
CREATE TABLE post_likes (
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (user_id, post_id)
);

-- 收藏表
CREATE TABLE post_bookmarks (
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (user_id, post_id)
);

-- 评论表（自引用以支持嵌套回复）
CREATE TABLE comments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    parent_comment_id UUID REFERENCES comments(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_comments_post ON comments(post_id);
CREATE INDEX idx_comments_author ON comments(author_id);
CREATE INDEX idx_comments_parent ON comments(parent_comment_id);

示例 2：MongoDB 模式（NoSQL）

场景：为实时聊天应用设计 MongoDB 模式

用户请求：

为实时聊天应用设计 MongoDB 模式。
读操作非常频繁，需要快速检索消息历史。

最终结果：

// 用户集合
{
  _id: ObjectId,
  username: String,  // 已索引，唯一
  email: String,     // 已索引，唯一
  avatar_url: String,
  status: String,    // '在线', '离线', '离开'
  last_seen: Date,
  created_at: Date
}

// 会话集合（反规范化 - 读优化）
{
  _id: ObjectId,
  participants: [    // 已索引
    {
      user_id: ObjectId,
      username: String,
      avatar_url: String
    }
  ],
  last_message: {    // 反规范化，用于快速检索最近消息
    content: String,
    sender_id: ObjectId,
    sent_at: Date
  },
  unread_counts: {   // 每个参与者的未读消息计数
    "user_id_1": 5,
    "user_id_2": 0
  },
  created_at: Date,
  updated_at: Date
}

// 消息集合
{
  _id: ObjectId,
  conversation_id: ObjectId,  // 已索引
  sender_id: ObjectId,
  content: String,
  attachments: [
    {
      type: String,  // '图片', '文件', '视频'
      url: String,
      filename: String
    }
  ],
  read_by: [ObjectId],  // 已读此消息的用户ID数组
  sent_at: Date,        // 已索引
  edited_at: Date
}

// 索引
db.users.createIndex({ username: 1 }, { unique: true });
db.users.createIndex({ email: 1 }, { unique: true });

db.conversations.createIndex({ "participants.user_id": 1 });
db.conversations.createIndex({ updated_at: -1 });

db.messages.createIndex({ conversation_id: 1, sent_at: -1 });
db.messages.createIndex({ sender_id: 1 });

设计亮点：

为读优化进行反规范化（嵌入 last_message）
对频繁访问的字段建立索引
使用数组字段（participants、read_by）

最佳实践

质量提升

命名约定一致性：对表/列名使用蛇形命名法
- users， post_tags, created_at
- 保持复数/单数的一致性（表名复数，列名单数等）
考虑软删除：对重要数据使用逻辑删除而非物理删除
- deleted_at 时间戳（NULL = 活跃，NOT NULL = 已删除）
- 允许恢复意外删除的数据
- 提供审计跟踪
时间戳必需：在大多数表中包含 created_at 和 updated_at
- 数据跟踪和调试
- 时间序列分析

效率提升

部分索引：使用条件索引减小索引大小

CREATE INDEX idx_posts_published ON posts(published_at) WHERE published_at IS NOT NULL;

物化视图：将复杂的聚合查询缓存为物化视图
分区：按日期/范围对大型表进行分区

常见问题

问题 1：N+1 查询问题

症状：多次数据库调用，而单个查询即可满足

原因：在循环中未使用 JOIN 进行单独查询

解决方案：

-- ❌ 反面示例：N+1 查询
SELECT * FROM posts;  -- 1 次
-- 对每篇文章
SELECT * FROM users WHERE id = ?;  -- N 次

-- ✅ 良好示例：1 次查询
SELECT posts.*, users.username, users.avatar_url
FROM posts
JOIN users ON posts.author_id = users.id;

问题 2：由于外键未索引导致的慢 JOIN

症状：JOIN 查询非常慢

原因：外键列缺少索引

解决方案：

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

问题 3：UUID 与自增性能

症状：使用 UUID 主键时插入性能下降

原因：UUID 是随机的，导致索引碎片

解决方案：

PostgreSQL：使用 uuid_generate_v7()（时间有序 UUID）
MySQL：使用 UUID_TO_BIN(UUID()， 1)
或者考虑使用自增 BIGINT

参考资料

官方文档

工具

dbdiagram.io – 实体关系图创建
PgModeler – PostgreSQL 建模工具
Prisma – ORM + 迁移

学习资源

数据库设计课程（freecodecamp）
Use The Index， Luke – SQL 索引指南

元数据

版本

当前版本：1.0.0
最后更新：2025-01-01
兼容平台：Claude, ChatGPT, Gemini

📄 原始文档

完整文档（英文）：

https://skills.sh/supercent-io/skills-template/database-schema-design

💡 提示：点击上方链接查看 skills.sh 原始英文文档，方便对照翻译。

声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。

Database Schema Design

🚀 快速安装

数据库模式设计

何时使用此技能

输入格式

必需信息

可选信息

输入示例

指示

步骤 1：定义实体和属性

步骤 2：设计关系和规范化

步骤 3：建立索引策略

步骤 4：设置约束和触发器

步骤 5：编写迁移脚本

输出格式

基本结构

实体关系图（Mermaid 格式）

约束条件

强制性规则（必须遵守）

禁止行为（不得违反）

安全规则

示例

示例 1：博客平台模式

示例 2：MongoDB 模式（NoSQL）

最佳实践

质量提升

效率提升

常见问题

问题 1：N+1 查询问题

问题 2：由于外键未索引导致的慢 JOIN

问题 3：UUID 与自增性能

参考资料

官方文档

工具

学习资源

元数据

版本

相关技能

标签

📄 原始文档

评论(0)

提示：请文明发言 取消回复

相关文章

Technical Writing

launch-marketing – 汇易宝

Monitoring & Observability

AI Agent Configuration Policy (Configuration & Security)

近期文章

近期评论

提示：请文明发言取消回复