Skip to content
Software Architecture Guide

Conceptual, Logical and Physical Data Model

Explanation

Section titled “Explanation”

A data model describes what data a system keeps and how that data fits together. Teams usually work at three levels. Each level answers a different question.

Conceptual model. It describes the meaning of a domain. Entity classes (the kinds of things the business cares about) and the relationships between them set its scope. It records data requirements without tying them to any technology. An entity–relationship model can express it in abstract form. See the articles Conceptual Data Model and Entity.

Logical model. It describes the same meaning as one particular data technology represents it. That can be tables and columns, classes, or XML tags. It does not depend on a specific database product or storage technology. It helps stakeholders understand the data requirements. See Logical Data Model.

Physical model. It describes how the data is physically stored, including partitions, CPUs and tablespaces. It organizes data into tables and deals with access, performance and storage. It specifies indexes and keys that speed up data access. It also covers storage choices such as filegroups, tablespaces and partitioning. It is tailored to one database system. It is developer-oriented and guides the actual implementation. See Physical Data Model and Logical vs. physical data model.

Why three levels. The levels keep the perspectives independent. Storage technology can change without affecting the logical or conceptual model.

Level Describes Tied to a specific DBMS or storage? Typical use
Conceptual Domain meaning: entity classes and relationships No Recording requirements
Logical The meaning as tables, classes or tags No Helping stakeholders understand requirements
Physical Storage, indexes, partitioning, access Yes Guiding implementation

Relation to ANSI-SPARC. ANSI-SPARC is an older three-level idea for databases. Its external level gives each user a view of only the part of the database that matters to them. Its conceptual level is a global view that does not say how data is physically stored. Its internal level covers how the database is physically represented on the computer. The terms differ from the three data models above, although both split logical meaning from physical storage. ANSI-SPARC never became a formal standard. Its idea of logical data independence is still widely adopted.

Example

Section titled “Example”

A company builds an order system. At the conceptual level, the team notes that a Customer places Orders and an Order contains Products. At the logical level, they turn these into tables with columns. At the physical level, a developer adds an index to speed up order lookups and decides how to partition the order table. If the company later moves to different storage, the first two models stay as they are.

Trade-offs

Section titled “Trade-offs”

Three models mean more documents to keep consistent. The facts here do not say when a level can be skipped, so decide that per project. The benefit is clear: each reader sees only the detail they need, and storage changes stay away from the business view.

The three levels move from business meaning to database-specific implementation.

Diagram source 
flowchart LR
  C[Conceptual model: entities and relationships] --> L[Logical model: tables, columns, keys]
  L --> P[Physical model: indexes, partitions, storage]

Checks

Section titled “Checks”
  • The conceptual model lists entity classes and their relationships without naming any storage technology.
  • The logical model is independent of a specific DBMS or storage technology.
  • Indexes, partitioning and tablespaces appear only in the physical model.
  • The documentation states that storage technology can change without changing the logical or conceptual model.
Section titled “Related”

Sources

Section titled “Sources”