⚡

数据库模式设计师Skill database-schema-designer

数据库模式设计技能专注于为SQL和NoSQL数据库设计健壮、可扩展的模式，提供规范化指南、索引策略、迁移模式、约束设计和性能优化，确保数据完整性、提升查询性能并创建可维护的数据模型，适用于高流量、百万记录规模场景。

架构设计 0 次安装 0 次浏览 更新于 3/21/2026

名称: 数据库模式设计师 描述: 设计健壮、可扩展的SQL和NoSQL数据库模式。提供规范化指南、索引策略、迁移模式、约束设计和性能优化。确保数据完整性、查询性能和可维护的数据模型。 许可证: MIT

数据库模式设计师

设计生产就绪的数据库模式，内置最佳实践。

快速开始

只需描述您的数据模型：

为电子商务平台设计一个模式，包括用户、产品、订单

您将获得完整的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)
);

请求中应包含：

  • 实体（用户、产品、订单）
  • 关键关系（用户有订单，订单有商品）
  • 规模提示（高流量、百万记录）
  • 数据库偏好（SQL/NoSQL）- 如果未指定，默认为SQL

触发词

触发词 示例
design schema “为用户认证设计一个模式”
database design “为多租户SaaS设计数据库”
create tables “为博客系统创建表”
schema for “为库存管理设计模式”
model data “为实时分析建模数据”
I need a database “我需要一个数据库来跟踪订单”
design NoSQL “为产品目录设计NoSQL模式”

关键术语

术语 定义
规范化 组织数据以减少冗余（1NF → 2NF → 3NF）
3NF 第三范式 - 列之间无传递依赖
OLTP 在线事务处理 - 写入重，需要规范化
OLAP 在线分析处理 - 读取重，受益于反规范化
外键（FK） 引用另一个表主键的列
索引 加速查询的数据结构（以写速减慢为代价）
访问模式 应用如何读写数据（查询、连接、筛选）
反规范化 有意复制数据以加速读取

快速参考

任务 方法 关键考虑
新模式 先规范化到3NF 域建模优于UI
SQL vs NoSQL 访问模式决定 读写比率重要
主键 INT或UUID UUID用于分布式系统
外键 始终约束 ON DELETE策略关键
索引 FK + WHERE列 列顺序重要
迁移 始终可逆 向后兼容优先

流程概述

您的数据需求
    |
    v
+-----------------------------------------------------+
| 阶段1: 分析                                         |
| * 识别实体和关系                                    |
| * 确定访问模式（读重vs写重）                        |
| * 根据需求选择SQL或NoSQL                             |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| 阶段2: 设计                                         |
| * 规范化到3NF（SQL）或嵌入/引用（NoSQL）            |
| * 定义主键和外键                                    |
| * 选择合适的数据类型                                |
| * 添加约束（UNIQUE、CHECK、NOT NULL）               |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| 阶段3: 优化                                         |
| * 计划索引策略                                      |
| * 为读重查询考虑反规范化                            |
| * 添加时间戳（created_at、updated_at）              |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| 阶段4: 迁移                                         |
| * 生成迁移脚本（up + down）                          |
| * 确保向后兼容性                                    |
| * 计划零停机部署                                    |
+-----------------------------------------------------+
    |
    v
生产就绪的模式

命令

命令 使用时机 操作
design schema for {domain} 从头开始 完整模式生成
normalize {table} 修复现有表 应用规范化规则
add indexes for {table} 性能问题 生成索引策略
migration for {change} 模式演进 创建可逆迁移
review schema 代码审查 审核现有模式

工作流： 从 design schema 开始 → 迭代使用 normalize → 使用 add indexes 优化 → 使用 migration 演进

核心原则

原则 为何重要 实现
建模领域 UI变化，领域不变 实体名称反映业务概念
数据完整性优先 数据损坏修复成本高 数据库级约束
针对访问模式优化 无法同时优化两者 OLTP：规范化，OLAP：反规范化
规划扩展性 后期修改痛苦 索引策略 + 分区计划

反模式

避免 原因 替代方案
处处使用VARCHAR(255) 浪费存储，隐藏意图 根据字段适当大小
使用FLOAT存储金钱 舍入错误 使用DECIMAL(10,2)
缺少外键约束 孤立数据 始终定义外键
外键无索引 JOIN缓慢 为所有外键建索引
将日期存储为字符串 无法比较/排序 使用DATE、TIMESTAMP类型
查询中使用SELECT * 获取不必要数据 明确列列表
不可逆迁移 无法回滚 始终编写DOWN迁移
添加NOT NULL无默认值 破坏现有行 先添加可为空，回填数据，然后约束

验证清单

设计模式后：

  • [ ] 每个表有主键
  • [ ] 所有关系有外键约束
  • [ ] 为每个FK定义ON DELETE策略
  • [ ] 所有外键有索引
  • [ ] 频繁查询列有索引
  • [ ] 合适的数据类型（如金钱用DECIMAL等）
  • [ ] 必填字段有NOT NULL
  • [ ] 需要处有UNIQUE约束
  • [ ] 验证用CHECK约束
  • [ ] 时间戳created_at和updated_at
  • [ ] 迁移脚本可逆
  • [ ] 在暂存环境用生产数据测试

<details> <summary><strong>深入探讨：规范化（SQL）</strong></summary>

范式

范式 规则 违规示例
1NF 原子值，无重复组 product_ids = '1,2,3'
2NF 1NF + 无部分依赖 order_items中的customer_name
3NF 2NF + 无传递依赖 从postal_code派生的country

第一范式（1NF）

-- 错误：列中多个值
CREATE TABLE orders (
  id INT PRIMARY KEY,
  product_ids VARCHAR(255)  -- '101,102,103'
);

-- 正确：为商品单独表
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
);

第二范式（2NF）

-- 错误：customer_name仅依赖customer_id
CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  customer_name VARCHAR(100),  -- 部分依赖！
  PRIMARY KEY (order_id, product_id)
);

-- 正确：客户数据在单独表
CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

第三范式（3NF）

-- 错误：country依赖postal_code
CREATE TABLE customers (
  id INT PRIMARY KEY,
  postal_code VARCHAR(10),
  country VARCHAR(50)  -- 传递依赖！
);

-- 正确：单独postal_codes表
CREATE TABLE postal_codes (
  code VARCHAR(10) PRIMARY KEY,
  country VARCHAR(50)
);

何时反规范化

场景 反规范化策略
读重报告 预计算聚合
昂贵JOIN 缓存派生列
分析仪表板 物化视图 
-- 为性能反规范化
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  total_amount DECIMAL(10,2),  -- 计算
  item_count INT               -- 计算
);

</details>

<details> <summary><strong>深入探讨：数据类型</strong></summary>

字符串类型

类型 用例 示例
CHAR(n) 固定长度 州代码、ISO日期
VARCHAR(n) 可变长度 姓名、邮箱
TEXT 长内容 文章、描述 
-- 良好大小
email VARCHAR(255)
phone VARCHAR(20)
country_code CHAR(2)

数字类型

类型 范围 用例
TINYINT -128 到 127 年龄、状态码
SMALLINT -32K 到 32K 数量
INT -2.1B 到 2.1B ID、计数
BIGINT 非常大 大ID、时间戳
DECIMAL(p,s) 精确精度 金钱
FLOAT/DOUBLE 近似 科学数据 
-- 始终用DECIMAL存储金钱
price DECIMAL(10, 2)  -- $99,999,999.99

-- 切勿用FLOAT存储金钱
price FLOAT  -- 舍入错误！

日期/时间类型

DATE        -- 2025-10-31
TIME        -- 14:30:00
DATETIME    -- 2025-10-31 14:30:00
TIMESTAMP   -- 自动时区转换

-- 始终以UTC存储
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

布尔

-- PostgreSQL
is_active BOOLEAN DEFAULT TRUE

-- MySQL
is_active TINYINT(1) DEFAULT 1

</details>

<details> <summary><strong>深入探讨：索引策略</strong></summary>

何时创建索引

始终索引 原因
外键 加速JOIN
WHERE子句列 加速筛选
ORDER BY列 加速排序
唯一约束 强制唯一性 
-- 外键索引
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- 查询模式索引
CREATE INDEX idx_orders_status_date ON orders(status, created_at);

索引类型

类型 最佳适用 示例
B树 范围、相等 price > 100
哈希 仅精确匹配 email = 'x@y.com'
全文 文本搜索 MATCH AGAINST
部分 行子集 WHERE is_active = true

复合索引顺序

CREATE INDEX idx_customer_status ON orders(customer_id, status);

-- 使用索引（customer_id优先）
SELECT * FROM orders WHERE customer_id = 123;
SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

-- 不使用索引（单独status）
SELECT * FROM orders WHERE status = 'pending';

规则： 选择性最强的列优先，或最常单独查询的列。

索引陷阱

陷阱 问题 解决方案
过度索引 写速慢 仅索引查询的列
错误列顺序 索引未用 匹配查询模式
缺少FK索引 JOIN缓慢 始终为外键建索引

</details>

<details> <summary><strong>深入探讨：约束</strong></summary>

主键

-- 自增（简单）
id INT AUTO_INCREMENT PRIMARY KEY

-- UUID（分布式系统）
id CHAR(36) PRIMARY KEY DEFAULT (UUID())

-- 复合（连接表）
PRIMARY KEY (student_id, course_id)

外键

FOREIGN KEY (customer_id) REFERENCES customers(id)
  ON DELETE CASCADE     -- 父删除时子删除
  ON DELETE RESTRICT    -- 有引用时阻止删除
  ON DELETE SET NULL    -- 父删除时设为NULL
  ON UPDATE CASCADE     -- 父更新时子更新
策略 适用场景
CASCADE 依赖数据（order_items）
RESTRICT 重要引用（防止意外）
SET NULL 可选关系

其他约束

-- 唯一
email VARCHAR(255) UNIQUE NOT NULL

-- 复合唯一
UNIQUE (student_id, course_id)

-- 检查
price DECIMAL(10,2) CHECK (price >= 0)
discount INT CHECK (discount BETWEEN 0 AND 100)

-- 非空
name VARCHAR(100) NOT NULL

</details>

<details> <summary><strong>深入探讨：关系模式</strong></summary>

一对多

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
);

多对多

-- 连接表
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)
);

自引用

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  manager_id INT REFERENCES employees(id)
);

多态

-- 方法1：单独FK（更强完整性）
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)
  )
);

-- 方法2：类型 + ID（灵活，完整性弱）
CREATE TABLE comments (
  id INT PRIMARY KEY,
  content TEXT NOT NULL,
  commentable_type VARCHAR(50) NOT NULL,
  commentable_id INT NOT NULL
);

</details>

<details> <summary><strong>深入探讨：NoSQL设计（MongoDB）</strong></summary>

嵌入 vs 引用

因素 嵌入 引用
访问模式 一起读取 单独读取
关系 1:少 1:多
文档大小 小 接近16MB
更新频率 很少 频繁

嵌入文档

{
  "_id": "order_123",
  "customer": {
    "id": "cust_456",
    "name": "Jane Smith",
    "email": "jane@example.com"
  },
  "items": [
    { "product_id": "prod_789", "quantity": 2, "price": 29.99 }
  ],
  "total": 109.97
}

引用文档

{
  "_id": "order_123",
  "customer_id": "cust_456",
  "item_ids": ["item_1", "item_2"],
  "total": 109.97
}

MongoDB索引

// 单字段
db.users.createIndex({ email: 1 }, { unique: true });

// 复合
db.orders.createIndex({ customer_id: 1, created_at: -1 });

// 文本搜索
db.articles.createIndex({ title: "text", content: "text" });

// 地理空间
db.stores.createIndex({ location: "2dsphere" });

</details>

<details> <summary><strong>深入探讨：迁移</strong></summary>

迁移最佳实践

实践 为何重要
始终可逆 需要回滚
向后兼容 零停机部署
模式先于数据 分离关注点
暂存环境测试 早期捕获问题

添加列（零停机）

-- 步骤1：添加可为空列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 步骤2：部署写新列代码

-- 步骤3：回填现有行
UPDATE users SET phone = '' WHERE phone IS NULL;

-- 步骤4：设为必填（如需要）
ALTER TABLE users MODIFY phone VARCHAR(20) NOT NULL;

重命名列（零停机）

-- 步骤1：添加新列
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);

-- 步骤2：复制数据
UPDATE users SET email_address = email;

-- 步骤3：部署读新列代码
-- 步骤4：部署写新列代码

-- 步骤5：删除旧列
ALTER TABLE users DROP COLUMN email;

迁移模板

-- 迁移：YYYYMMDDHHMMSS_描述.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;

</details>

<details> <summary><strong>深入探讨：性能优化</strong></summary>

查询分析

EXPLAIN SELECT * FROM orders
WHERE customer_id = 123 AND status = 'pending';
查看项 含义
type: ALL 全表扫描（差）
type: ref 使用索引（好）
key: NULL 未用索引
rows: high 扫描多行

N+1查询问题

# 错误：N+1查询
orders = db.query("SELECT * FROM orders")
for order in orders:
    customer = db.query(f"SELECT * FROM customers WHERE id = {order.customer_id}")

# 正确：单JOIN
results = db.query("""
    SELECT orders.*, customers.name
    FROM orders
    JOIN customers ON orders.customer_id = customers.id
""")

优化技术

技术 适用场景
添加索引 WHERE/ORDER BY慢
反规范化 昂贵JOIN
分页 大结果集
缓存 重复查询
读副本 读重负载
分区 非常大表

</details>

扩展点

  1. 数据库特定模式： 添加MySQL vs PostgreSQL vs SQLite变体
  2. 高级模式： 时间序列、事件溯源、CQRS、多租户
  3. ORM集成： TypeORM、Prisma、SQLAlchemy模式
  4. 监控： 查询性能跟踪、慢查询警报