Conceptual Data Modeling

When to Use This Skill

Use this skill when:

• Conceptual Modeling tasks - Working on identify business entities, relationships, and high-level data structures from requirements and domain knowledge
• Planning or design - Need guidance on Conceptual Modeling approaches
• Best practices - Want to follow established patterns and standards

Overview

Conceptual data modeling identifies the essential business entities and their relationships at a high level, independent of any specific database technology. It bridges business requirements and technical implementation.

Conceptual vs Logical vs Physical

Aspect	Conceptual	Logical	Physical
Audience	Business stakeholders	Analysts, architects	Developers, DBAs
Detail	High-level entities	Attributes, keys	Columns, indexes
Technology	None	Database-agnostic	Platform-specific
Focus	What data exists	How data relates	How data is stored

Entity Identification Process

Step 1: Extract Nouns from Requirements

Read requirements and identify candidate entities:

Requirements: "Customers place orders for products. Each order contains
multiple items. Products belong to categories."

Candidate Entities:
- Customer
- Order
- Product
- Item (OrderItem)
- Category

Step 2: Filter Candidates

Eliminate non-entities:

• Attributes (name, date, price)
• Derived concepts (total, count)
• Roles (admin is a User role, not an entity)
• Events (unless storing event history)

Step 3: Identify Relationships

Relationship Type	Description	Example
One-to-One (1:1)	Single instance each side	User ↔ Profile
One-to-Many (1:N)	One to multiple	Customer → Orders
Many-to-Many (M:N)	Multiple both sides	Products ↔ Categories

Step 4: Define Cardinality and Participation

Customer ─┤├─○< Order     (One customer has zero or more orders)
Order    ─┤├─┤< OrderItem (One order has one or more items)
Product  >○─┤├─ Category  (One product belongs to one category)

Notation:

• ┤├

  = Mandatory (must exist)

• ○

  = Optional (may not exist)

• <

  or

  >

  = Many side

Conceptual Model Template

# Conceptual Data Model: [Domain Name]

## 1. Domain Overview
[Brief description of the business domain]

## 2. Entity Catalog

### Entity: [Entity Name]
| Property | Value |
|----------|-------|
| Description | [What this entity represents] |
| Business Owner | [Who owns this data] |
| Lifecycle | [Created → Active → Archived] |
| Volume | [Expected record count] |
| Growth | [Expected growth rate] |

**Key Attributes (Business Identifiers):**
- [Natural key 1]
- [Natural key 2]

**Related Entities:**
- [Entity A] - [relationship description]
- [Entity B] - [relationship description]

## 3. Relationship Matrix

| From | To | Cardinality | Description |
|------|-----|-------------|-------------|
| Customer | Order | 1:N | Customer places orders |
| Order | OrderItem | 1:N | Order contains items |
| Product | OrderItem | 1:N | Product appears in items |
| Category | Product | 1:N | Category contains products |

## 4. Entity Relationship Diagram

```mermaid
erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ ORDER_ITEM : contains
    PRODUCT ||--o{ ORDER_ITEM : "appears in"
    CATEGORY ||--|{ PRODUCT : contains

5. Glossary

[Business terms and definitions - ubiquitous language]

Domain-Driven Design Alignment

Bounded Contexts

Group entities by business capability:

┌─────────────────────┐  ┌─────────────────────┐
│   Order Context     │  │  Catalog Context    │
│                     │  │                     │
│  ┌───────────────┐  │  │  ┌───────────────┐  │
│  │    Order      │  │  │  │   Product     │  │
│  │   OrderItem   │  │  │  │   Category    │  │
│  │   Shipping    │  │  │  │   Inventory   │  │
│  └───────────────┘  │  │  └───────────────┘  │
└─────────────────────┘  └─────────────────────┘
         ↕                        ↕
    Customer ID             Product ID
   (shared reference)     (shared reference)

Aggregates

Identify aggregate boundaries:

// Order Aggregate
public class Order // Aggregate Root
{
    public OrderId Id { get; }
    public CustomerId CustomerId { get; }
    public List<OrderItem> Items { get; } // Owned by Order
    public ShippingInfo Shipping { get; } // Value Object

    // OrderItems cannot exist without Order
    // Access OrderItems only through Order
}

Value Objects vs Entities

Concept	Entity	Value Object
Identity	Has unique ID	No identity
Equality	By ID	By value
Mutability	Mutable	Immutable
Example	Customer	Address, Money

Mermaid ER Diagram Syntax

erDiagram
    CUSTOMER {
        guid id PK
        string name
        string email UK
    }
    ORDER {
        guid id PK
        guid customer_id FK
        date order_date
        string status
    }
    ORDER_ITEM {
        guid id PK
        guid order_id FK
        guid product_id FK
        int quantity
        decimal price
    }
    PRODUCT {
        guid id PK
        string sku UK
        string name
        decimal price
    }

    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ ORDER_ITEM : contains
    PRODUCT ||--o{ ORDER_ITEM : "ordered as"

Common Patterns

Hierarchical Data

Category (self-referencing)
├── Electronics
│   ├── Phones
│   │   ├── Smartphones
│   │   └── Feature Phones
│   └── Computers
└── Clothing

erDiagram
    CATEGORY {
        guid id PK
        guid parent_id FK
        string name
        int level
    }
    CATEGORY ||--o{ CATEGORY : "parent of"

Temporal Data

Track state changes over time:

ProductPrice (temporal)
- product_id
- price
- effective_from
- effective_to (nullable = current)

Polymorphic Associations

Attachment can belong to Order OR Product OR Customer:

Option 1: Separate FKs
attachment.order_id, attachment.product_id, attachment.customer_id

Option 2: Polymorphic
attachment.attachable_type, attachment.attachable_id

Validation Checklist

• All business entities identified
• Relationships have clear cardinality
• Naming follows ubiquitous language
• Aggregate boundaries defined
• No premature physical details
• Stakeholders can understand the model
• Glossary defines all terms

Integration Points

Inputs from:

• Requirements documents → Entity candidates
• Domain experts → Business rules

• event-storming

  (EA plugin) → Domain events

Outputs to:

• er-modeling

  skill → Logical model

• schema-design

  skill → Physical model
• DDD implementation → Aggregate design