一份面向技术团队的完整指南
数据模型设计是软件工程的核心环节之一，它定义了系统中数据的组织方式、存储结构、访问模式以及数据之间的关系。一个优秀的数据模型不仅要满足当前业务需求，还要具备良好的扩展性和可维护性，能够适应业务的演进和变化。
数据模型是业务逻辑与技术实现之间的桥梁。它将抽象的业务概念转化为具体的数据结构，使得：
数据是企业的核心资产。在数字化转型时代，数据的价值日益凸显，而数据模型直接决定了数据的质量、可用性和价值挖掘的难易程度。
| 维度 | 良好设计 | 糟糕设计 |
|---|---|---|
| 性能 | 查询高效、响应迅速 | 全表扫描、索引失效 |
| 一致性 | 数据准确、无冗余 | 数据冲突、更新异常 |
| 可维护性 | 结构清晰、易于理解 | 耦合严重、难以修改 |
| 扩展性 | 平滑演进、支持增长 | 重构成本高、风险大 |
| 成本 | 资源利用率高 | 存储浪费、计算低效 |
数据模型设计中的问题具有累积效应和放大效应：
💡 经验法则：在数据模型上投入的设计时间，会在系统的整个生命周期中获得 10 倍以上的回报。
对数据进行科学分类是设计工作的第一步。不同维度的分类帮助我们理解数据的特性，从而做出合理的设计决策。
主数据是描述业务核心实体的数据，具有高稳定性、高共享性的特点。
典型主数据类型：
设计要点：
-- 客户主表设计示例
CREATE TABLE customers (
customer_id VARCHAR(32) PRIMARY KEY, -- 业务主键
customer_code VARCHAR(50) UNIQUE, -- 客户编码（对外）
customer_name VARCHAR(200) NOT NULL,
customer_type TINYINT COMMENT '1-企业 2-个人',
industry_code VARCHAR(20), -- 所属行业
credit_level TINYINT DEFAULT 3, -- 信用等级 1-5
status TINYINT DEFAULT 1, -- 状态
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,
version INT DEFAULT 1, -- 乐观锁版本号
INDEX idx_industry (industry_code),
INDEX idx_credit (credit_level)
) ENGINE=InnoDB COMMENT='客户主数据表';
交易数据记录业务活动的发生，具有高频率、大容量的特点。
典型交易数据：
设计要点：
-- 订单表设计（含分表策略支持）
CREATE TABLE orders_${date_suffix} (
order_id BIGINT UNSIGNED PRIMARY KEY,
order_no VARCHAR(64) UNIQUE NOT NULL,
customer_id VARCHAR(32) NOT NULL,
order_amount DECIMAL(16, 2) NOT NULL,
order_status TINYINT NOT NULL COMMENT '10-待支付 20-已支付...',
created_at DATETIME NOT NULL,
completed_at DATETIME,
-- 分表键：通常选择时间或用户ID
partition_key INT AS (YEAR(created_at) * 100 + MONTH(created_at)),
INDEX idx_customer (customer_id, created_at),
INDEX idx_status_created (order_status, created_at),
INDEX idx_order_no (order_no)
) ENGINE=InnoDB COMMENT='订单交易表';
配置数据定义系统的运行参数和业务规则。
典型配置数据：
设计建议：
分析数据面向报表和决策支持，通常从业务数据加工而来。
特点：
具有预定义的数据模型，存储在关系型数据库中。
特征：
✓ 严格的数据类型约束
✓ 支持复杂的关系和约束
✓ 强大的事务支持
✓ 成熟的查询优化器
适用场景：金融交易、ERP系统、核心业务系统
具有一定结构但模式灵活的数据，如JSON、XML。
// 半结构化数据示例：产品属性
{
"product_id": "P001",
"name": "智能手机",
"attributes": {
"color": ["黑", "白", "蓝"],
"storage": ["128GB", "256GB"],
"screen": {
"size": "6.1英寸",
"resolution": "2532×1170"
}
},
"specs": {
"weight": "174g",
"dimensions": "146.7×71.5×7.65mm"
}
}
数据库选型：MongoDB、PostgreSQL（JSONB）、Elasticsearch
没有预定义结构的数据，如图片、视频、文档。
管理策略：
-- 文件元数据表
CREATE TABLE file_metadata (
file_id VARCHAR(32) PRIMARY KEY,
file_name VARCHAR(500),
file_size BIGINT,
mime_type VARCHAR(100),
storage_path VARCHAR(500), -- 对象存储路径
md5_hash VARCHAR(32), -- 完整性校验
metadata JSON, -- 扩展元数据（EXIF等）
created_at DATETIME
);
生命周期极短，通常仅在单次请求或会话中存在。
设计原则：
支撑日常业务运营的核心数据，需要持久化存储。
设计原则：
超过活跃期的数据，访问频率低但需长期保留。
管理策略：
| 策略 | 适用场景 | 实现方式 |
|---|---|---|
| 归档 | 合规要求 | 迁移到低成本存储 |
| 压缩 | 空间优化 | 列式压缩存储 |
| 汇总 | 趋势分析 | 预计算聚合结果 |
| 删除 | 隐私合规 | 按策略定期清理 |
┌─────────────────────────────────────────────────────────┐
│ 数据生命周期 │
├─────────────┬─────────────┬─────────────┬───────────────┤
│ 产生期 │ 活跃期 │ 衰退期 │ 归档期 │
├─────────────┼─────────────┼─────────────┼───────────────┤
│ • 数据写入 │ • 频繁读写 │ • 读多写少 │ • 极少访问 │
│ • 实时校验 │ • 事务保障 │ • 性能优化 │ • 长期保存 │
│ • 索引构建 │ • 缓存加速 │ • 分区裁剪 │ • 合规审计 │
├─────────────┼─────────────┼─────────────┼───────────────┤
│ 存储: SSD │ 存储: SSD │ 存储: HDD │ 存储: 冷存 │
│ 库: 主库 │ 库: 主/从 │ 库: 历史库 │ 格式: Parquet │
└─────────────┴─────────────┴─────────────┴───────────────┘
数据建模通常采用三层架构，从抽象到具体逐步细化：
┌─────────────────────────────────────────────┐
│ 概念模型 (Conceptual) │
│ 业务视角的实体和关系 │
│ ↓ 业务确认 │
├─────────────────────────────────────────────┤
│ 逻辑模型 (Logical) │
│ 技术无关的结构定义 │
│ ↓ 技术选型 │
├─────────────────────────────────────────────┤
│ 物理模型 (Physical) │
│ 具体数据库的实现 │
└─────────────────────────────────────────────┘
目标：捕获业务需求，建立领域共识
核心元素：
示例：电商领域概念模型
┌─────────┐ ┌─────────┐ ┌─────────┐
│ 客户 │◄─────►│ 订单 │◄─────►│ 商品 │
└─────────┘ 1:n └─────────┘ n:m └─────────┘
│
│ 1:n
▼
┌─────────┐
│ 订单项 │
└─────────┘
建模工具：ER/Studio, PowerDesigner, Draw.io
目标：定义数据结构，不考虑具体数据库实现
核心工作：
示例：订单逻辑模型
订单表 (orders)
├── order_id: BIGINT PK
├── customer_id: VARCHAR(32) FK → customers
├── order_no: VARCHAR(64) UK
├── order_status: TINYINT
├── total_amount: DECIMAL(16,2)
├── created_at: TIMESTAMP
└── updated_at: TIMESTAMP
订单项表 (order_items)
├── item_id: BIGINT PK
├── order_id: BIGINT FK → orders
├── product_id: VARCHAR(32) FK → products
├── quantity: INT
├── unit_price: DECIMAL(16,2)
└── subtotal: DECIMAL(16,2)
目标：针对特定数据库的优化实现
关键决策：
ER模型是最经典的数据建模方法，由Peter Chen于1976年提出。
┌────────────────────────────────────────────────────┐
│ ER图符号 │
├────────────────────────────────────────────────────┤
│ ┌─────┐ ╔═════╗ ┌─────────────────┐ │
│ │实体 │ ║强实体║ │ 属性 │ │
│ └─────┘ ╚═════╝ └─────────────────┘ │
│ │
│ ◇────────── 关系 ──────────◇ │
│ (1,1) (0,n) │
│ │
│ 基数表示: 1:1 一对─对, 1:n 一对多, n:m 多对多 │
└────────────────────────────────────────────────────┘
| 关系类型 | 转换策略 | 示例 |
|---|---|---|
| 1:1 | 合并为单表，或在任意一方加外键 | 用户-用户详情 |
| 1:n | 在多方加外键 | 部门-员工 |
| n:m | 创建关联表 | 学生-课程 |
多对多关系处理示例：
-- 原始多对多：商品与标签
-- 解决方案：引入关联表
CREATE TABLE products (
product_id VARCHAR(32) PRIMARY KEY,
product_name VARCHAR(200)
);
CREATE TABLE tags (
tag_id VARCHAR(32) PRIMARY KEY,
tag_name VARCHAR(100)
);
-- 关联表（解决多对多）
CREATE TABLE product_tags (
product_id VARCHAR(32),
tag_id VARCHAR(32),
tagged_at DATETIME DEFAULT CURRENT_TIMESTAMP,
tagged_by VARCHAR(32),
PRIMARY KEY (product_id, tag_id),
FOREIGN KEY (product_id) REFERENCES products(product_id),
FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
);
维度建模由Ralph Kimball推广，是数据仓库领域的标准方法。
事实表（Fact Table）：存储业务过程的度量数据
维度表（Dimension Table）：存储业务实体的描述性属性
星型模型 (Star Schema):
┌─────────┐
│ 日期维 │
└────┬────┘
│
┌─────────┐ ┌────┴────┐ ┌─────────┐
│ 产品维 │────►│ 事实表 │◄────│ 地区维 │
└─────────┘ └────┬────┘ └─────────┘
│
┌────┴────┐
│ 客户维 │
└─────────┘
雪花模型 (Snowflake Schema):
维度表进一步规范化
┌─────────┐
│ 日期维 │
└────┬────┘
┌────┴────┐
│ 月份维 │
└────┬────┘
│
┌─────────┐ ┌────┴────┐
│ 品牌维 │ │ 事实表 │
└────┬────┘ └─────────┘
┌────┴────┐
│ 产品维 │
└─────────┘
选型建议：
-- 销售事实表
CREATE TABLE fact_sales (
-- 维度外键
date_key INT NOT NULL,
product_key INT NOT NULL,
customer_key INT NOT NULL,
store_key INT NOT NULL,
-- 退化维度（直接存储而不建维表）
order_number VARCHAR(64),
-- 度量值
quantity INT,
unit_price DECIMAL(12, 2),
discount_amount DECIMAL(12, 2),
sales_amount DECIMAL(12, 2),
cost_amount DECIMAL(12, 2),
-- 派生度量
gross_profit AS (sales_amount - cost_amount),
PRIMARY KEY (date_key, product_key, customer_key, order_number),
FOREIGN KEY (date_key) REFERENCES dim_date(date_key),
FOREIGN KEY (product_key) REFERENCES dim_product(product_key),
FOREIGN KEY (customer_key) REFERENCES dim_customer(customer_key),
FOREIGN KEY (store_key) REFERENCES dim_store(store_key)
);
维度数据会随时间变化，需要不同的处理策略：
| 类型 | 名称 | 处理方式 | 适用场景 |
|---|---|---|---|
| SCD Type 0 | 固定维度 | 不允许变化 | 出生日期等永不改变的数据 |
| SCD Type 1 | 直接覆盖 | 旧值被新值覆盖 | 错误修正，不需要历史 |
| SCD Type 2 | 增加版本 | 新增记录，保留历史 | 需要完整历史轨迹 |
| SCD Type 3 | 增加字段 | 增加新旧值字段 | 只需要最近的历史 |
| SCD Type 4 | 历史表 | 当前表+历史表 | 快速查询当前，详细分析历史 |
SCD Type 2 实现示例：
CREATE TABLE dim_customer (
customer_key INT AUTO_INCREMENT PRIMARY KEY, -- 代理键
customer_id VARCHAR(32), -- 自然键
customer_name VARCHAR(100),
customer_type VARCHAR(20),
-- SCD Type 2 字段
effective_date DATE,
expiry_date DATE,
is_current BOOLEAN DEFAULT TRUE,
-- 审计字段
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uk_customer_version (customer_id, effective_date)
);
-- 插入新版本时
UPDATE dim_customer
SET expiry_date = CURDATE() - INTERVAL 1 DAY,
is_current = FALSE
WHERE customer_id = 'C001' AND is_current = TRUE;
INSERT INTO dim_customer (customer_id, customer_name, customer_type, effective_date, is_current)
VALUES ('C001', '新名称', 'VIP', CURDATE(), TRUE);
NoSQL数据库的兴起带来了文档模型，特别适合灵活的结构和海量数据场景。
优势：
劣势：
文档模型设计的核心决策：何时嵌入，何时引用。
嵌入（Embedding）：
// 订单文档包含订单项
{
"_id": "order_001",
"customer_id": "cust_123",
"items": [
{
"product_id": "prod_456",
"product_name": "iPhone 15",
"quantity": 2,
"price": 5999
},
{
"product_id": "prod_789",
"product_name": "AirPods Pro",
"quantity": 1,
"price": 1899
}
],
"total_amount": 13897
}
适用嵌入的场景：
引用（Referencing）：
// 订单文档引用客户
{
"_id": "order_001",
"customer_id": "cust_123", // 引用
"items": [...],
"total_amount": 13897
}
// 客户文档独立存储
{
"_id": "cust_123",
"name": "张三",
"email": "zhangsan@example.com"
}
适用引用的场景：
┌───────────────────────────────────────────────────────────┐
│ 文档模型设计检查清单 │
├───────────────────────────────────────────────────────────┤
│ □ 根据访问模式设计，而非数据关系 │
│ □ 优先嵌入，必要时引用 │
│ □ 避免过大的数组（考虑子文档或拆分） │
│ □ 预留扩展字段（如 metadata: {}） │
│ □ 设计合适的分片键（sharding key） │
│ □ 考虑TTL索引管理过期数据 │
│ □ 建立适当的复合索引 │
└───────────────────────────────────────────────────────────┘
适用于高度互联的数据，如社交网络、推荐系统、知识图谱。
(用户A) --[:关注]--> (用户B)
│ │
[:点赞] [:发布]
▼ ▼
(帖子1) (帖子2)
适用数据库：Neo4j、Amazon Neptune、JanusGraph
最简单的数据模型，适合高速缓存和会话存储。
Key Value
────────────────────────────────────────
session:abc123 → {user_id: 123, ...}
user:123:profile → {name: "张三", ...}
cache:product:456 → {price: 99.9, ...}
适用数据库：Redis、Riak、Amazon DynamoDB
适合海量数据的时间序列存储。
Row Key │ Column Family: metrics
│─────────┬─────────┬─────────
│ cpu │ memory │ disk
device_001:t1 │ 45% │ 60% │ 30%
device_001:t2 │ 50% │ 65% │ 31%
device_002:t1 │ 30% │ 40% │ 25%
适用数据库：Apache Cassandra、HBase、ScyllaDB
核心思想：每个知识点在系统中应该有单一、明确、权威的表示。
反例：重复存储可计算字段
-- 糟糕的设计：订单表中同时存储金额和折扣后的金额
CREATE TABLE orders_bad (
order_id BIGINT PRIMARY KEY,
amount DECIMAL(16,2),
discount_rate DECIMAL(4,2),
discount_amount DECIMAL(16,2), -- 冗余，可由 amount * discount_rate 计算
final_amount DECIMAL(16,2) -- 冗余，可由 amount - discount_amount 计算
);
正例：只存储原始数据，计算在查询时或应用层完成
-- 好的设计
CREATE TABLE orders_good (
order_id BIGINT PRIMARY KEY,
amount DECIMAL(16,2) NOT NULL,
discount_rate DECIMAL(4,2) DEFAULT 0,
-- 使用生成列（MySQL 5.7+）
discount_amount DECIMAL(16,2) AS (amount * discount_rate) STORED,
final_amount DECIMAL(16,2) AS (amount - amount * discount_rate) STORED
);
虽然DRY是原则，但在数据模型中有时需要有意的冗余来优化性能：
| 冗余类型 | 目的 | 维护方式 |
|---|---|---|
| 反规范化 | 减少JOIN，提升查询性能 | 触发器或应用层同步 |
| 聚合字段 | 避免实时计算 | 定时任务更新 |
| 缓存数据 | 加速热点查询 | TTL过期或主动失效 |
决策框架：
是否需要冗余？
↓
写入频率 vs 读取频率？
↓
如果读 >> 写 → 考虑冗余
↓
冗余数据变化频率？
↓
如果变化频繁 → 使用触发器/CDC同步
如果变化稀少 → 应用层维护即可
核心思想：系统各组件应相互独立，修改一个不应影响其他。
独立性原则：
实现方式：
# 糟糕：直接依赖表结构
class OrderService:
def get_order(self, order_id):
sql = "SELECT * FROM orders WHERE id = %s AND deleted = 0"
return db.execute(sql, order_id)
# 好的：通过Repository模式隔离
class OrderRepository:
def find_by_id(self, order_id: str) -> Optional[Order]:
# 内部实现可以更改，不影响调用方
return self._mapper.to_entity(
self._db.query(OrderTable).filter_by(id=order_id, deleted=False).first()
)
将不同类型的数据存储在合适的存储介质中：
┌─────────────────────────────────────────────────────────┐
│ 数据分离策略 │
├─────────────────┬─────────────────┬─────────────────────┤
│ 数据类型 │ 存储介质 │ 理由 │
├─────────────────┼─────────────────┼─────────────────────┤
│ 交易数据 │ 关系型数据库 │ ACID事务 │
│ 会话数据 │ Redis │ 高性能、自动过期 │
│ 搜索数据 │ Elasticsearch │ 全文检索 │
│ 日志数据 │ ClickHouse │ 时序分析 │
│ 文件数据 │ 对象存储 │ 大文件、CDN分发 │
│ 缓存数据 │ 本地内存 │ 最低延迟 │
└─────────────────┴─────────────────┴─────────────────────┘
反例：字段含义过于具体
-- 糟糕：字段过于具体，难以扩展
CREATE TABLE users (
user_id INT PRIMARY KEY,
email VARCHAR(100),
phone VARCHAR(20), -- 只能存一个手机号
address VARCHAR(500) -- 只能存一个地址
);
正例：设计可扩展的结构
-- 好的：使用关联表支持多值属性
CREATE TABLE users (
user_id INT PRIMARY KEY,
email VARCHAR(100)
);
CREATE TABLE user_contacts (
contact_id INT PRIMARY KEY,
user_id INT,
contact_type ENUM('phone', 'email', 'wechat'),
contact_value VARCHAR(100),
is_primary BOOLEAN DEFAULT FALSE,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
CREATE TABLE user_addresses (
address_id INT PRIMARY KEY,
user_id INT,
address_type ENUM('home', 'work', 'shipping'),
province VARCHAR(50),
city VARCHAR(50),
detail VARCHAR(500),
is_default BOOLEAN DEFAULT FALSE,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
对于变化频繁的属性，可以使用JSON字段：
CREATE TABLE products (
product_id VARCHAR(32) PRIMARY KEY,
name VARCHAR(200),
base_price DECIMAL(16,2),
-- 不同品类有不同的属性
attributes JSON,
INDEX idx_category ((attributes->>'$.category'))
);
-- 插入不同品类的商品
INSERT INTO products VALUES
('P001', 'iPhone 15', 5999, '{"category": "phone", "screen": "6.1英寸", "storage": "256GB"}'),
('P002', 'Nike Air Max', 899, '{"category": "shoes", "size": "42", "color": "black"}');
强一致性（Strong Consistency）：
最终一致性（Eventual Consistency）：
因果一致性（Causal Consistency）：
┌──────────────────────────────────────────────────────────────┐
│ 一致性级别决策树 │
├──────────────────────────────────────────────────────────────┤
│ │
│ 数据是否涉及资金/库存等关键业务？ │
│ ├── 是 → 强一致性（ACID事务） │
│ └── 否 → │
│ 延迟敏感吗？ │
│ ├── 是 → 缓存 + 最终一致性 │
│ └── 否 → 根据业务接受度选择 │
│ │
└──────────────────────────────────────────────────────────────┘
事务管理：
-- 显式事务保证一致性
START TRANSACTION;
-- 扣减库存
UPDATE products SET stock = stock - 1 WHERE product_id = 'P001' AND stock > 0;
-- 检查是否扣减成功
IF ROW_COUNT() = 0 THEN
ROLLBACK;
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足';
END IF;
-- 创建订单
INSERT INTO orders (order_no, product_id, quantity) VALUES ('O001', 'P001', 1);
COMMIT;
乐观锁：
-- 版本号实现乐观锁
UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE account_id = 'A001' AND version = 5;
-- 应用层检查影响行数，为0则说明版本冲突
悲观锁：
-- SELECT FOR UPDATE
START TRANSACTION;
SELECT * FROM accounts WHERE account_id = 'A001' FOR UPDATE;
-- ... 业务逻辑 ...
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A001';
COMMIT;
表命名：
orders 而非 orderorder → orders字段命名：
id 或 {table}_id{referenced_table}_idis_{adjective} 或 has_{noun}{action}_at（created_at, updated_at）索引命名：
pk_{table}uk_{table}_{fields}idx_{table}_{fields}范式理论：
| 范式 | 要求 | 目的 |
|---|---|---|
| 1NF | 字段原子性 | 消除重复组 |
| 2NF | 消除部分依赖 | 非主属性完全依赖于主键 |
| 3NF | 消除传递依赖 | 非主属性不依赖于其他非主属性 |
| BCNF | 消除主属性依赖 | 更强的3NF |
实践建议：
问题：物理删除导致数据无法恢复、关联数据丢失
解决方案：
CREATE TABLE products (
product_id VARCHAR(32) PRIMARY KEY,
name VARCHAR(200),
-- 软删除标记
is_deleted BOOLEAN DEFAULT FALSE,
deleted_at DATETIME,
deleted_by VARCHAR(32),
INDEX idx_active (is_deleted), -- 常用过滤条件
INDEX idx_deleted_at (deleted_at) -- 清理历史数据时使用
);
-- 查询时默认过滤已删除数据
SELECT * FROM products WHERE is_deleted = FALSE;
-- "删除"操作实际是更新
UPDATE products
SET is_deleted = TRUE, deleted_at = NOW(), deleted_by = 'admin'
WHERE product_id = 'P001';
需求：追踪数据变更历史，满足合规要求
实现方案：
-- 审计日志表
CREATE TABLE audit_log (
log_id BIGINT AUTO_INCREMENT PRIMARY KEY,
table_name VARCHAR(64),
record_id VARCHAR(64),
operation ENUM('INSERT', 'UPDATE', 'DELETE'),
old_values JSON,
new_values JSON,
operated_by VARCHAR(32),
operated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_table_record (table_name, record_id),
INDEX idx_operated_at (operated_at)
);
-- 通过触发器自动记录（示例）
CREATE TRIGGER trg_products_audit_update
AFTER UPDATE ON products
FOR EACH ROW
INSERT INTO audit_log (table_name, record_id, operation, old_values, new_values, operated_by)
VALUES ('products', OLD.product_id, 'UPDATE',
JSON_OBJECT('name', OLD.name, 'price', OLD.price),
JSON_OBJECT('name', NEW.name, 'price', NEW.price),
@current_user_id);
需求：SaaS系统需要隔离不同租户的数据
三种隔离策略：
| 策略 | 实现方式 | 优点 | 缺点 |
|---|---|---|---|
| 独立数据库 | 每个租户一个数据库 | 完全隔离、可定制 | 成本高、维护复杂 |
| 共享数据库独立Schema | 同一数据库不同Schema | 较好的隔离 | Schema管理复杂 |
| 共享Schema | 所有租户共享表，加tenant_id字段 | 成本低、易扩展 | 隔离性较弱 |
共享Schema方案示例：
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
tenant_id VARCHAR(32) NOT NULL, -- 租户标识
order_no VARCHAR(64),
-- ... 其他字段
FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id),
UNIQUE KEY uk_tenant_order (tenant_id, order_no), -- 租户内唯一
INDEX idx_tenant_created (tenant_id, created_at)
) PARTITION BY LIST COLUMNS(tenant_id) ( -- 可选：按租户分区
PARTITION p_t1 VALUES IN ('tenant_001'),
PARTITION p_t2 VALUES IN ('tenant_002'),
PARTITION p_default VALUES IN (DEFAULT)
);
需求：管理实体在生命周期中的状态流转
实现：
-- 订单状态机
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
order_no VARCHAR(64),
-- 状态定义
status TINYINT NOT NULL COMMENT '
10=待支付,
20=已支付,
30=已发货,
40=已完成,
50=已取消,
60=已退款',
-- 状态变更时间记录
paid_at DATETIME,
shipped_at DATETIME,
completed_at DATETIME,
cancelled_at DATETIME,
INDEX idx_status (status)
);
-- 状态流转日志（可选）
CREATE TABLE order_status_history (
history_id BIGINT PRIMARY KEY,
order_id BIGINT,
from_status TINYINT,
to_status TINYINT,
changed_by VARCHAR(32),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
reason VARCHAR(500),
FOREIGN KEY (order_id) REFERENCES orders(order_id)
);
有效状态转换表：
当前状态 → 可转换到
─────────────────────────────
待支付(10) → 已支付(20), 已取消(50)
已支付(20) → 已发货(30), 已退款(60)
已发货(30) → 已完成(40), 已退款(60)
已完成(40) → (终态)
已取消(50) → (终态)
已退款(60) → (终态)
问题描述：将结构化数据存储为三列（实体-属性-值），看似灵活，实则灾难。
-- EAV 反模式示例（避免使用）
CREATE TABLE product_attributes (
entity_id VARCHAR(32), -- 产品ID
attribute_name VARCHAR(64), -- 属性名
attribute_value TEXT -- 属性值
);
-- 查询变得非常复杂
SELECT p.entity_id,
MAX(CASE WHEN attribute_name = 'color' THEN attribute_value END) AS color,
MAX(CASE WHEN attribute_name = 'size' THEN attribute_value END) AS size
FROM product_attributes p
GROUP BY p.entity_id;
问题：
替代方案：
问题描述：一个列存储不同类型的数据，通过另一个列区分含义。
-- 反模式示例
CREATE TABLE contacts (
contact_id INT PRIMARY KEY,
contact_type VARCHAR(20), -- 'email', 'phone', 'fax'
contact_value VARCHAR(100) -- 可能是邮箱、电话或传真
);
问题：
正确做法：
CREATE TABLE contacts (
contact_id INT PRIMARY KEY,
email VARCHAR(100),
phone VARCHAR(20),
fax VARCHAR(20),
preferred_contact ENUM('email', 'phone', 'fax') DEFAULT 'email'
);
问题描述：表中包含大量NULL值的列，通常是因为合并了多个实体类型。
-- 反模式：不同类型的产品混在一起
CREATE TABLE products (
product_id VARCHAR(32),
name VARCHAR(200),
-- 手机特有
screen_size DECIMAL(4,2), -- 大部分产品为NULL
battery_capacity INT, -- 大部分产品为NULL
-- 衣服特有
size VARCHAR(10), -- 大部分产品为NULL
color VARCHAR(20), -- 大部分产品为NULL
material VARCHAR(50) -- 大部分产品为NULL
);
解决方案：
CREATE TABLE products (
product_id VARCHAR(32) PRIMARY KEY,
name VARCHAR(200),
category ENUM('phone', 'clothing', 'book'),
common_attrs JSON, -- 所有产品共有的扩展属性
specific_attrs JSON -- 品类特有的属性
);
-- 基础产品表
CREATE TABLE products (
product_id VARCHAR(32) PRIMARY KEY,
name VARCHAR(200),
price DECIMAL(16,2)
);
-- 手机特有属性
CREATE TABLE product_phones (
product_id VARCHAR(32) PRIMARY KEY,
screen_size DECIMAL(4,2),
battery_capacity INT,
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
-- 服装特有属性
CREATE TABLE product_clothing (
product_id VARCHAR(32) PRIMARY KEY,
size VARCHAR(10),
color VARCHAR(20),
material VARCHAR(50),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
问题描述：缺乏主键、外键、CHECK约束，导致数据质量问题。
-- 反模式
CREATE TABLE orders (
id INT, -- 无主键
user_id INT, -- 无外键
amount DECIMAL, -- 无精度限制
status VARCHAR -- 无枚举限制
);
正确做法：
CREATE TABLE orders (
order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
amount DECIMAL(16, 2) NOT NULL CHECK (amount >= 0),
status TINYINT NOT NULL DEFAULT 10
CHECK (status IN (10, 20, 30, 40, 50, 60)),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id)
ON DELETE RESTRICT ON UPDATE CASCADE
);
问题描述：为每个字段都建索引，导致写操作性能下降。
问题：
索引设计原则：
┌──────────┐ ┌─────────────┐ ┌──────────────┐
│ 应用层 │────►│ 写操作 │────►│ 主库(Master)│
└──────────┘ └─────────────┘ └──────────────┘
│ │
│ 数据同步
│ ▼
│ ┌─────────────┐ ┌──────────────┐
└────►│ 读操作 │◄────────│ 从库(Slave) │
└─────────────┘ └──────────────┘
垂直分表：按字段拆分
用户表(user_id, name, email, avatar, bio, settings)
↓
用户基础表(user_id, name, email)
用户详情表(user_id, avatar, bio, settings)
水平分表：按行拆分
订单表
↓
orders_202401, orders_202402, orders_202403...
分片键选择原则：
核心流程：
用户浏览商品 → 加入购物车 → 下单 → 支付 → 发货 → 收货 → 评价
关键实体：用户、商品、订单、库存、支付、物流
-- 商品表（SPU）
CREATE TABLE products (
product_id VARCHAR(32) PRIMARY KEY COMMENT 'SPU ID',
product_name VARCHAR(200) NOT NULL,
category_id VARCHAR(32) NOT NULL,
brand_id VARCHAR(32),
description TEXT,
status TINYINT DEFAULT 1 COMMENT '0-下架 1-上架',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_category (category_id),
INDEX idx_status (status)
) COMMENT='商品SPU表';
-- SKU表（库存单元）
CREATE TABLE skus (
sku_id VARCHAR(32) PRIMARY KEY,
product_id VARCHAR(32) NOT NULL,
sku_name VARCHAR(200),
sku_specs JSON COMMENT '规格组合 {"颜色":"红","尺码":"XL"}',
price DECIMAL(16, 2) NOT NULL,
stock INT UNSIGNED DEFAULT 0,
status TINYINT DEFAULT 1,
FOREIGN KEY (product_id) REFERENCES products(product_id),
INDEX idx_product (product_id)
) COMMENT='商品SKU表';
-- 订单主表
CREATE TABLE orders (
order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
order_no VARCHAR(64) UNIQUE NOT NULL COMMENT '订单编号，对外展示',
user_id INT UNSIGNED NOT NULL,
order_status TINYINT DEFAULT 10 COMMENT '10-待支付 20-已支付...',
total_amount DECIMAL(16, 2) NOT NULL,
discount_amount DECIMAL(16, 2) DEFAULT 0,
pay_amount DECIMAL(16, 2) NOT NULL,
pay_type TINYINT COMMENT '1-支付宝 2-微信 3-银行卡',
pay_time DATETIME,
delivery_time DATETIME,
receive_time DATETIME,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_user_created (user_id, created_at),
INDEX idx_order_no (order_no),
INDEX idx_status_created (order_status, created_at)
) COMMENT='订单主表';
-- 订单商品明细
CREATE TABLE order_items (
item_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
order_id BIGINT UNSIGNED NOT NULL,
product_id VARCHAR(32) NOT NULL,
sku_id VARCHAR(32) NOT NULL,
product_name VARCHAR(200),
sku_specs JSON,
price DECIMAL(16, 2) NOT NULL COMMENT '下单时价格',
quantity INT UNSIGNED NOT NULL,
subtotal DECIMAL(16, 2) AS (price * quantity) STORED,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
INDEX idx_order (order_id)
) COMMENT='订单商品明细';
-- 库存表（支持预占库存模式）
CREATE TABLE inventory (
sku_id VARCHAR(32) PRIMARY KEY,
available_stock INT UNSIGNED DEFAULT 0 COMMENT '可用库存',
reserved_stock INT UNSIGNED DEFAULT 0 COMMENT '已预占库存',
sold_stock BIGINT UNSIGNED DEFAULT 0 COMMENT '已售数量',
version INT UNSIGNED DEFAULT 1 COMMENT '乐观锁版本',
updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- 实际库存 = available_stock + reserved_stock
CONSTRAINT chk_stock CHECK (available_stock >= 0)
) COMMENT='库存表';
orders_2024_01-- 用户账户表
CREATE TABLE accounts (
account_id VARCHAR(32) PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
account_type TINYINT NOT NULL COMMENT '1-基本户 2-一般户 3-专用户',
currency VARCHAR(3) DEFAULT 'CNY',
balance DECIMAL(18, 4) DEFAULT 0,
frozen_amount DECIMAL(18, 4) DEFAULT 0,
available_balance DECIMAL(18, 4) AS (balance - frozen_amount) STORED,
status TINYINT DEFAULT 1 COMMENT '0-冻结 1-正常',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
version INT UNSIGNED DEFAULT 1,
INDEX idx_user (user_id),
UNIQUE KEY uk_user_type_currency (user_id, account_type, currency),
CONSTRAINT chk_balance CHECK (balance >= frozen_amount)
) COMMENT='账户表';
-- 交易流水表（不可变，只能插入）
CREATE TABLE transactions (
transaction_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
transaction_no VARCHAR(64) UNIQUE NOT NULL,
transaction_type TINYINT NOT NULL COMMENT '1-充值 2-提现 3-转账...',
from_account VARCHAR(32),
to_account VARCHAR(32),
amount DECIMAL(18, 4) NOT NULL,
currency VARCHAR(3) DEFAULT 'CNY',
status TINYINT DEFAULT 1 COMMENT '1-成功 2-处理中 3-失败',
remark VARCHAR(500),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
completed_at DATETIME,
INDEX idx_from_account (from_account, created_at),
INDEX idx_to_account (to_account, created_at),
INDEX idx_created (created_at)
) COMMENT='交易流水表';
-- 账户明细（余额变更记录）
CREATE TABLE account_details (
detail_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
account_id VARCHAR(32) NOT NULL,
transaction_id BIGINT UNSIGNED,
change_type TINYINT NOT NULL COMMENT '1-收入 2-支出',
amount DECIMAL(18, 4) NOT NULL,
balance_before DECIMAL(18, 4) NOT NULL,
balance_after DECIMAL(18, 4) NOT NULL,
remark VARCHAR(500),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (account_id) REFERENCES accounts(account_id),
INDEX idx_account_created (account_id, created_at),
INDEX idx_transaction (transaction_id)
) COMMENT='账户明细表';
-- 对账记录
CREATE TABLE reconciliation (
recon_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
recon_date DATE NOT NULL,
channel VARCHAR(32) NOT NULL COMMENT '对账渠道',
total_count INT UNSIGNED,
total_amount DECIMAL(18, 4),
matched_count INT UNSIGNED,
unmatched_count INT UNSIGNED,
status TINYINT DEFAULT 0 COMMENT '0-待处理 1-处理中 2-完成',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
completed_at DATETIME,
UNIQUE KEY uk_date_channel (recon_date, channel)
) COMMENT='对账记录表';
-- 转账事务（存储过程示例）
DELIMITER //
CREATE PROCEDURE transfer(
IN p_from_account VARCHAR(32),
IN p_to_account VARCHAR(32),
IN p_amount DECIMAL(18, 4),
IN p_transaction_no VARCHAR(64),
OUT p_result INT
)
BEGIN
DECLARE v_version INT;
DECLARE v_balance DECIMAL(18, 4);
START TRANSACTION;
-- 1. 检查并锁定付款方账户
SELECT balance, version INTO v_balance, v_version
FROM accounts
WHERE account_id = p_from_account FOR UPDATE;
IF v_balance < p_amount THEN
SET p_result = -1; -- 余额不足
ROLLBACK;
ELSE
-- 2. 扣减付款方余额
UPDATE accounts
SET balance = balance - p_amount, version = version + 1
WHERE account_id = p_from_account AND version = v_version;
IF ROW_COUNT() = 0 THEN
SET p_result = -2; -- 并发冲突
ROLLBACK;
ELSE
-- 3. 增加收款方余额
UPDATE accounts
SET balance = balance + p_amount, version = version + 1
WHERE account_id = p_to_account;
-- 4. 记录交易流水
INSERT INTO transactions (transaction_no, transaction_type,
from_account, to_account, amount, status)
VALUES (p_transaction_no, 3, p_from_account, p_to_account, p_amount, 1);
SET p_result = 1; -- 成功
COMMIT;
END IF;
END IF;
END //
DELIMITER ;
-- 租户表
CREATE TABLE tenants (
tenant_id VARCHAR(32) PRIMARY KEY,
tenant_name VARCHAR(100) NOT NULL,
tenant_code VARCHAR(32) UNIQUE NOT NULL,
plan_type TINYINT DEFAULT 1 COMMENT '1-免费 2-基础 3-专业 4-企业',
max_users INT UNSIGNED DEFAULT 10,
max_storage BIGINT DEFAULT 1073741824, -- 1GB
status TINYINT DEFAULT 1 COMMENT '0-停用 1-正常',
expire_date DATE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) COMMENT='租户表';
-- 租户配置表（扩展字段）
CREATE TABLE tenant_configs (
tenant_id VARCHAR(32) PRIMARY KEY,
config_data JSON,
updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id)
) COMMENT='租户配置表';
-- 所有业务表都包含 tenant_id 字段
CREATE TABLE projects (
project_id VARCHAR(32) PRIMARY KEY,
tenant_id VARCHAR(32) NOT NULL,
project_name VARCHAR(100) NOT NULL,
owner_id VARCHAR(32) NOT NULL,
status TINYINT DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id),
INDEX idx_tenant (tenant_id, created_at)
) COMMENT='项目表';
-- 角色表（租户级别）
CREATE TABLE roles (
role_id VARCHAR(32) PRIMARY KEY,
tenant_id VARCHAR(32) NOT NULL,
role_name VARCHAR(50) NOT NULL,
role_code VARCHAR(50) NOT NULL,
description VARCHAR(200),
is_system BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id),
UNIQUE KEY uk_tenant_role_code (tenant_id, role_code)
) COMMENT='角色表';
-- 权限表
CREATE TABLE permissions (
permission_id VARCHAR(32) PRIMARY KEY,
permission_code VARCHAR(100) UNIQUE NOT NULL,
permission_name VARCHAR(100),
resource_type VARCHAR(50),
action VARCHAR(50),
description VARCHAR(200)
) COMMENT='权限表';
-- 角色-权限关联
CREATE TABLE role_permissions (
role_id VARCHAR(32),
permission_id VARCHAR(32),
PRIMARY KEY (role_id, permission_id),
FOREIGN KEY (role_id) REFERENCES roles(role_id),
FOREIGN KEY (permission_id) REFERENCES permissions(permission_id)
) COMMENT='角色权限关联';
-- 用户-角色关联
CREATE TABLE user_roles (
user_id VARCHAR(32),
role_id VARCHAR(32),
granted_by VARCHAR(32),
granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id, role_id),
FOREIGN KEY (role_id) REFERENCES roles(role_id)
) COMMENT='用户角色关联';
| 工具 | 特点 | 适用场景 |
|---|---|---|
| PowerDesigner | 功能全面，支持多种数据库 | 大型企业，复杂项目 |
| ER/Studio | 优秀的可视化，团队协作强 | 中型企业，团队开发 |
| Navicat Data Modeler | 轻量易用，性价比高 | 中小项目，个人开发者 |
| 工具 | 类型 | 特点 |
|---|---|---|
| draw.io/diagrams.net | 在线绘图 | 免费，支持ER图绘制 |
| dbdiagram.io | 在线建模 | 通过DSL定义，自动生成ER图和SQL |
| MySQL Workbench | 数据库工具 | MySQL官方，支持正向/逆向工程 |
| pgModeler | 桌面应用 | PostgreSQL专用，开源 |
// 定义表
Table users {
id int [pk, increment]
username varchar [unique, not null]
email varchar [unique, not null]
created_at timestamp [default: `now()`]
}
Table orders {
id int [pk, increment]
user_id int [not null, ref: > users.id]
order_no varchar [unique, not null]
total_amount decimal [not null]
status varchar [default: 'pending']
created_at timestamp [default: `now()`]
}
Table order_items {
id int [pk, increment]
order_id int [not null, ref: > orders.id]
product_id int [not null]
quantity int [not null]
price decimal [not null]
}
| 字段名 | 数据类型 | 长度 | 是否必填 | 默认值 | 说明 |
|---|---|---|---|---|---|
## 表名：orders
### 基本信息
- **说明**：订单主表
- **引擎**：InnoDB
- **字符集**：utf8mb4
- **创建时间**：2024-01-15
- **创建人**：张三
### 字段定义
| 字段名 | 类型 | 约束 | 说明 |
|--------|------|------|------|
| order_id | BIGINT | PK, AI | 订单ID |
| ... | ... | ... | ... |
### 索引
| 索引名 | 字段 | 类型 | 说明 |
|--------|------|------|------|
| idx_user_created | user_id, created_at | 普通 | 用户订单查询 |
### 关联关系
- users.user_id → orders.user_id (N:1)
### 变更历史
| 日期 | 变更人 | 变更内容 |
|------|--------|----------|
| 2024-02-01 | 李四 | 新增 pay_time 字段 |
结构性检查：
业务性检查：
技术性检查：
使用版本控制管理数据库变更：
database/
├── migrations/
│ ├── V1.0__initial_schema.sql
│ ├── V1.1__add_user_profile.sql
│ ├── V1.2__create_order_tables.sql
│ └── V1.3__add_indexes.sql
├── seeds/
│ └── initial_data.sql
└── README.md
变更管理工具：
数据模型设计是一门艺术与科学的结合。它既需要严谨的逻辑思维，又需要对业务的深刻理解。
关键原则：
常见误区避免：
经典书籍：
在线资源：
好的数据模型设计不是一蹴而就的，而是在实践中不断打磨和优化的结果。保持好奇心，持续学习，善于总结，你会在这条路上越走越远。
记住：数据是资产，模型是骨架。保护好你的数据，设计好你的模型，它们会在未来的某一天给你带来意想不到的回报。
本文档持续更新中，如有问题或建议，欢迎讨论交流。