Data modeling with Entity-Relationship Diagrams (ERDs), data dictionaries, and conceptual/logical/physical models. Documents data structures, relationships, and attributes.
Create and document data structures using Entity-Relationship Diagrams (ERDs), data dictionaries, and conceptual/logical/physical models. Use when designing databases, documenting data relationships, or normalizing data structures.
/plugin marketplace add melodic-software/claude-code-plugins/plugin install business-analysis@melodic-softwareThis skill is limited to using the following tools:
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 capabilitiesUse when working with Payload CMS projects (payload.config.ts, collections, fields, hooks, access control, Payload API). Use when debugging validation errors, security issues, relationship queries, transactions, or hook behavior.
Applies Anthropic's official brand colors and typography to any sort of artifact that may benefit from having Anthropic's look-and-feel. Use it when brand colors or style guidelines, visual formatting, or company design standards apply.
Creating algorithmic art using p5.js with seeded randomness and interactive parameter exploration. Use this when users request creating art using code, generative art, algorithmic art, flow fields, or particle systems. Create original algorithmic art rather than copying existing artists' work to avoid copyright violations.