9.1 What is a Data Model? (Conceptual / Logical / Physical)

Level: 9 | Version: v1.1 | Author: Meptrasoft

Overview

A data model is a way to represent an organization's data and the relationships between that data.

It helps us move from a business requirement to an actual database design.

Data modeling is commonly explained in three levels:

CONCEPTUAL
    ↓
LOGICAL
    ↓
PHYSICAL

Conceptual → What the business needs
Logical → How the data is organized
Physical → How it is implemented in a database

Data Modeling — From Business Idea to Database: Conceptual, Logical, and Physical — Figure 4: Data modeling progresses from the business view to logical organization and finally to physical database implementation.

Conceptual Data Model

The Conceptual Data Model is the highest-level view.

It focuses on:

Main entities
Relationships between entities
Business requirements

It does not usually include database-specific details.

Example:

[ Customer ]
      |
      | places
      ↓
[ Order ]

At this stage, we are mainly asking:

“What things exist in the business, and how are they related?”

Conceptual Data Model — High-Level Business View — Figure 1: A conceptual data model provides a high-level business view of entities and their relationships.

Logical Data Model

The Logical Data Model adds more detail.

It defines:

Attributes
Keys
Relationships
Data organization

Example:

Customer(
    customer_id,
    name,
    email
)

Order(
    order_id,
    order_date,
    customer_id
)

At this stage, we are asking:

“What data do we need, and how should it be logically organized?”

The logical model is generally independent of a specific DBMS.

Logical Data Model — Detailed Data Structure — Figure 2: A logical data model adds attributes, keys, and relationships to organize the data in greater detail.

Physical Data Model

The Physical Data Model describes how the design will actually be implemented in a particular database system.

It can include:

Table names
Column names
Data types
Constraints
Indexes
DBMS-specific features

For example, in PostgreSQL:

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

Here we are deciding exactly how the logical design will be implemented.

Physical model → Actual database implementation

Physical Data Model — Actual Database Implementation — Figure 3: A physical data model shows how the logical design is implemented using database-specific tables, data types, and constraints.

Simple Comparison

Level	Focus	Example
Conceptual	Business entities and relationships	Customer → Order
Logical	Attributes, keys, relationships	`Customer(customer_id, name, email)`
Physical	Tables, data types, indexes, constraints	PostgreSQL `CREATE TABLE`

Easy Memory Trick

CONCEPTUAL → BUSINESS IDEA
LOGICAL    → DATA DESIGN
PHYSICAL   → DATABASE IMPLEMENTATION

One Example: Online Shopping

Suppose we are designing an online shopping system.

Conceptual

Customer → places → Order

Logical

Customer(
    customer_id,
    name
)

Order(
    order_id,
    order_date,
    customer_id
)

Physical

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT REFERENCES customers(customer_id)
);

The same business requirement is gradually converted into a database implementation.

Why Data Modeling Is Important

Data modeling helps teams:

Understand business requirements
Organize data properly
Define relationships
Reduce design problems
Create a clear database structure

The three levels provide different views of the same system.

BUSINESS REQUIREMENT
        ↓
CONCEPTUAL MODEL
        ↓
LOGICAL MODEL
        ↓
PHYSICAL MODEL
        ↓
DATABASE

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
Conceptual and physical models are the same	Conceptual is high-level; physical is implementation-specific
Logical model contains SQL code	SQL is usually part of the physical implementation
Physical model is only about storage disks	It also includes tables, columns, data types, constraints, indexes, and DBMS-specific details
Every model must contain the same level of detail	Each level has a different purpose
Data modeling is only for database administrators	Developers, data engineers, architects, and business teams can all use data models

Placement Quick Points

CONCEPTUAL
→ ENTITIES + RELATIONSHIPS

LOGICAL
→ ATTRIBUTES + KEYS + RELATIONSHIPS

PHYSICAL
→ TABLES + TYPES + CONSTRAINTS + INDEXES

A data model represents data and its relationships.
Conceptual models focus on business concepts.
Logical models add attributes, keys, and relationships.
Physical models describe the actual database implementation.
Data modeling moves from business requirements toward a concrete database design.

Interview Questions

What is a data model?

A data model is a representation of data, its structure, and its relationships.

What are the three levels of data modeling?

Conceptual
Logical
Physical

What is a conceptual data model?

It is a high-level representation of business entities and their relationships without database-specific details.

What is a logical data model?

It defines attributes, keys, and relationships in greater detail while generally remaining independent of a specific DBMS.

What is a physical data model?

It describes how the logical design is implemented in a specific database system using tables, columns, data types, constraints, indexes, and other details.

What is the main difference between logical and physical models?

Logical  → What the data structure should be
Physical → How it is implemented in a DBMS

Practice & Hands-On Exercises

Explain conceptual, logical, and physical data models in your own words.
Convert Customer → Order into a logical model.
Identify the attributes in the logical model.
Identify the primary and foreign keys.
Write PostgreSQL tables for the logical design.

💡 Tip: Test your schema queries using the in-browser interactive runner above.

Key Takeaway

CONCEPTUAL
→ BUSINESS VIEW

LOGICAL
→ DATA STRUCTURE

PHYSICAL
→ ACTUAL DATABASE

Data modeling moves from a high-level business idea to a detailed logical design and finally to a physical database implementation.

End of What is a Data Model? (Conceptual / Logical / Physical)