Data Modeling Skill | Agent Skills

Data Modeling

When to Use This Skill

Use this skill when:

Data Modeling tasks - Working on data modeling with entity-relationship diagrams (erds), data dictionaries, and conceptual/logical/physical models. documents data structures, relationships, and attributes
Planning or design - Need guidance on Data Modeling approaches
Best practices - Want to follow established patterns and standards

Overview

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.

What is Data Modeling?

Data modeling creates visual and structured representations of data elements and their relationships. It documents:

Entities: Things about which data is stored
Attributes: Properties of entities
Relationships: How entities connect
Constraints: Rules governing data

Modeling Levels

| 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 |

Conceptual Model

High-level view of business concepts:

Major entities only
Key relationships
No attributes (or minimal)
No technical details

Logical Model

Technology-independent data structure:

All entities and attributes
Primary and foreign keys
All relationships with cardinality
Normalization applied
No physical implementation details

Physical Model

Database-specific implementation:

Table names (physical naming)
Column names and data types
Indexes and constraints
Views and stored procedures
Database-specific features

ERD Notation

Entity (Rectangle)

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) |

Attributes

| 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 |

Relationships (Lines)

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 |

Cardinality

| 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"

Workflow

Phase 1: Identify Entities

Step 1: Extract Nouns from Requirements

From business requirements, identify:

Things the business tracks
Subjects of business rules
Sources and targets of data

Step 2: Filter Candidates

| 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) |

Step 3: Document Entities

## 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 |

Phase 2: Define Attributes

Step 1: List Attributes for Each Entity

For each entity, identify:

What do we need to know about this entity?
What uniquely identifies it?
What data does the business reference?

Step 2: Classify Attributes

| Attribute | Type | Required | Notes | |-----------|------|----------|-------| | customer_id | PK | Yes | Surrogate key | | email | Unique | Yes | Business key | | name | String | Yes | | | phone | String | No | Optional |

Step 3: Identify Keys

Primary Key (PK): Unique identifier
Natural Key: Business-meaningful identifier
Surrogate Key: System-generated identifier
Composite Key: Multiple attributes combined

Phase 3: Define Relationships

Step 1: Identify Connections

For each pair of entities:

Is there a business connection?
What is the nature of the relationship?
What is the cardinality?

Step 2: Document Relationships

## Relationships

| Relationship | From | To | Cardinality | Description |
|--------------|------|-----|-------------|-------------|
| places | Customer | Order | 1:M | Customer places orders |
| contains | Order | Product | M:N | Order contains products |

Step 3: Resolve Many-to-Many

M:N relationships require associative entities:

Student ──M:N── Course

Becomes:

Student ──1:M── Enrollment ──M:1── Course

Phase 4: Normalize (Logical Model)

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:

Read performance critical
Reporting/analytics use cases
Data warehouse design
Justified with clear trade-off analysis

Phase 5: Create Physical Model

Step 1: Map to Physical Types

| 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 |

Step 2: Define Constraints

Primary key constraints
Foreign key constraints
Unique constraints
Check constraints
Default values

Step 3: Plan Indexes

Primary key (automatic)
Foreign keys (for joins)
Frequently queried columns
Covering indexes for performance

Output Formats

Mermaid ERD

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

## 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

Structured Data (YAML)

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"

Narrative Summary

## 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

Common Patterns

Inheritance (Subtype/Supertype)

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
    }

Self-Referencing

erDiagram
    EMPLOYEE ||--o{ EMPLOYEE : "manages"

    EMPLOYEE {
        int employee_id PK
        string name
        int manager_id FK
    }

Audit Trail

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
    }

Integration

Upstream

Requirements - Data requirements source
domain-storytelling - Domain concepts
process-modeling - Data in processes

Downstream

Database design - Physical implementation
API design - Data contracts
Integration - Data exchange

Related Skills

process-modeling - Process context for data
journey-mapping - Customer data touchpoints
decision-analysis - Data-driven decisions
capability-mapping - Data supporting capabilities

Version History

v1.0.0 (2025-12-26): Initial release

Agent Skills: Data Modeling

Install this agent skill to your local

Skill Files