Skip to content
Software Architecture Guide

Logical vs. physical data model

Explanation

Section titled “Explanation”

A data model shows what data a system keeps and how the pieces relate. It exists at several levels of detail. The article Conceptual, Logical and Physical Data Model covers all three. This article compares the logical and the physical level.

Logical data model. Teams design it independently of the database that will later run it. It describes the business data, not the technology. Data architects and business analysts usually create it.

Physical data model. It refines the logical model for one specific database technology. It is always built for a specific database management system (DBMS), which is the software that stores the data. So it must follow that platform’s rules, such as naming conventions. It states the data type of every stored data object. One logical entity may be split into several tables. Developers and database administrators implement it.

Keys, indexes and more. The physical stage is where you define primary keys, foreign keys, indexes, user roles, authorizations and stored procedures.

Where the conceptual model fits. The conceptual model is abstract and high-level. In practice, the line between conceptual and logical is somewhat blurry.

Independence between levels. In the three-level view, storage technology can change without affecting the logical or conceptual model. You can swap the database and keep the business view.

One logical model, many physical designs. The same logical model can lead to different physical designs. Normalized and dimensional designs are two. Data Vault is another. It is a hybrid of third normal form (3NF) and star schema. Its hubs hold unique business keys. Its links are many-to-many join tables with some metadata.

Checking the translation. Comparing the physical model with the logical model shows whether the business requirements were translated correctly.

Example

Section titled “Example”

A shop’s logical model has a Customer entity and an Order entity. A customer places many orders. Nothing says which database will store them.

The team then picks a specific DBMS. The physical model gives the tables and columns names that follow the platform’s rules. Each column gets a data type. Keys link orders to customers, and an index is added where lookups need it. If the team later changes database, the logical model stays the same. Only the physical model changes.

Trade-offs

Section titled “Trade-offs”

Keeping two models costs effort, because each one needs upkeep. The benefit is that business meaning stays separate from storage details, so storage technology can change without touching the logical view. Because the boundary between conceptual and logical models is blurry, teams should agree on what each model contains. The facts here do not say how to keep the models in sync.

One logical model can lead to several physical designs.

Diagram source 
flowchart TD
  L[Logical model: entities and relationships, no technology]
  L --> P1[Physical design A: normalized]
  L --> P2[Physical design B: dimensional]
  L --> P3[Physical design C: Data Vault]

Checks

Section titled “Checks”
  • The logical model is free of database-specific details and does not depend on a chosen DBMS.
  • The physical model names the target DBMS and follows its naming rules.
  • Data types are stated for all stored data objects in the physical model.
  • Keys and indexes are defined at the physical stage.
  • The physical model was compared with the logical model to confirm business requirements were translated correctly.
Section titled “Related”

Sources

Section titled “Sources”