Levels of Data Modelling

 

Data modelling happens in three distinct stages, each serving different purposes and audiences:

 

  • Conceptual Data Model –

    • High-level representation of business concepts.

    Focus:

  • What data exists
  • How entities relate

    • Audience:

  • Business stakeholders
  • Domain experts

    • Example:

  • Customer
  • Order
  • Product

    •  

  • Logical Data Model –

    • More detailed, but still technology-agnostic.

    Focus:

  • Attributes
  • Primary & foreign keys
  • Relationships
  • Normalization

    • Audience:

  • Data Analysts
  • Data Engineers
  • Architects

    • Example:

  • Customer(customer_id, name, email)
  • Order(order_id, customer_id, order_date)

    •  

  • Physical Data Model –

    • More detailed, but still technology-agnostic.

    Focus:

  • Tables
  • Data types
  • Indexes
  • Partitioning

    • Audience:

  • Database administrators
  • Engineers

    • Example:

  • SQL tables
  • Parquet files in a data lake

    •  

    Conceptual Model vs. Logical vs. Physical: Understanding the Progression

    These three levels represent increasing detail and technical specificity:

    Aspect

    Conceptual

    Logical

    Physical

    Purpose

    Understand business requirements

    Design data structure

    Implement in specific database

    Abstraction Level

    Highest (most abstract)

    Medium

    Lowest (most concrete)

    Audience

    Business stakeholders, managers

    Data architects, technical leads

    DBAs, database engineers

    Focus

    Business concepts & relationships

    Data structure & normalization

    Technical implementation

    Entities

    Identified, no details

    All with complete attributes

    Mapped to database tables

    Attributes

    Not specified

    All specified with types

    Specific data types (INT, VARCHAR)

    Keys

    Not specified

    Primary and foreign keys defined

    Implementation-specific syntax

    Technology

    Technology-independent

    Technology-independent

    Database-specific (PostgreSQL, Oracle)

    Normalization

    Not applied

    Applied (typically 3NF)

    Fully applied with optimization

    Time to Create

    Hours to days

    Days to weeks

    Weeks to months

    Reusability

    Highly reusable

    Maps to any database

    Database-specific only

     

    How the Three Levels Work Together

    Progression Flow:

  • Business stakeholder describes requirements → Conceptual model created
  • Technical team analyzes requirements → Logical model designed
  • Logical model is database-agnostic → Can generate multiple physical models
  • Physical model created for each database → Implementation-ready SQL/DDL

    • Reusability: The same logical model can generate different physical models for PostgreSQL, MongoDB, and Oracle—ensuring consistency while adapting to each platform's strengths.

     

    Leave a Reply