data-modeling

What this skill does

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

```text
┌─────────────────┐
│    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

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

```markdown
## 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:

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

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

```markdown
## Data Dictionary

### CUSTOMER

| Column | Type | Null | Key | Default | Description |
|----