name: data-modeling description: 用于设计数据模型、数据库模式或选择建模方法时使用。涵盖维度建模、星型模式、数据保险库、实体-关系设计和模式演进。 allowed-tools: Read, Glob, Grep
数据建模
操作数据库、数据仓库和分析系统的数据建模技术综合指南。
何时使用此技能
- 设计数据库模式
- 选择建模方法
- 构建数据仓库
- 规划模式演进
- 理解数据模型中的权衡
- 为分析vs操作设计
数据建模基础
数据模型类型
数据模型类别:
1. 概念模型
目的: 业务理解
受众: 业务利益相关者
内容: 实体、关系、业务规则
细节: 高层次，无实现细节
2. 逻辑模型
目的: 结构定义
受众: 数据架构师
内容: 表、列、键、关系
细节: 数据库无关设计
3. 物理模型
目的: 实现
受众: 数据库工程师
内容: 索引、分区、存储
细节: 数据库特定优化
模型演进:
┌─────────────────────────────────────────────────────────────┐
│ │
│ 业务需求 概念模型 逻辑模型 物理模型 │
│ Business Conceptual Logical Physical │
│ Requirements ──► Model ──► Model ──► Model │
│ │
│ "客户下订单" 客户 ─────► customers customers │
│ 订单 ├── id ├── id PK │
│ └──< 下单 ├── name ├── name │
│ └── email ├── email │
│ └── idx_* │
│ │
└─────────────────────────────────────────────────────────────┘
操作型 vs 分析型
操作型 (OLTP) vs 分析型 (OLAP):
┌────────────────────────────────────────────────────────────┐
│ OLTP │
│ (在线事务处理) │
├────────────────────────────────────────────────────────────┤
│ 目的: 运行业务 │
│ 负载: 多个小事务 │
│ 模型: 规范化 (3NF) │
│ 优化: 写入性能，一致性 │
│ 用户: 应用，服务 │
│ 示例: 插入新订单，更新库存 │
└────────────────────────────────────────────────────────────┘
┌────────────────────────────────────────────────────────────┐
│ OLAP │
│ (在线分析处理) │
├────────────────────────────────────────────────────────────┤
│ 目的: 分析业务 │
│ 负载: 少量复杂查询 │
│ 模型: 反规范化 (星型/雪花型) │
│ 优化: 读取性能，聚合 │
│ 用户: 分析师，BI工具 │
│ 示例: 按区域、月汇总销售额 │
└────────────────────────────────────────────────────────────┘
何时使用:
├── OLTP: 面向用户的应用程序，实时操作
├── OLAP: 报告，仪表板，机器学习
└── 混合: 某些系统需要两者 (HTAP)
规范化
范式
数据库规范化:
1NF (第一范式):
├── 消除重复组
├── 为相关数据创建单独表
├── 用主键标识每一行
└── 每个单元格包含单一值
示例 (SQL Server - PascalCase):
❌ Orders (Id, Item1, Item2, Item3)
✓ Orders (Id, ...) + OrderItems (OrderId, ItemId)
2NF (第二范式):
├── 满足1NF要求
├── 移除部分依赖
└── 非键列依赖于整个主键
示例 (SQL Server - PascalCase):
❌ OrderItems (OrderId, ProductId, ProductName)
✓ OrderItems (OrderId, ProductId) + Products (ProductId, ProductName)
3NF (第三范式):
├── 满足2NF要求
├── 移除传递依赖
└── 非键列仅依赖于主键
示例 (SQL Server - PascalCase):
❌ Orders (Id, CustomerId, CustomerCity)
✓ Orders (Id, CustomerId) + Customers (Id, City)
BCNF (Boyce-Codd范式):
├── 满足3NF要求
├── 每个决定子都是候选键
└── 处理多值依赖
更高范式 (4NF, 5NF):
├── 处理复杂多值依赖
├── 实践中很少使用
└── 可能导致过度规范化
何时反规范化
规范化权衡:
规范化 (3NF):
├── 优点: 数据完整性，无冗余，存储较小
├── 缺点: 复杂连接，读取较慢
└── 使用: OLTP，写密集型负载
反规范化:
├── 优点: 读取更快，查询更简单
├── 缺点: 冗余，更新异常，存储较大
└── 使用: OLAP，读密集型负载
反规范化触发点:
1. 读取性能关键
2. 写入频率低
3. 数据变更不频繁
4. 查询频繁连接相同表
5. 聚合常见
受控反规范化模式:
┌─────────────────────────────────────────────────────────────┐
│ 模式: 汇总表 │
│ 保持规范化 + 物化聚合 │
│ │
│ orders ──┐ │
│ items ──┼──► daily_sales_summary (物化) │
│ products─┘ │
│ │
│ 两者兼得: 写入规范化，从汇总读取 │
└─────────────────────────────────────────────────────────────┘
维度建模
星型模式
星型模式:
中心事实表周围围绕维度表。
┌──────────────┐
│ dim_date │
│──────────────│
│ date_key PK │
│ date │
│ month │
│ quarter │
│ year │
└──────┬───────┘
│
┌──────────────┐ ┌──────┴───────┐ ┌──────────────┐
│ dim_product │ │ fact_sales │ │ dim_customer │
│──────────────│ │──────────────│ │──────────────│
│ product_key PK│◄──│ date_key FK │───►│ customer_key PK│
│ product_name │ │ product_key FK│ │ customer_name│
│ category │ │ customer_key FK│ │ segment │
│ brand │ │ store_key FK │ │ region │
└──────────────┘ │──────────────│ └──────────────┘
│ quantity │
│ revenue │ │
│ cost │ │
└──────┬───────┘ ┌──────┴───────┐
│ │ dim_store │
└───────────►│──────────────│
│ store_key PK│
│ store_name │
│ city │
│ state │
└──────────────┘
星型模式优点:
├── 简单查询 (少连接)
├── 优化聚合
├── 对业务用户直观
├── 与BI工具配合良好
└── 可预测查询性能
事实表类型:
├── 事务型: 每个事件一行 (销售，点击)
├── 周期性快照: 每个周期一行 (日余额)
├── 累积快照: 跟踪进度 (订单生命周期)
└── 无度量事实: 无度量事件 (出席)
雪花型模式
雪花型模式:
规范化维度 (维度有子维度)。
┌──────────────┐
│ dim_date │
└──────┬───────┘
│
┌──────────────┐ ┌──────┴───────┐ ┌──────────────┐
│ dim_category │◄───│ dim_product │ │ dim_customer │
└──────────────┘ └──────┬───────┘ └──────┬───────┘
│ │
┌──────────────────┼────────────────────┼──────┐
│ ┌──────┴───────┐ │ │
│ │ fact_sales │ │ │
│ └──────┬───────┘ │ │
│ │ │ │
│ ┌──────┴───────┐ ┌──────┴──────┐
│ │ dim_store │ │ dim_segment │
│ └──────┬───────┘ └─────────────┘
│ │
│ ┌──────┴───────┐
│ │ dim_city │
│ └──────────────┘
星型 vs 雪花型:
┌────────────────────────────────────────────────────────────┐
│ 因素 │ 星型 │ 雪花型 │
├─────────────────┼───────────────────┼──────────────────────┤
│ 查询简单性│ 更简单 │ 更复杂 │
│ 查询速度 │ 更快 (连接少) │ 更慢 │
│ 存储 │ 更多 (冗余) │ 更少 (规范化) │
│ 维护 │ 更容易 │ 更复杂 │
│ BI工具支持 │ 更好 │ 可能需要建模 │
└────────────────────────────────────────────────────────────┘
推荐: 优先星型模式，除非存储是关键问题。
缓慢变化维度
缓慢变化维度 (SCD):
如何处理维度随时间变化。
类型 0: 保留原始
└── 从不更新维度
└── 用于: 不应更改的属性
类型 1: 覆盖
└── 就地更新，丢失历史
└── 用于: 纠正，非历史属性
类型 2: 添加新行 (最常见)
┌─────────────────────────────────────────────────────────────┐
│ customer_key│ customer_id │ address │ valid_from │ valid_to │ current│
├─────────────┼─────────────┼──────────────┼────────────┼──────────┼────────┤
│ 1001 │ C123 │ 123 Oak St │ 2020-01-01 │ 2023-06-30│ false │
│ 1002 │ C123 │ 456 Pine Ave │ 2023-07-01 │ 9999-12-31│ true │
└─────────────────────────────────────────────────────────────┘
└── 新代理键，跟踪有效期间
└── 用于: 需要完整历史
类型 3: 添加新列
└── 先前和当前值列
└── 用于: 有限历史 (仅先前)
类型 4: 历史表
└── 主表中当前，历史在单独表
└── 用于: 频繁变更，大维度
类型 6: 混合 (1+2+3)
└── 结合方法以获得灵活性
└── 用于: 复杂需求
数据保险库
数据保险库架构
数据保险库建模:
企业数据仓库模式，用于敏捷性和可审计性。
组件:
┌─────────────────────────────────────────────────────────────┐
│ 数据保险库 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 中心 (业务键) │
│ ┌────────────────────┐ │
│ │ hub_customer │ │
│ │ ├── hub_key PK │ ← 代理键 │
│ │ ├── customer_id │ ← 业务键 │
│ │ ├── load_date │ ← 加载时间 │
│ │ └── source │ ← 来源 │
│ └────────────────────┘ │
│ │
│ 链接 (关系) │
│ ┌────────────────────┐ │
│ │ link_customer_order│ │
│ │ ├── link_key PK │ │
│ │ ├── hub_customer_fk│ │
│ │ ├── hub_order_fk │ │
│ │ ├── load_date │ │
│ │ └── source │ │
│ └────────────────────┘ │
│ │
│ 卫星 (描述性数据) │
│ ┌────────────────────┐ │
│ │ sat_customer_details│ │
│ │ ├── hub_customer_fk│ │
│ │ ├── load_date PK │ ← 部分PK │
│ │ ├── name │ ← 描述性属性 │
│ │ ├── email │ │
│ │ ├── hash_diff │ ← 变更检测 │
│ │ └── source │ │
│ └────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
数据保险库优点:
├── 完整审计跟踪 (所有变更跟踪)
├── 来源系统无关
├── 可并行加载
├── 优雅处理模式变更
└── 适合监管要求
数据保险库挑战:
├── 复杂查询 (多连接)
├── 需要呈现层
├── 学习曲线陡峭
├── 比星型模式存储更多
└── 不适合直接BI访问
何时使用数据保险库
数据保险库决策矩阵:
使用数据保险库当:
├── 多个来源系统，不同模式
├── 监管要求审计跟踪
├── 模式变更频繁
├── 历史准确性关键
├── 大型企业，复杂数据环境
└── 从收购集成数据
使用星型模式当:
├── 更简单报告需求
├── 单一来源系统
├── 快速查询性能优先
├── 业务用户直接查询
├── 规模较小，需求更简单
└── 需要快速价值实现
混合方法:
┌─────────────────────────────────────────────────────────────┐
│ │
│ 来源系统 ──► 数据保险库 ──► 星型模式 ──► BI工具 │
│ Source (原始保险库) (呈现层) (报告) │
│ Systems │
│ │
│ 优点: │
│ ├── 原始保险库: 完整历史，审计 │
│ ├── 星型模式: 查询性能 │
│ └── 解耦: 更改原始而不破坏报告 │
│ │
└─────────────────────────────────────────────────────────────┘
模式演进
模式演进模式
模式演进策略:
1. 添加性变更 (最安全)
├── 添加带默认值的新列
├── 添加新表
└── 添加新索引
安全: 不破坏现有查询
ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT NULL;
2. 非破坏性变更
├── 扩大列类型 (VARCHAR(50) → VARCHAR(100))
├── 移除NOT NULL约束
└── 添加可选外键
通常安全，通过测试验证
3. 破坏性变更 (危险)
├── 重命名列/表
├── 移除列
├── 更改列类型
└── 添加NOT NULL无默认值
需要迁移策略
迁移模式:
┌─────────────────────────────────────────────────────────────┐
│ 模式: 扩展-收缩 │
│ │
│ 阶段 1 (扩展): │
│ ├── 添加新列 (name_new) │
│ ├── 写入新旧列都写
│ └── 回填新列 │
│ │
│ 阶段 2 (迁移): │
│ ├── 更新读取器使用新列 │
│ └── 验证所有系统已迁移 │
│ │
│ 阶段 3 (收缩): │
│ └── 移除旧列 │
│ │
│ 时间线: 每阶段几天到几周 │
└─────────────────────────────────────────────────────────────┘
版本控制策略
模式版本控制:
1. 单一模式 (最常见)
├── 一个模式，原地演进
├── 使用迁移 (Flyway, Liquibase)
└── 所有应用使用相同版本
2. 多版本模式
├── 多个模式版本存在
├── 应用指定版本
└── 复杂但允许渐进迁移
示例: v1_users, v2_users
3. 事件溯源
├── 存储事件，而非状态
├── 重播到任何模式版本
└── 最灵活，最复杂
事件: UserCreated, UserNameChanged, UserDeleted
→ 重播以构建当前状态
迁移工具:
┌────────────────────────────────────────────────────────────┐
│ 工具 │ 语言 │ 方法 │
├─────────────┼────────────┼─────────────────────────────────┤
│ Flyway │ Java/SQL │ SQL迁移，版本化 │
│ Liquibase │ Java/XML │ 变更日志格式，回滚 │
│ Alembic │ Python │ SQLAlchemy集成 │
│ Entity Framework│ .NET │ 代码优先迁移 │
│ Prisma │ TypeScript │ 声明式模式 │
│ Atlas │ Go │ 声明式 + 命令式 │
└────────────────────────────────────────────────────────────┘
键和标识符
主键策略
主键选项:
1. 自然键
├── 业务标识符 (邮箱，SSN，ISBN)
├── 优点: 有意义，业务唯一
├── 缺点: 可能变更，隐私问题
└── 使用: 当真正不可变时
2. 代理键 (推荐)
├── 自增整数
├── 优点: 简单，性能好，稳定
├── 缺点: 无意义，数据库特定
└── 使用: 大多数操作型系统
3. UUIDs
├── 全局唯一标识符
├── 优点: 全局唯一，分布式生成
├── 缺点: 较大，性能较差
└── 使用: 分布式系统，外部暴露
4. ULIDs / Snowflake IDs
├── 时间可排序唯一标识符
├── 优点: 可排序，唯一，分布式
├── 缺点: 生成更复杂
└── 使用: 时间序列，事件系统
比较:
┌────────────────────────────────────────────────────────────┐
│ 类型 │ 大小 │ 可排序 │ 分布式 │ 示例 │
├───────────────┼─────────┼──────────┼─────────────┼─────────┤
│ 自增 │ 4-8 字节│ 是 │ 否 │ 12345 │
│ UUID v4 │ 16 字节 │ 否 │ 是 │ a1b2c...│
│ UUID v7 │ 16 字节 │ 是 │ 是 │ 0188... │
│ ULID │ 16 字节 │ 是 │ 是 │ 01H5... │
│ Snowflake │ 8 字节 │ 是 │ 是 │ 7890... │
└────────────────────────────────────────────────────────────┘
复合键
复合键:
多列主键。
使用案例:
├── 连接表 (多对多)
├── 时间序列分区
├── 多租户系统
└── 自然业务键
示例: 订单项 (SQL Server - PascalCase)
┌─────────────────────────────────────────────────────────────┐
│ OrderItems │
│ ├── OrderId PK, FK │
│ ├── LineNumber PK (与OrderId复合) │
│ ├── ProductId FK │
│ ├── Quantity │
│ └── Price │
└─────────────────────────────────────────────────────────────┘
权衡:
├── 优点: 强制唯一性，自然排序
├── 缺点: 复杂外键，ORM挑战
└── 替代: 代理键 + 唯一约束
最佳实践
数据建模最佳实践:
1. 首先理解用例
├── OLTP vs OLAP 需求
├── 预期查询模式
├── 写vs读比例
└── 增长预期
2. 从规范化开始，理性反规范化
├── 操作型从3NF开始
├── 仅为证明的性能需求反规范化
├── 记录反规范化决策
└── 先考虑物化视图
3. 使用一致命名 (数据库特定)
├── 单数表名 (User, 非 Users)
├── SQL Server: PascalCase (CustomerId, OrderDate, CreatedAt)
├── PostgreSQL: snake_case (customer_id, order_date, created_at)
├── 外键: {Entity}Id 或 {entity}_id 按约定
└── 避免保留字
4. 记录一切
├── 列描述
├── 关系含义
├── 业务规则
└── 变更历史
5. 为变更计划
├── 使用代理键
├── 设计模式演进
├── 版本化模式
└── 彻底测试迁移
6. 早期考虑性能
├── 索引策略
├── 分区需求
├── 数据类型 (最小足够)
└── 查询模式
反模式
数据建模反模式:
1. "一表统治所有"
❌ 单一表有许多可空列
✓ 适当实体分离
2. "实体-属性-值 (EAV)"
❌ 通用键-值表
✓ 适当列或JSON字段
3. "列中CSV"
❌ 逗号分隔值在一个字段
✓ 适当连接表
4. "过早反规范化"
❌ 未经测量反规范化
✓ 从规范化开始，用数据优化
5. "无外键"
❌ 跳过外键以 '性能'
✓ 使用外键保证完整性，考虑索引
6. "无意义名称"
❌ table1, field_a, temp_data
✓ 描述性，一致命名
相关技能
data-architecture- 数据湖，湖仓一体，数据网格stream-processing- 实时数据建模database-scaling- 缩放数据系统etl-elt-patterns- 数据转换