Data Modeling

Modeling Levels

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:

Attributes

Relationships (Lines)

Notation Styles:

Crow's Foot Notation:

Cardinality

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

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

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:

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

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