database-schema-designer

数据库模式设计器

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

快速开始

只需描述你的数据模型：

为电商平台设计模式，包含用户、商品、订单

你将获得完整的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策略至关重要
索引	外键 + WHERE列	列顺序很重要
迁移	始终可逆	首先向后兼容

流程概述

你的数据需求
    |
    v
+-----------------------------------------------------+
| 阶段1：分析                                         |
| * 识别实体和关系                                     |
| * 确定访问模式（读密集型还是写密集型）               |
| * 根据需求选择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而不设默认值	破坏现有行	先允许NULL，回填数据，再添加约束

验证清单

设计模式后：

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

范式

范式	规则	违反示例
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>

字符串类型

类型	用途	示例
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>

何时创建索引

始终索引	原因
外键	加速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-Tree	范围、等值	`price > 100`
Hash	仅精确匹配	`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';

规则： 选择性最高的列在前，或单独查询最多的列在前。

索引陷阱

陷阱	问题	解决方案
过度索引	写入慢	只索引被查询的列
列顺序错误	索引未使用	匹配查询模式
缺少外键索引	JOIN慢	始终索引外键

</details>

主键

-- 自增（简单）
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>

一对多

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：单独外键（完整性更强）
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>深入探讨：NoSQL设计（MongoDB）</summary>

嵌入 vs 引用

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

嵌入文档

{
  "_id": "order_123",
  "customer": {
    "id": "cust_456",
    "name": "张三",
    "email": "zhangsan@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>

迁移最佳实践

实践	原因
始终可逆	需要回滚
向后兼容	零停机部署
先模式后数据	分离关注点
在预发布环境测试	及早发现问题

添加列（零停机）

-- 步骤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_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;

</details>

查询分析

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>

扩展点

数据库特定模式： 添加MySQL vs PostgreSQL vs SQLite的差异
高级模式： 时序、事件溯源、CQRS、多租户
ORM集成： TypeORM、Prisma、SQLAlchemy模式
监控： 查询性能跟踪、慢查询告警

你可能还喜欢

golang-database

2.3Kdatabase

Go数据库访问综合指南——参数化查询、结构体扫描、可空列、事务、隔离级别、SELECT FOR UPDATE、连接池、批量处理、上下文传播及迁移工具。适用于编写、审查或调试与PostgreSQL、MariaDB、MySQL或SQLite交互的Golang代码；用于数据库测试；或关于database/sql、sqlx、pgx的问题。不生成数据库模式或迁移SQL。

samber

获取

plan-orchestrate

237Kdatabase

Read a plan document, decompose it into steps, design a per-step agent chain from the ECC catalogue, and emit ready-to-paste /orchestrate custom prompts. Generative only — never invokes /orchestrate itself. Use when the user has a multi-step plan and wants to drive it through orchestrate without composing chains by hand.

affaan-m

获取

project-overview

81Kdatabase

LobeHub open-source monorepo architecture map. Use when locating code layers, understanding apps/packages/src layout, business stubs, project structure, or onboarding to the repository.

lobehub

获取

继续探索

浏览 database 热门 Skills 最近更新