重要前提
安装AI Skills的关键前提是:必须科学上网,且开启TUN模式,这一点至关重要,直接决定安装能否顺利完成,在此郑重提醒三遍:科学上网,科学上网,科学上网。查看完整安装教程 →
data-modeling by melodic-software/claude-code-plugins
npx skills add https://github.com/melodic-software/claude-code-plugins --skill data-modeling在以下情况下使用此技能:
使用实体关系图(ERD)、数据字典和结构化数据模型来创建和记录数据结构。支持数据库设计和数据架构的概念、逻辑和物理建模层级。
数据建模 创建数据元素及其关系的可视化和结构化表示。它记录:
| 层级 | 目的 | 受众 | 详细程度 |
|---|---|---|---|
| 概念模型 | 业务概念 | 业务用户 | 实体、高层级关系 |
| 逻辑模型 |
广告位招租
在这里展示您的产品或服务
触达数万 AI 开发者,精准高效
| 数据结构 |
| 分析师、设计师 |
| 实体、属性、所有关系 |
| 物理模型 | 实现 | 开发人员、数据库管理员 | 表、列、类型、索引 |
业务概念的高层级视图:
与技术无关的数据结构:
特定于数据库的实现:
实体代表存储数据的对象。
┌─────────────────┐
│ CUSTOMER │
├─────────────────┤
│ customer_id PK │
│ name │
│ email │
│ created_at │
└─────────────────┘
实体类型:
| 类型 | 描述 | 示例 |
|---|---|---|
| 强实体 | 独立存在 | Customer, Product |
| 弱实体 | 依赖于另一个实体 | Order Line (依赖于 Order) |
| 关联实体 | 解决多对多关系 | Enrollment (Student-Course) |
| 类型 | 符号 | 描述 |
|---|---|---|
| 主键 (PK) | 下划线 / PK | 唯一标识符 |
| 外键 (FK) | FK | 对另一个实体的引用 |
| 必需 | * 或 NOT NULL | 必须有值 |
| 可选 | ○ 或 NULL | 可以为空 |
| 派生 | / | 从其他属性计算得出 |
| 复合 | {attrs} | 由子属性组成 |
| 多值 | [attr] | 可以有多个值 |
表示法风格:
| 风格 | 使用场景 |
|---|---|
| Chen | 学术、概念 |
| Crow's Foot | 行业标准 |
| UML | 软件设计 |
| IDEF1X | 政府、结构化 |
Crow's Foot 表示法:
| 符号 | 含义 |
|---|---|
── | 一个(强制) |
──○ | 零个或一个(可选) |
──< | 多个 |
──○< | 零个或多个 |
| 表示法 | 含义 | 示例 |
|---|---|---|
| 1:1 | 一对一 | Employee → Workstation |
| 1:M | 一对多 | Customer → Orders |
| M:N | 多对多 | Students ↔ Courses |
解读基数:
"一个 [实体 A] 拥有 [最小值]..[最大值] 个 [实体 B]"
示例:"一个 Customer 拥有 0..多个 Orders"
从业务需求中识别:
| 保留 | 排除 |
|---|---|
| 独立概念 | 属性(实体的特性) |
| 具有多个实例的对象 | 同义词(相同概念,不同名称) |
| 需要数据存储的对象 | 动作(动词,而非名词) |
## Entities
| Entity | Description | Example |
|--------|-------------|---------|
| Customer | Person or organization that purchases | John Smith, Acme Corp |
| Order | Purchase transaction | Order #12345 |
| Product | Item available for sale | Widget, Gadget |
对于每个实体,识别:
| 属性 | 类型 | 必需 | 备注 |
|---|---|---|---|
| customer_id | PK | 是 | 代理键 |
| Unique | 是 | 业务键 | |
| name | String | 是 | |
| phone | String | 否 | 可选 |
对于每对实体:
## Relationships
| Relationship | From | To | Cardinality | Description |
|--------------|------|-----|-------------|-------------|
| places | Customer | Order | 1:M | Customer places orders |
| contains | Order | Product | M:N | Order contains products |
M:N 关系需要关联实体:
Student ──M:N── Course
Becomes:
Student ──1:M── Enrollment ──M:1── Course
范式:
| 范式 | 规则 | 违反示例 |
|---|---|---|
| 1NF | 原子值,无重复组 | Phone1, Phone2, Phone3 |
| 2NF | 无部分依赖 | 非键依赖于复合键的一部分 |
| 3NF | 无传递依赖 | 非键依赖于非键 |
| BCNF | 每个决定因子都是候选键 | 候选键重叠 |
何时反规范化:
| 逻辑类型 | 物理类型 (PostgreSQL) | 物理类型 (SQL Server) |
|---|---|---|
| String(50) | VARCHAR(50) | NVARCHAR(50) |
| Integer | INTEGER | INT |
| Decimal(10,2) | NUMERIC(10,2) | DECIMAL(10,2) |
| Date | DATE | DATE |
| Timestamp | TIMESTAMP | DATETIME2 |
| Boolean | BOOLEAN | BIT |
erDiagram
CUSTOMER ||--o{ ORDER : places
ORDER ||--|{ ORDER_LINE : contains
PRODUCT ||--o{ ORDER_LINE : includes
CUSTOMER {
int customer_id PK
string name
string email UK
date created_at
}
ORDER {
int order_id PK
int customer_id FK
date order_date
decimal total
string status
}
ORDER_LINE {
int order_id PK,FK
int product_id PK,FK
int quantity
decimal unit_price
}
PRODUCT {
int product_id PK
string name
string sku UK
decimal price
int stock_qty
}
## Data Dictionary
### CUSTOMER
| Column | Type | Null | Key | Default | Description |
|--------|------|------|-----|---------|-------------|
| customer_id | INT | No | PK | AUTO | Unique identifier |
| name | VARCHAR(100) | No | | | Customer full name |
| email | VARCHAR(255) | No | UK | | Contact email |
| phone | VARCHAR(20) | Yes | | NULL | Contact phone |
| created_at | TIMESTAMP | No | | NOW() | Record creation |
**Indexes:**
- `pk_customer` (customer_id) - Primary
- `uk_customer_email` (email) - Unique
- `ix_customer_name` (name) - Search
**Constraints:**
- Email format validation (CHECK)
- Name length minimum 2 characters
data_model:
name: "E-Commerce"
version: "1.0"
date: "2025-01-15"
level: "logical" # conceptual, logical, physical
analyst: "data-modeler"
entities:
- name: "Customer"
type: "strong"
description: "Person or organization that makes purchases"
attributes:
- name: "customer_id"
type: "integer"
key: "primary"
required: true
generated: true
- name: "email"
type: "string"
length: 255
key: "unique"
required: true
- name: "name"
type: "string"
length: 100
required: true
- name: "Order"
type: "strong"
description: "Purchase transaction"
attributes:
- name: "order_id"
type: "integer"
key: "primary"
required: true
- name: "customer_id"
type: "integer"
key: "foreign"
references: "Customer.customer_id"
required: true
relationships:
- name: "places"
from: "Customer"
to: "Order"
cardinality: "1:M"
from_participation: "optional" # 0..1
to_participation: "mandatory" # 1..M
description: "Customer places orders"
constraints:
- entity: "Customer"
type: "check"
expression: "LENGTH(name) >= 2"
description: "Name minimum length"
indexes:
- entity: "Order"
name: "ix_order_date"
columns: ["order_date"]
purpose: "Date range queries"
## Data Model: E-Commerce
**Version:** 1.0
**Date:** [ISO Date]
**Level:** Logical
### Entity Summary
| Entity | Description | Key Relationships |
|--------|-------------|-------------------|
| Customer | Purchasers | Places Orders |
| Order | Transactions | Belongs to Customer, Contains Products |
| Product | Items for sale | Included in Orders |
| Order Line | Order details | Links Order to Product |
### Key Relationships
1. **Customer → Order (1:M)**
- One customer can place many orders
- Each order belongs to exactly one customer
2. **Order ↔ Product (M:N via Order Line)**
- An order can contain many products
- A product can appear in many orders
### Data Integrity Rules
1. Orders cannot exist without a customer
2. Order lines must reference valid order and product
3. Stock quantity cannot be negative
4. Email must be unique per customer
### Notes
- Consider partitioning Orders by date for large volumes
- Product price stored in Order Line for historical accuracy
erDiagram
PERSON ||--o| EMPLOYEE : "is a"
PERSON ||--o| CUSTOMER : "is a"
PERSON {
int person_id PK
string name
string email
}
EMPLOYEE {
int person_id PK,FK
date hire_date
decimal salary
}
CUSTOMER {
int person_id PK,FK
string company
decimal credit_limit
}
erDiagram
EMPLOYEE ||--o{ EMPLOYEE : "manages"
EMPLOYEE {
int employee_id PK
string name
int manager_id FK
}
erDiagram
ENTITY ||--o{ ENTITY_HISTORY : "has history"
ENTITY {
int id PK
string data
timestamp updated_at
}
ENTITY_HISTORY {
int history_id PK
int entity_id FK
string data
timestamp valid_from
timestamp valid_to
string changed_by
}
process-modeling - 数据的流程上下文journey-mapping - 客户数据接触点decision-analysis - 数据驱动的决策capability-mapping - 支持能力的数据每周安装量
65
代码仓库
GitHub 星标数
41
首次出现
Jan 21, 2026
安全审计
安装于
opencode58
codex57
gemini-cli56
github-copilot51
claude-code51
cursor47
Use this skill when:
Create and document data structures using Entity-Relationship Diagrams (ERDs), data dictionaries, and structured data models. Supports conceptual, logical, and physical modeling levels for database design and data architecture.
Data modeling creates visual and structured representations of data elements and their relationships. It documents:
| Level | Purpose | Audience | Detail |
|---|---|---|---|
| Conceptual | Business concepts | Business users | Entities, high-level relationships |
| Logical | Data structure | Analysts, designers | Entities, attributes, all relationships |
| Physical | Implementation | Developers, DBAs | Tables, columns, types, indexes |
High-level view of business concepts:
Technology-independent data structure:
Database-specific implementation:
An entity represents a thing about which data is stored.
┌─────────────────┐
│ CUSTOMER │
├─────────────────┤
│ customer_id PK │
│ name │
│ email │
│ created_at │
└─────────────────┘
Entity Types:
| Type | Description | Example |
|---|---|---|
| Strong | Independent existence | Customer, Product |
| Weak | Depends on another entity | Order Line (depends on Order) |
| Associative | Resolves M:N relationships | Enrollment (Student-Course) |
| Type | Symbol | Description |
|---|---|---|
| Primary Key (PK) | Underlined/PK | Unique identifier |
| Foreign Key (FK) | FK | Reference to another entity |
| Required | * or NOT NULL | Must have value |
| Optional | ○ or NULL | May be empty |
| Derived | / | Calculated from other attributes |
| Composite | {attrs} | Made of sub-attributes |
| Multi-valued | [attr] | Can have multiple values |
Notation Styles:
| Style | Used In |
|---|---|
| Chen | Academic, conceptual |
| Crow's Foot | Industry standard |
| UML | Software design |
| IDEF1X | Government, structured |
Crow's Foot Notation:
| Symbol | Meaning |
|---|---|
── | One (mandatory) |
──○ | Zero or one (optional) |
──< | Many |
──○< | Zero or many |
| Notation | Meaning | Example |
|---|---|---|
| 1:1 | One to one | Employee → Workstation |
| 1:M | One to many | Customer → Orders |
| M:N | Many to many | Students ↔ Courses |
Reading Cardinality:
"One [Entity A] has [min]..[max] [Entity B]"
Example: "One Customer has 0..many Orders"
From business requirements, identify:
| Keep | Exclude |
|---|---|
| Independent concepts | Attributes (properties of entities) |
| Things with multiple instances | Synonyms (same concept, different name) |
| Things requiring data storage | Actions (verbs, not nouns) |
## Entities
| Entity | Description | Example |
|--------|-------------|---------|
| Customer | Person or organization that purchases | John Smith, Acme Corp |
| Order | Purchase transaction | Order #12345 |
| Product | Item available for sale | Widget, Gadget |
For each entity, identify:
| Attribute | Type | Required | Notes |
|---|---|---|---|
| customer_id | PK | Yes | Surrogate key |
| Unique | Yes | Business key | |
| name | String | Yes | |
| phone | String | No | Optional |
For each pair of entities:
## Relationships
| Relationship | From | To | Cardinality | Description |
|--------------|------|-----|-------------|-------------|
| places | Customer | Order | 1:M | Customer places orders |
| contains | Order | Product | M:N | Order contains products |
M:N relationships require associative entities:
Student ──M:N── Course
Becomes:
Student ──1:M── Enrollment ──M:1── Course
Normal Forms:
| Form | Rule | Violation Example |
|---|---|---|
| 1NF | Atomic values, no repeating groups | Phone1, Phone2, Phone3 |
| 2NF | No partial dependencies | Non-key depends on part of composite key |
| 3NF | No transitive dependencies | Non-key depends on non-key |
| BCNF | Every determinant is a candidate key | Overlap in candidate keys |
When to Denormalize:
| Logical Type | Physical (PostgreSQL) | Physical (SQL Server) |
|---|---|---|
| String(50) | VARCHAR(50) | NVARCHAR(50) |
| Integer | INTEGER | INT |
| Decimal(10,2) | NUMERIC(10,2) | DECIMAL(10,2) |
| Date | DATE | DATE |
| Timestamp | TIMESTAMP | DATETIME2 |
| Boolean | BOOLEAN | BIT |
erDiagram
CUSTOMER ||--o{ ORDER : places
ORDER ||--|{ ORDER_LINE : contains
PRODUCT ||--o{ ORDER_LINE : includes
CUSTOMER {
int customer_id PK
string name
string email UK
date created_at
}
ORDER {
int order_id PK
int customer_id FK
date order_date
decimal total
string status
}
ORDER_LINE {
int order_id PK,FK
int product_id PK,FK
int quantity
decimal unit_price
}
PRODUCT {
int product_id PK
string name
string sku UK
decimal price
int stock_qty
}
## Data Dictionary
### CUSTOMER
| Column | Type | Null | Key | Default | Description |
|--------|------|------|-----|---------|-------------|
| customer_id | INT | No | PK | AUTO | Unique identifier |
| name | VARCHAR(100) | No | | | Customer full name |
| email | VARCHAR(255) | No | UK | | Contact email |
| phone | VARCHAR(20) | Yes | | NULL | Contact phone |
| created_at | TIMESTAMP | No | | NOW() | Record creation |
**Indexes:**
- `pk_customer` (customer_id) - Primary
- `uk_customer_email` (email) - Unique
- `ix_customer_name` (name) - Search
**Constraints:**
- Email format validation (CHECK)
- Name length minimum 2 characters
data_model:
name: "E-Commerce"
version: "1.0"
date: "2025-01-15"
level: "logical" # conceptual, logical, physical
analyst: "data-modeler"
entities:
- name: "Customer"
type: "strong"
description: "Person or organization that makes purchases"
attributes:
- name: "customer_id"
type: "integer"
key: "primary"
required: true
generated: true
- name: "email"
type: "string"
length: 255
key: "unique"
required: true
- name: "name"
type: "string"
length: 100
required: true
- name: "Order"
type: "strong"
description: "Purchase transaction"
attributes:
- name: "order_id"
type: "integer"
key: "primary"
required: true
- name: "customer_id"
type: "integer"
key: "foreign"
references: "Customer.customer_id"
required: true
relationships:
- name: "places"
from: "Customer"
to: "Order"
cardinality: "1:M"
from_participation: "optional" # 0..1
to_participation: "mandatory" # 1..M
description: "Customer places orders"
constraints:
- entity: "Customer"
type: "check"
expression: "LENGTH(name) >= 2"
description: "Name minimum length"
indexes:
- entity: "Order"
name: "ix_order_date"
columns: ["order_date"]
purpose: "Date range queries"
## Data Model: E-Commerce
**Version:** 1.0
**Date:** [ISO Date]
**Level:** Logical
### Entity Summary
| Entity | Description | Key Relationships |
|--------|-------------|-------------------|
| Customer | Purchasers | Places Orders |
| Order | Transactions | Belongs to Customer, Contains Products |
| Product | Items for sale | Included in Orders |
| Order Line | Order details | Links Order to Product |
### Key Relationships
1. **Customer → Order (1:M)**
- One customer can place many orders
- Each order belongs to exactly one customer
2. **Order ↔ Product (M:N via Order Line)**
- An order can contain many products
- A product can appear in many orders
### Data Integrity Rules
1. Orders cannot exist without a customer
2. Order lines must reference valid order and product
3. Stock quantity cannot be negative
4. Email must be unique per customer
### Notes
- Consider partitioning Orders by date for large volumes
- Product price stored in Order Line for historical accuracy
erDiagram
PERSON ||--o| EMPLOYEE : "is a"
PERSON ||--o| CUSTOMER : "is a"
PERSON {
int person_id PK
string name
string email
}
EMPLOYEE {
int person_id PK,FK
date hire_date
decimal salary
}
CUSTOMER {
int person_id PK,FK
string company
decimal credit_limit
}
erDiagram
EMPLOYEE ||--o{ EMPLOYEE : "manages"
EMPLOYEE {
int employee_id PK
string name
int manager_id FK
}
erDiagram
ENTITY ||--o{ ENTITY_HISTORY : "has history"
ENTITY {
int id PK
string data
timestamp updated_at
}
ENTITY_HISTORY {
int history_id PK
int entity_id FK
string data
timestamp valid_from
timestamp valid_to
string changed_by
}
process-modeling - Process context for datajourney-mapping - Customer data touchpointsdecision-analysis - Data-driven decisionscapability-mapping - Data supporting capabilitiesWeekly Installs
65
Repository
GitHub Stars
41
First Seen
Jan 21, 2026
Security Audits
Gen Agent Trust HubPassSocketPassSnykPass
Installed on
opencode58
codex57
gemini-cli56
github-copilot51
claude-code51
cursor47
Supabase 使用指南:安全最佳实践、CLI 命令与 MCP 服务器配置
5,400 周安装
原型人物画像(Proto-Persona)指南:快速创建假设驱动的用户画像,协调产品团队
611 周安装
Mapbox Web集成模式:React/Vue/Angular/Svelte最佳实践与GL JS v3教程
607 周安装
定价变更财务影响评估框架:ARPU、流失风险、CAC回收期分析指南
611 周安装
Twill Cloud Coding Agent API 使用指南 - 自动化代码任务与工作流管理
610 周安装
SaaS经济效率指标:产品经理必备的单位经济效益与资本效率分析框架
626 周安装
史诗拆分顾问:产品经理必备的9种用户故事拆分模式与INVEST验证指南
624 周安装