数据建模完全指南：ER图、关系类型与数据字典实战教程

Data Modeling by danhvb/my-ba-skills

GitHub

安装命令

npx skills add https://github.com/danhvb/my-ba-skills --skill 'Data Modeling'

数据库业务分析数据处理

🇨🇳中文介绍

数据建模技能

目的

创建清晰的数据模型，以记录系统设计和开发所需的数据结构、关系和业务规则。

使用时机

设计新的数据库结构
为分析而记录现有数据
在功能需求规格说明中定义数据需求
系统间的集成设计
数据迁移规划

数据建模概念

实体关系图

实体：我们存储数据的对象/事物（例如，Customer、Order、Product）属性：实体的属性（例如，customer_name、order_date）关系：实体之间如何相互关联

关系类型

一对一 示例：User ↔ UserProfile

一个用户恰好有一个个人资料
一个个人资料属于恰好一个用户

一对多 示例：Customer → Orders

一个客户可以拥有多个订单
一个订单属于一个客户

多对多 示例：Products ↔ Categories

一个产品可以属于多个类别
一个类别可以包含多个产品
需要连接表/桥接表

基数表示法

    ||──────|| : 有且仅有一个
    ||──────<  : 一对多
    >──────<   : 多对多
    o|──────<  : 零个或一个对多个
    ||──────o< : 一个对零个或多个

ERD 示例

电子商务 ERD

erDiagram
    CUSTOMER ||--o{ ORDER : places
    CUSTOMER {
        uuid customer_id PK
        string email UK
        string first_name
        string last_name
        string phone
        datetime created_at
    }
    ORDER ||--|{ ORDER_ITEM : contains
    ORDER {
        uuid order_id PK
        uuid customer_id FK
        string order_number UK
        decimal subtotal
        decimal tax
        decimal shipping
        decimal total
        string status
        datetime order_date
    }
    ORDER_ITEM {
        uuid item_id PK
        uuid order_id FK
        uuid product_id FK
        int quantity
        decimal unit_price
        decimal total_price
    }
    PRODUCT ||--o{ ORDER_ITEM : "included in"
    PRODUCT {
        uuid product_id PK
        string sku UK
        string name
        text description
        decimal price
        int stock_quantity
        string status
    }
    PRODUCT }|--|| CATEGORY : "belongs to"
    CATEGORY {
        uuid category_id PK
        string name
        uuid parent_id FK
    }

CRM ERD

erDiagram
    ACCOUNT ||--o{ CONTACT : has
    ACCOUNT ||--o{ OPPORTUNITY : has
    ACCOUNT {
        uuid account_id PK
        string name
        string industry
        string website
        int employee_count
        decimal annual_revenue
        uuid owner_id FK
    }
    CONTACT {
        uuid contact_id PK
        uuid account_id FK
        string first_name
        string last_name
        string email UK
        string phone
        string title
    }
    OPPORTUNITY }o--|| CONTACT : "primary contact"
    OPPORTUNITY {
        uuid opportunity_id PK
        uuid account_id FK
        uuid contact_id FK
        string name
        string stage
        decimal amount
        date close_date
        int probability
        uuid owner_id FK
    }
    LEAD {
        uuid lead_id PK
        string first_name
        string last_name
        string email
        string company
        string source
        int score
        string status
    }

数据字典

模板

属性	数据类型	大小	必填	默认值	描述	验证规则
customer_id	UUID	-	是	自动生成	唯一标识符	UUID 格式
email	VARCHAR	255	是	-	客户邮箱	有效邮箱地址
status	ENUM	-	是	'active'	账户状态	active, inactive, suspended

示例：订单实体

属性	类型	大小	必填	默认值	描述	规则
order_id	UUID	-	是	自动	主键	唯一
order_number	VARCHAR	20	是	生成	显示编号	格式：ORD-YYYYMMDD-XXXX
customer_id	UUID	-	是	-	外键指向客户	必须存在
order_date	DATETIME	-	是	NOW()	下单时间	不能是未来时间

规范化

第一范式

消除重复组
每个单元格包含单个值
每条记录都是唯一的

❌ 不良示例：customer_phones = "123-456, 789-012" ✅ 良好示例：使用 customer_id 外键的独立电话表

第二范式

满足第一范式
没有部分依赖（所有非键属性都依赖于整个主键）

第三范式

满足第二范式
没有传递依赖（非键属性不依赖于其他非键属性）

❌ 不良示例：Order 表包含 customer_email（依赖于 customer_id，而非 order） ✅ 良好示例：通过 Customer 表连接获取 customer_email

领域特定数据模式

电子商务

具有变体的产品（每个变体一个 SKU）
分层类别
购物车 → 订单转换
地址规范化
价格历史跟踪

企业资源规划

会计科目表结构
多公司数据隔离
主数据（客户、供应商、产品）
带有日记账的事务表
审计追踪

客户关系管理

潜在客户 → 联系人 → 客户转换
活动日志记录（通话、邮件、会议）
销售机会 → 报价 → 订单流程
营销活动 → 成员 → 响应跟踪

客户数据平台

客户身份解析
事件/行为数据（时间序列）
档案属性（统一）
细分群体成员资格
同意跟踪

最佳实践

✅ 应该做：

使用一致的命名约定（snake_case）
包含审计字段（created_at、updated_at、created_by）
明确定义主键
记录外键关系
包含数据类型和约束
考虑软删除与硬删除
为数据增长做规划

❌ 不应该做：

存储计算值（除非出于性能考虑）
使用模糊的名称
跳过文档记录
忽略数据验证规则
忘记处理 NULL 值

工具

Figma：可视化 ERD 设计
Mermaid：文档中基于代码的图表
dbdiagram.io：快速创建 ERD
Lucidchart：专业图表

后续步骤

数据建模之后：

与技术团队一起评审
包含在功能需求规格说明文档中
创建迁移脚本
规划数据验证规则
基于数据模型设计 API 契约

参考资料

数据库规范化
实体关系建模
数据字典标准

每周安装次数

–

代码仓库

danhvb/my-ba-skills

首次出现

–

安全审计

Gen Agent Trust HubPass SocketPass SnykPass

🇺🇸English

Data Modeling Skill

Purpose

Create clear data models that document data structures, relationships, and business rules for system design and development.

When to Use

Designing new database structures
Documenting existing data for analysis
Defining data requirements in FRS
Integration design between systems
Data migration planning

Data Modeling Concepts

Entity Relationship Diagram (ERD)

Entities : Objects/things we store data about (e.g., Customer, Order, Product) Attributes : Properties of entities (e.g., customer_name, order_date) Relationships : How entities relate to each other

Relationship Types

One-to-One (1:1) Example: User ↔ UserProfile

One user has exactly one profile
One profile belongs to exactly one user

One-to-Many (1:N) Example: Customer → Orders

One customer can have many orders
One order belongs to one customer

Many-to-Many (M:N) Example: Products ↔ Categories

One product can be in many categories
One category can have many products
Requires junction/bridge table

Cardinality Notation (Crow's Foot)

广告位招租

在这里展示您的产品或服务

触达数万 AI 开发者，精准高效

联系我们

E-commerce ERD (Mermaid)

erDiagram
    CUSTOMER ||--o{ ORDER : places
    CUSTOMER {
        uuid customer_id PK
        string email UK
        string first_name
        string last_name
        string phone
        datetime created_at
    }
    ORDER ||--|{ ORDER_ITEM : contains
    ORDER {
        uuid order_id PK
        uuid customer_id FK
        string order_number UK
        decimal subtotal
        decimal tax
        decimal shipping
        decimal total
        string status
        datetime order_date
    }
    ORDER_ITEM {
        uuid item_id PK
        uuid order_id FK
        uuid product_id FK
        int quantity
        decimal unit_price
        decimal total_price
    }
    PRODUCT ||--o{ ORDER_ITEM : "included in"
    PRODUCT {
        uuid product_id PK
        string sku UK
        string name
        text description
        decimal price
        int stock_quantity
        string status
    }
    PRODUCT }|--|| CATEGORY : "belongs to"
    CATEGORY {
        uuid category_id PK
        string name
        uuid parent_id FK
    }

erDiagram
    ACCOUNT ||--o{ CONTACT : has
    ACCOUNT ||--o{ OPPORTUNITY : has
    ACCOUNT {
        uuid account_id PK
        string name
        string industry
        string website
        int employee_count
        decimal annual_revenue
        uuid owner_id FK
    }
    CONTACT {
        uuid contact_id PK
        uuid account_id FK
        string first_name
        string last_name
        string email UK
        string phone
        string title
    }
    OPPORTUNITY }o--|| CONTACT : "primary contact"
    OPPORTUNITY {
        uuid opportunity_id PK
        uuid account_id FK
        uuid contact_id FK
        string name
        string stage
        decimal amount
        date close_date
        int probability
        uuid owner_id FK
    }
    LEAD {
        uuid lead_id PK
        string first_name
        string last_name
        string email
        string company
        string source
        int score
        string status
    }

Attribute	Data Type	Size	Required	Default	Description	Validation
customer_id	UUID	-	Yes	Auto-gen	Unique identifier	UUID format
email	VARCHAR	255	Yes	-	Customer email	Valid email
status	ENUM	-	Yes	'active'	Account status	active, inactive, suspended

Example: Order Entity

Attribute	Type	Size	Required	Default	Description	Rules
order_id	UUID	-	Yes	Auto	Primary key	Unique
order_number	VARCHAR	20	Yes	Generated	Display number	Format: ORD-YYYYMMDD-XXXX
customer_id	UUID	-	Yes	-	FK to Customer	Must exist
order_date	DATETIME	-	Yes	NOW()	When order placed	Cannot be future
status	ENUM	-	Yes	'pending'	Order status	pending, processing, shipped, delivered, cancelled
subtotal	DECIMAL	10,2	Yes	0.00	Sum of items	>= 0
tax	DECIMAL	10,2	Yes	0.00	Calculated tax	>= 0
shipping_cost	DECIMAL	10,2	Yes	0.00	Shipping fee	>= 0
total	DECIMAL	10,2	Yes	-	Final total	= subtotal + tax + shipping
shipping_address	JSON	-	Yes	-	Delivery address	Valid address
billing_address	JSON	-	Yes	-	Billing address	Valid address
notes	TEXT	-	No	NULL	Order notes	Max 2000 chars
created_at	DATETIME	-	Yes	NOW()	Record created	Immutable
updated_at	DATETIME	-	Yes	NOW()	Last modified	Auto-update

First Normal Form (1NF)

Eliminate repeating groups
Each cell contains single value
Each record is unique

❌ Bad: customer_phones = "123-456, 789-012" ✅ Good: Separate phone table with customer_id FK

Second Normal Form (2NF)

Meet 1NF
No partial dependencies (all non-key attributes depend on entire primary key)

Third Normal Form (3NF)

Meet 2NF
No transitive dependencies (non-key attributes don't depend on other non-key attributes)

❌ Bad: Order has customer_email (depends on customer_id, not order) ✅ Good: Get customer_email via Customer table join

Domain-Specific Data Patterns

Products with variants (SKU per variant)
Hierarchical categories
Shopping cart → Order transition
Address normalization
Price history tracking

Chart of Accounts structure
Multi-company data isolation
Master data (customer, vendor, product)
Transaction tables with journals
Audit trails

Lead → Contact → Account conversion
Activity logging (calls, emails, meetings)
Opportunity → Quote → Order pipeline
Campaign → Member → Response tracking

Customer identity resolution
Event/behavioral data (time-series)
Profile attributes (unified)
Segment membership
Consent tracking

Use consistent naming conventions (snake_case)
Include audit fields (created_at, updated_at, created_by)
Define primary keys explicitly
Document foreign key relationships
Include data types and constraints
Consider soft deletes vs. hard deletes
Plan for data growth

Store calculated values (unless for performance)
Use ambiguous names
Skip documentation
Ignore data validation rules
Forget about NULL handling

Figma : Visual ERD design
Mermaid : Code-based diagrams in docs
dbdiagram.io : Quick ERD creation
Lucidchart : Professional diagrams

After data modeling:

Review with technical team
Include in FRS documentation
Create migration scripts
Plan data validation rules
Design API contracts based on data model

Database Normalization (1NF, 2NF, 3NF)
Entity Relationship Modeling
Data Dictionary Standards

智能OCR文字识别工具 - 支持100+语言，高精度提取图片/PDF/手写文本

933 周安装

数据建模完全指南：ER图、关系类型与数据字典实战教程

🇨🇳中文介绍

数据建模技能

目的

使用时机

数据建模概念

实体关系图

关系类型

基数表示法

ERD 示例

电子商务 ERD

CRM ERD

数据字典

模板

示例：订单实体

规范化

第一范式

第二范式

第三范式

领域特定数据模式

电子商务

企业资源规划

客户关系管理

客户数据平台

最佳实践

工具

后续步骤

参考资料

🇺🇸English

Data Modeling Skill

Purpose

When to Use

Data Modeling Concepts

Entity Relationship Diagram (ERD)

Relationship Types

Cardinality Notation (Crow's Foot)

相关 Skills

ERD Examples

E-commerce ERD (Mermaid)

CRM ERD

Data Dictionary

Template

Example: Order Entity

Normalization

First Normal Form (1NF)

Second Normal Form (2NF)

Third Normal Form (3NF)

Domain-Specific Data Patterns

E-commerce

ERP

CRM

CDP

Best Practices

Tools

Next Steps

References

最新 Skills