领域: 软件架构 > 数据建模
标签: #数据建模 #数据库设计 #架构设计 #领域驱动设计 #ER模型 #范式
数据模型设计是软件系统架构的基石。一个优秀的数据模型不仅能准确表达业务领域知识，还能为系统提供可扩展、高性能的数据基础。本文从理论基础到实践方法，全面解析数据模型设计的核心要点。
数据模型是对现实世界数据特征的抽象描述，它定义了数据的结构、关系、约束和操作规则。数据模型是连接业务需求与技术实现的桥梁，决定了系统如何存储、组织和访问数据。
数据模型的三个核心要素：
┌─────────────────────────────────────────────────────────┐
│ 数据模型 │
├──────────────┬──────────────┬───────────────────────────┤
│ 数据结构 │ 数据操作 │ 数据约束 │
├──────────────┼──────────────┼───────────────────────────┤
│ • 数据类型 │ • 查询 │ • 实体完整性 │
│ • 关系定义 │ • 插入 │ • 参照完整性 │
│ • 层次结构 │ • 更新 │ • 用户自定义约束 │
│ • 属性定义 │ • 删除 │ • 业务规则 │
└──────────────┴──────────────┴───────────────────────────┘
根据抽象程度不同，数据模型分为三个层次：
目标: 与具体数据库无关，面向业务人员和领域专家
核心元素: 实体、属性、关系
常用工具: ER图、UML类图、领域模型图
┌────────────────────────────────────────────────────────────┐
│ 业务领域 │
│ ┌─────────┐ 下订单 ┌─────────┐ │
│ │ 客户 │ ──────────────>│ 订单 │ │
│ │(Customer)│ │ (Order) │ │
│ └────┬────┘ └────┬────┘ │
│ │ │ │
│ ┌────┴────┐ ┌────┴────┐ │
│ │姓名 │ │订单号 │ │
│ │邮箱 │ │下单时间 │ │
│ │电话 │ │总金额 │ │
│ └─────────┘ └─────────┘ │
└────────────────────────────────────────────────────────────┘
目标: 与具体DBMS无关，面向系统分析师和架构师
核心元素: 表、字段、主键、外键、索引
常用工具: 规范化ER图、关系模型图
-- 逻辑模型示例
Customer (customer_id PK, name, email, phone)
Order (order_id PK, customer_id FK, order_date, total_amount)
└── FK references Customer(customer_id)
目标: 针对特定DBMS的实现细节
核心元素: 数据类型、存储引擎、分区策略、索引类型
常用工具: DDL脚本、数据库设计工具
-- 物理模型示例 (MySQL)
CREATE TABLE customer (
customer_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL COLLATE utf8mb4_unicode_ci,
email VARCHAR(255) NOT NULL UNIQUE,
phone VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_email (email)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
┌────────────────────────────────────────────────────────────┐
│ 数据模型设计的影响 │
├──────────────┬──────────────┬──────────────┬──────────────┤
│ 系统性能 │ 开发效率 │ 维护成本 │ 业务敏捷 │
├──────────────┼──────────────┼──────────────┼──────────────┤
│ • 查询性能 │ • 开发速度 │ • 变更成本 │ • 需求响应 │
│ • 存储效率 │ • 理解成本 │ • 数据迁移 │ • 扩展能力 │
│ • 并发能力 │ • 沟通效率 │ • 技术债务 │ • 集成能力 │
└──────────────┴──────────────┴──────────────┴──────────────┘
关系模型由Edgar F. Codd于1970年提出，基于集合论和谓词逻辑，是目前最主流的数据模型。
┌─────────────────────────────────────────────────────────────┐
│ 关系模型核心术语 │
├─────────────┬──────────────────────────────────────────────┤
│ 术语 │ 含义 │
├─────────────┼──────────────────────────────────────────────┤
│ 关系(Relation) │ 二维表，对应数据库中的表 │
│ 元组(Tuple) │ 表中的一行，对应一条记录 │
│ 属性(Attribute)│ 表中的一列，对应一个字段 │
│ 域(Domain) │ 属性的取值范围 │
│ 主键(Primary Key)│ 唯一标识元组的属性集 │
│ 外键(Foreign Key)│ 引用其他关系主键的属性 │
│ 候选键(Candidate Key)│ 可作为主键的属性集 │
└─────────────┴──────────────────────────────────────────────┘
-- 1. 实体完整性：主键非空且唯一
CREATE TABLE product (
product_id INT PRIMARY KEY, -- 非空 + 唯一
name VARCHAR(100) NOT NULL
);
-- 2. 参照完整性：外键必须引用存在的值或NULL
CREATE TABLE order_item (
item_id INT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT,
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES product(product_id)
ON DELETE SET NULL -- 删除产品时将外键设为NULL
);
-- 3. 用户定义完整性
CREATE TABLE employee (
id INT PRIMARY KEY,
age INT CHECK (age >= 18 AND age <= 65),
email VARCHAR(255) UNIQUE,
salary DECIMAL(10,2) CHECK (salary > 0)
);
范式是关系数据库设计的规范化标准，用于减少数据冗余和异常。
定义: 每个属性值都是不可分的原子值
❌ 不符合1NF
orders (order_id, customer, products)
其中products: "苹果,香蕉,橙子" -- 包含多个值
✅ 符合1NF
orders (order_id, customer)
order_items (order_id, product_name, quantity)
定义: 满足1NF，且非主键属性完全依赖于主键
❌ 不符合2NF (组合主键: order_id + product_id)
order_items (order_id, product_id, quantity, product_price)
-- product_price只依赖于product_id，不完全依赖主键
✅ 符合2NF
order_items (order_id, product_id, quantity)
products (product_id, product_price, product_name)
定义: 满足2NF，且不存在非主键属性对主键的传递依赖
❌ 不符合3NF
employees (id, name, dept_id, dept_name, dept_location)
-- dept_name和dept_location传递依赖于id（通过dept_id）
✅ 符合3NF
employees (id, name, dept_id)
departments (dept_id, dept_name, dept_location)
定义: 对于每个函数依赖 X → Y，X 必须是超键
❌ 不符合BCNF
student_courses (student_id, course_id, teacher_id)
假设规则：每个课程只由一个教师教授
函数依赖：course_id → teacher_id
但course_id不是超键
✅ 符合BCNF
student_courses (student_id, course_id)
course_teachers (course_id, teacher_id)
┌─────────────────────────────────────────────────────────────┐
│ 范式层级与解决的数据异常 │
├─────────┬────────────────────────┬──────────────────────────┤
│ 范式 │ 解决的核心问题 │ 典型应用场景 │
├─────────┼────────────────────────┼──────────────────────────┤
│ 1NF │ 重复组、多值属性 │ 所有关系数据库的基础 │
│ 2NF │ 部分函数依赖 │ 复合主键场景 │
│ 3NF │ 传递函数依赖 │ 一般OLTP系统 │
│ BCNF │ 非平凡函数依赖 │ 复杂约束场景 │
│ 4NF │ 多值依赖 │ 多对多关系分离 │
│ 5NF │ 连接依赖 │ 极少使用 │
└─────────┴────────────────────────┴──────────────────────────┘
在某些场景下，为了性能考虑，可以有意识地违反范式原则。
-- 1. 冗余字段：存储派生数据
CREATE TABLE orders (
order_id PRIMARY KEY,
customer_id INT,
total_amount DECIMAL(10,2),
item_count INT, -- 冗余：可从order_items计算
last_updated TIMESTAMP
);
-- 2. 预聚合表：存储聚合结果
CREATE TABLE daily_sales_summary (
date DATE PRIMARY KEY,
total_orders INT,
total_revenue DECIMAL(12,2),
avg_order_value DECIMAL(10,2)
);
-- 3. 宽表设计：减少JOIN操作
CREATE TABLE user_profiles_wide (
user_id PRIMARY KEY,
-- 基本信息
username, email, phone,
-- 统计信息（冗余）
order_count, total_spent,
-- 偏好信息
preferred_category,
last_login_at
);
┌────────────────────────────────────────────────────────────┐
│ 反规范化决策矩阵 │
├──────────────┬──────────────┬──────────────┬──────────────┤
│ 场景 │ 推荐策略 │ 维护方式 │ 风险 │
├──────────────┼──────────────┼──────────────┼──────────────┤
│ 读多写少 │ 物化视图 │ 定时刷新 │ 数据延迟 │
│ 实时报表 │ 触发器维护 │ 实时更新 │ 写性能下降 │
│ 高频查询 │ 应用层缓存 │ 应用控制 │ 一致性问题 │
│ 复杂分析 │ 宽表/星型模型 │ ETL更新 │ 存储增加 │
└──────────────┴──────────────┴──────────────┴──────────────┘
┌─────────────────────────────────────────────────────────────┐
│ NoSQL数据库分类 │
├───────────────┬───────────────┬────────────────────────────┤
│ 类型 │ 数据模型 │ 典型产品 │
├───────────────┼───────────────┼────────────────────────────┤
│ 文档数据库 │ JSON/BSON文档 │ MongoDB, Couchbase │
│ 键值存储 │ 键-值对 │ Redis, DynamoDB │
│ 列族存储 │ 列族 │ Cassandra, HBase │
│ 图数据库 │ 节点和关系 │ Neo4j, Amazon Neptune │
│ 搜索引擎 │ 倒排索引 │ Elasticsearch, Solr │
│ 时序数据库 │ 时间序列 │ InfluxDB, TimescaleDB │
└───────────────┴───────────────┴────────────────────────────┘
// 关系模型：需要多个表 + JOIN
// users, orders, order_items, products
// 文档模型：嵌入式文档
{
_id: ObjectId("..."),
username: "zhangsan",
email: "zhangsan@example.com",
profile: {
age: 28,
city: "上海",
preferences: ["科技", "阅读"]
},
orders: [
{
order_id: "ORD-001",
date: ISODate("2024-01-15"),
items: [
{ product: "iPhone 15", price: 7999, quantity: 1 },
{ product: "AirPods", price: 1299, quantity: 2 }
],
total: 10597
}
]
}
┌─────────────────────────────────────────────────────────────┐
│ 嵌入 vs 引用决策指南 │
├──────────────────┬─────────────────┬───────────────────────┤
│ 嵌入条件 │ 引用条件 │ 混合策略场景 │
├──────────────────┼─────────────────┼───────────────────────┤
│ • 数据一起读写 │ • 数据独立更新 │ • 最近数据嵌入 │
│ • 1:1或1:few关系 │ • 多对多关系 │ • 历史数据引用 │
│ • 大小有界(<16MB)│ • 数据量不确定 │ • 摘要信息嵌入 │
│ • 无需独立查询 │ • 需要独立访问 │ • 详情数据引用 │
└──────────────────┴─────────────────┴───────────────────────┘
// 1. 数组大小控制 - 桶模式
// ❌ 订单无限增长
db.customers.findOne({ _id: 1 }).orders.length // 可能成千上万
// ✅ 按年月分桶
db.orders_by_month.insertOne({
customer_id: 1,
year: 2024,
month: 1,
orders: [...], // 该月所有订单
summary: { count: 15, total: 23500 }
});
// 2. 计算模式 - 预计算聚合值
db.products.insertOne({
name: "iPhone 15",
price: 7999,
// 预计算字段
stats: {
review_count: 1250,
avg_rating: 4.7,
total_sold: 50000
}
});
// 3. 子集模式 - 分离冷热数据
db.movies.insertOne({
_id: 1,
title: "流浪地球2",
// 热数据：总是需要
basic_info: { director, year, genre, rating },
// 温数据：有时需要
cast: [...],
// 冷数据：很少需要
full_synopsis: "...",
technical_specs: {...}
});
-- 关系思维（不适合Cassandra）
CREATE TABLE users (
user_id UUID PRIMARY KEY,
name TEXT,
email TEXT
);
CREATE TABLE orders (
order_id UUID PRIMARY KEY,
user_id UUID,
amount DECIMAL
);
-- Cassandra思维：查询驱动设计
-- 场景：按用户查询订单历史
CREATE TABLE user_orders (
user_id UUID,
order_date TIMESTAMP,
order_id UUID,
amount DECIMAL,
status TEXT,
PRIMARY KEY (user_id, order_date)
) WITH CLUSTERING ORDER BY (order_date DESC);
-- 场景：按状态查询订单
CREATE TABLE orders_by_status (
status TEXT,
order_date TIMESTAMP,
order_id UUID,
user_id UUID,
amount DECIMAL,
PRIMARY KEY (status, order_date, order_id)
);
┌────────────────────────────────────────────────────────────┐
│ Cassandra数据建模流程 │
├──────────────┬─────────────────────────────────────────────┤
│ 步骤 │ 内容 │
├──────────────┼─────────────────────────────────────────────┤
│ 1. 理解业务 │ 识别实体、关系、查询模式 │
│ 2. 设计查询 │ 列出所有需要的查询 │
│ 3. 设计表 │ 每个查询对应一个表（反规范化） │
│ 4. 定义主键 │ 分区键 + 聚簇列 │
│ 5. 评估优化 │ 考虑数据分布、读写性能 │
└──────────────┴─────────────────────────────────────────────┘
// 节点（实体）
CREATE (p:Person {name: "张三", age: 30})
CREATE (c:Company {name: "阿里巴巴", founded: 1999})
// 关系（边）
CREATE (p)-[:WORKS_AT {since: 2015, role: "工程师"}]->(c)
CREATE (p2:Person {name: "李四"})
CREATE (p)-[:MANAGES]->(p2)
// 查询：查找张三是谁的下属
MATCH (manager)-[:MANAGES]->(p:Person {name: "张三"})
RETURN manager.name
// 查询：查找阿里巴巴的员工
MATCH (p:Person)-[:WORKS_AT]->(c:Company {name: "阿里巴巴"})
RETURN p.name, p.age
┌────────────────────────────────────────────────────────────┐
│ 图数据库建模原则 │
├──────────────┬─────────────────────────────────────────────┤
│ 原则 │ 说明 │
├──────────────┼─────────────────────────────────────────────┤
│ 节点建模 │ 实体用节点，属性值通常作为属性 │
│ 关系建模 │ 动词和动作用关系，可以有时间属性 │
│ 避免中间节点 │ 尽量使用直接关系而非中间节点 │
│ 关系方向 │ 语义明确时指定方向，双向关系创建两个 │
│ 标签设计 │ 使用多个标签表示继承，如(:Person:Employee) │
└──────────────┴─────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────┐
│ ER图符号说明 │
├─────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────┐ ┌──────────┐ │
│ │ 客户 │ 1 * │ 订单 │ │
│ │ ───── │──────────────│ ─── │ │
│ │ 客户ID │ 下达 │ 订单ID │ │
│ │ 姓名 │ │ 日期 │ │
│ │ 电话 │ │ 金额 │ │
│ └──────────┘ └──────────┘ │
│ │
│ 矩形 = 实体 椭圆 = 属性 菱形 = 关系 │
│ │
└─────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────┐
│ ER建模七步法 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 1. 识别实体 → 找出业务中的关键对象 │
│ ↓ │
│ 2. 识别关系 → 确定实体间的关联 │
│ ↓ │
│ 3. 确定基数 → 1:1, 1:N, M:N │
│ ↓ │
│ 4. 识别属性 → 描述实体的特征 │
│ ↓ │
│ 5. 确定主键 → 唯一标识每个实体 │
│ ↓ │
│ 6. 规范化 → 消除冗余和异常 │
│ ↓ │
│ 7. 验证模型 → 用业务场景检验 │
│ │
└─────────────────────────────────────────────────────────────┘
维度建模是数据仓库设计的标准方法，由Ralph Kimball提出。
┌─────────────────────────────────────────────────────────────┐
│ 维度模型结构 │
├─────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────┐ │
│ │ 时间维度 │ │
│ │ ─────── │ │
│ │ 日期 │ │
│ │ 月份 │ │
│ │ 季度 │ │
│ └────┬─────┘ │
│ │ │
│ ┌──────────┐ ┌─────┴─────┐ ┌──────────┐ │
│ │ 产品维度 │──────│ 销售事实 │──────│ 地区维度 │ │
│ │ ─────── │ │ ─────── │ │ ─────── │ │
│ │ 产品ID │ │ 销售量 │ │ 城市 │ │
│ │ 产品名 │ │ 销售额 │ │ 省份 │ │
│ │ 类别 │ │ 成本 │ │ 国家 │ │
│ │ 品牌 │ │ 利润 │ └──────────┘ │
│ └──────────┘ │ 折扣 │ │
│ └──────────┘ │
│ │
│ ★ 事实表 = 可度量数据 │
│ □ 维度表 = 分析上下文 │
│ │
└─────────────────────────────────────────────────────────────┘
-- 星型模型：维度表直接连接事实表（推荐）
-- 查询简单，性能好
-- 雪花模型：维度表进一步规范化
-- 节省存储，但JOIN更多
/* 星型模型示例 */
-- 事实表
CREATE TABLE fact_sales (
date_key INT,
product_key INT,
store_key INT,
quantity INT,
amount DECIMAL(10,2),
cost DECIMAL(10,2)
);
-- 维度表（非规范化）
CREATE TABLE dim_product (
product_key INT PRIMARY KEY,
product_name VARCHAR(100),
category_name VARCHAR(50), -- 直接存储，不归一化
brand_name VARCHAR(50) -- 直接存储
);
/* 雪花模型示例 */
-- 维度表（规范化）
CREATE TABLE dim_product (
product_key INT PRIMARY KEY,
product_name VARCHAR(100),
category_key INT,
brand_key INT
);
CREATE TABLE dim_category (
category_key INT PRIMARY KEY,
category_name VARCHAR(50)
);
-- SCD Type 2：保留历史记录，添加时间戳
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY,
customer_id VARCHAR(20), -- 业务键
customer_name VARCHAR(100),
address VARCHAR(200),
-- SCD Type 2 字段
effective_date DATE, -- 生效日期
expiration_date DATE, -- 失效日期 (9999-12-31表示当前)
is_current BOOLEAN, -- 是否当前记录
version INT -- 版本号
);
-- 示例：客户地址变更
-- customer_id='C001', 原地址='北京', 新地址='上海'
-- 1. 更新旧记录
UPDATE dim_customer
SET expiration_date = CURRENT_DATE - 1,
is_current = FALSE
WHERE customer_id = 'C001' AND is_current = TRUE;
-- 2. 插入新记录
INSERT INTO dim_customer VALUES
('C001', '张三', '上海', CURRENT_DATE, '9999-12-31', TRUE, 2);
┌─────────────────────────────────────────────────────────────┐
│ DDD聚合与数据存储 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 聚合根（Order） │
│ ┌─────────────────────────────────────────┐ │
│ │ Order │ │
│ │ ├── orderId (ID) │ │
│ │ ├── customerId │ │
│ │ ├── orderDate │ │
│ │ ├── status │ │
│ │ ├── totalAmount │ │
│ │ ├── OrderItems [实体集合] │ │
│ │ │ ├── OrderItem │ │
│ │ │ │ ├── productId │ │
│ │ │ │ ├── quantity │ │
│ │ │ │ └── unitPrice │ │
│ │ └── ShippingAddress [值对象] │ │
│ │ ├── street │ │
│ │ ├── city │ │
│ │ └── zipCode │ │
│ └─────────────────────────────────────────┘ │
│ │
│ 存储映射： │
│ • Order → orders表 │
│ • OrderItems → order_items表 │
│ • ShippingAddress → JSON列 或 嵌套表 │
│ │
└─────────────────────────────────────────────────────────────┘
// 聚合修改后发布领域事件
@Entity
public class Order {
@Id
private String orderId;
private OrderStatus status;
@ElementCollection
private List<OrderItem> items;
@Transient
private List<DomainEvent> domainEvents = new ArrayList<>();
public void confirm() {
if (this.status != OrderStatus.PENDING) {
throw new IllegalStateException("只能确认待处理订单");
}
this.status = OrderStatus.CONFIRMED;
// 发布领域事件
domainEvents.add(new OrderConfirmedEvent(
this.orderId,
LocalDateTime.now(),
calculateTotal()
));
}
}
// 事件存储表
domain_events (
event_id UUID PRIMARY KEY,
aggregate_type VARCHAR(50), -- 'Order'
aggregate_id VARCHAR(50), -- orderId
event_type VARCHAR(100), -- 'OrderConfirmedEvent'
event_data JSON, -- 事件详情
version INT, -- 聚合版本
occurred_at TIMESTAMP
);
-- 数据库对象命名
-- 表名：小写，复数，下划线分隔
users, order_items, product_categories
-- 字段名：小写，下划线分隔
user_id, created_at, is_active
-- 索引名：表名_字段名_idx
CREATE INDEX users_email_idx ON users(email);
-- 外键名：fk_从表_主表_字段
ALTER TABLE order_items
ADD CONSTRAINT fk_order_items_orders
FOREIGN KEY (order_id) REFERENCES orders(id);
-- 约束名：chk_表名_规则描述
ALTER TABLE products
ADD CONSTRAINT chk_products_price
CHECK (price >= 0);
-- 推荐所有表包含的审计字段
CREATE TABLE example_table (
-- 主键
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-- 业务字段...
name VARCHAR(100) NOT NULL,
-- 标准审计字段
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
created_by VARCHAR(50), -- 创建者
updated_by VARCHAR(50), -- 更新者
-- 软删除（推荐）
deleted_at TIMESTAMP NULL DEFAULT NULL,
is_deleted BOOLEAN DEFAULT FALSE,
-- 乐观锁
version INT DEFAULT 0,
-- 租户隔离（多租户场景）
tenant_id VARCHAR(50),
INDEX idx_created_at (created_at),
INDEX idx_tenant_id (tenant_id)
) ENGINE=InnoDB;
-- 1. 主键索引（自动创建）
PRIMARY KEY (id)
-- 2. 唯一索引
UNIQUE INDEX uk_users_email (email)
-- 3. 复合索引（遵循最左前缀原则）
-- 查询条件通常是 (user_id, status, created_at)
INDEX idx_user_status_created (user_id, status, created_at)
-- 4. 覆盖索引（包含查询所需所有字段）
INDEX idx_covering (user_id, status, amount)
INCLUDE (created_at) -- PostgreSQL语法
-- 5. 部分索引（过滤条件索引）
CREATE INDEX idx_active_users
ON users(email)
WHERE is_active = TRUE;
-- 6. 函数索引
CREATE INDEX idx_lower_email
ON users(LOWER(email));
┌─────────────────────────────────────────────────────────────┐
│ 数据类型选择指南 │
├─────────────────┬─────────────────┬─────────────────────────┤
│ 数据类型 │ 推荐用法 │ 注意事项 │
├─────────────────┼─────────────────┼─────────────────────────┤
│ INT/BIGINT │ 自增ID、计数 │ BIGINT应对大数据量 │
│ VARCHAR(n) │ 变长字符串 │ n设置合理，不要太长 │
│ CHAR(n) │ 固定长度 │ 手机号、身份证号 │
│ TEXT │ 长文本 │ 有独立存储，避免排序 │
│ DECIMAL │ 精确货币 │ 避免FLOAT/DOUBLE │
│ TIMESTAMP │ 时间戳 │ 使用时区-aware │
│ DATE │ 日期 │ 生日、纪念日 │
│ JSON │ 半结构化数据 │ 查询性能较低 │
│ ENUM │ 有限选项 │ 变更需ALTER表 │
│ UUID │ 分布式ID │ 考虑有序UUID │
└─────────────────┴─────────────────┴─────────────────────────┘
❌ 反模式：一个表包含所有信息
users_extended (
id, name, email,
-- 地址信息
address_line1, address_line2, city, state, zip,
-- 偏好设置
pref_theme, pref_language, pref_notifications,
-- 社交账号
wechat_id, weibo_id, github_id,
-- 统计数据
login_count, last_login_ip, total_orders, total_spent,
-- 扩展字段（EAV）
custom_field_1, custom_field_2, custom_field_3
)
✅ 正确做法：拆分为多个相关表
users (id, name, email, created_at)
user_addresses (id, user_id, type, address...)
user_preferences (user_id, theme, language...)
user_social_accounts (user_id, provider, account_id)
user_stats (user_id, login_count, total_orders...)
❌ 反模式：用多个列存储同类数据
contacts (
id,
phone1, phone2, phone3, -- 最多3个电话
email1, email2 -- 最多2个邮箱
)
✅ 正确做法：使用关联表
contacts (id, name)
contact_phones (contact_id, phone_number, type, is_primary)
contact_emails (contact_id, email, type, is_primary)
❌ 反模式：每年创建新表
sales_2022, sales_2023, sales_2024
✅ 正确做法：单表 + 分区
sales (
id, sale_date, amount, ...,
INDEX idx_sale_date (sale_date)
)
-- 按年分区（数据库原生支持）
PARTITION BY RANGE (YEAR(sale_date)) (...)
✅ 或者使用分表策略（应用层处理）
-- 逻辑上还是sales表，物理上分片
❌ 反模式：模糊的分组条件
-- 查询：找出每个部门的最高工资员工
SELECT department, MAX(salary)
FROM employees
GROUP BY department;
-- 问题：不知道是谁的工资
✅ 正确做法：使用窗口函数或子查询
SELECT department, employee_name, salary
FROM (
SELECT *,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees
) ranked
WHERE rank = 1;
❌ 反模式：邻接表处理无限层级
categories (
id, name, parent_id
)
-- 查询所有子树需要递归，性能差
✅ 正确做法：闭包表或路径枚举
-- 闭包表
category_closure (
ancestor_id, -- 祖先
descendant_id, -- 后代
path_length -- 距离
)
-- 路径枚举
categories (
id, name,
path VARCHAR(500) -- '1/5/12/34/'
)
┌─────────────────────────────────────────────────────────────┐
│ 电商核心数据模型 │
├─────────────────────────────────────────────────────────────┤
│ │
│ ┌────────────┐ ┌────────────┐ ┌────────────┐ │
│ │ 用户 │ │ 商品 │ │ 订单 │ │
│ │ (users) │ │ (products) │ │ (orders) │ │
│ │ ──────── │ │ ──────── │ │ ──────── │ │
│ │ user_id │ │ product_id │ │ order_id │ │
│ │ username │ │ name │ │ user_id │──┐ │
│ │ email │◄─┤ price │ │ status │ │ │
│ │ phone │ │ stock │ │ total_amt │ │ │
│ └────────────┘ │ seller_id │◄─┤ pay_time │ │ │
│ │ └────────────┘ └─────┬──────┘ │ │
│ │ │ │ │ │
│ ▼ ▼ ▼ │ │
│ ┌────────────┐ ┌────────────┐ ┌───────────┴────┐ │
│ │ 收货地址 │ │ 商品分类 │ │ 订单项 │ │
│ │(addresses) │ │(categories)│ │ (order_items) │ │
│ └────────────┘ └────────────┘ │ ─────────── │ │
│ │ item_id │ │
│ ┌────────────┐ │ order_id │────┘
│ │ 支付 │ │ product_id │────┐
│ │ (payments) │ │ quantity │ │
│ │ ──────── │ │ unit_price │ │
│ │ pay_id │◄─────────────────────┤ subtotal │ │
│ │ order_id │ └────────────────┘ │
│ │ amount │ │
│ │ status │ │
│ └────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
// 社交图谱 - 图数据库建模
// 用户节点
(:User {
userId: "u123456",
username: "zhangsan",
createdAt: 2024-01-01,
profile: {...}
})
// 关系
(u1:User)-[:FOLLOWS {since: "2024-01-15"}]->(u2:User)
(u1:User)-[:FRIEND {since: "2024-02-01", type: "close"}]->(u3:User)
(u:User)-[:POSTED {at: "2024-03-01"}]->(p:Post)
(p:Post)-[:HAS_TAG]->(t:Tag {name: "旅游"})
// 查询：朋友的朋友（二度关系）
MATCH (me:User {userId: "u123456"})-[:FRIEND]-(friend)-[:FRIEND]-(fof)
WHERE fof <> me
RETURN fof, count(*) as mutualFriends
ORDER BY mutualFriends DESC
LIMIT 10
-- 时序数据库（TimescaleDB）设计
-- 超表：自动分区的时间序列表
CREATE TABLE sensor_readings (
time TIMESTAMPTZ NOT NULL,
device_id TEXT NOT NULL,
temperature DOUBLE PRECISION,
humidity DOUBLE PRECISION,
pressure DOUBLE PRECISION,
location GEOGRAPHY(POINT)
);
-- 转换为超表，按时间自动分区
SELECT create_hypertable('sensor_readings', 'time',
chunk_time_interval => INTERVAL '1 day');
-- 自动压缩历史数据
ALTER TABLE sensor_readings
SET (timescaledb.compress,
timescaledb.compress_segmentby = 'device_id');
-- 保留策略：删除30天前的数据
SELECT add_retention_policy('sensor_readings', INTERVAL '30 days');
┌─────────────────────────────────────────────────────────────┐
│ 数据建模工具推荐 │
├──────────────────┬──────────────────────────────────────────┤
│ 工具类型 │ 推荐工具 │
├──────────────────┼──────────────────────────────────────────┤
│ 专业建模工具 │ PowerDesigner, ER/Studio, ERwin │
│ 开源建模工具 │ MySQL Workbench, pgModeler, DBeaver │
│ 在线协作工具 │ dbdiagram.io, DrawSQL, dbdocs.io │
│ 代码驱动建模 │ JDL Studio, jOOQ │
│ 版本控制 │ Liquibase, Flyway, Sqitch │
│ 文档生成 │ SchemaSpy, dbdocs │
└──────────────────┴──────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────┐
│ 推荐学习资源 │
├──────────────────┬──────────────────────────────────────────┤
│ 书籍 │ │
│ ─────────────── │ 《数据库系统概念》(Silberschatz) │
│ │ 《数据密集型应用系统设计》(Martin Kleppmann)│
│ │ 《SQL反模式》(Bill Karwin) │
│ │ 《维度建模权威指南》(Kimball) │
├──────────────────┼──────────────────────────────────────────┤
│ 在线资源 │ │
│ ─────────────── │ Use The Index, Luke (索引优化) │
│ │ MongoDB University │
│ │ Cassandra Data Modeling Course │
└──────────────────┴──────────────────────────────────────────┘
数据模型设计是软件架构中最基础也最重要的工作之一。优秀的数据模型应该：
核心原则：
决策检查清单：
数据模型设计不是一蹴而就的工作，而是一个随着业务发展持续演进的过程。建立良好的变更管理机制，保持对数据模型的持续关注，才能确保系统长期健康发展。
最后更新：2026年4月6日
字数统计：约12,000字