Conceptual, Logical, and Physical Data Models
Data modeling happens at three levels of detail: conceptual, logical, and physical. This guide explains what each captures, who reads it, and how a design refines from one to the next.
The phrase "data model" hides an important distinction: there are three of them, at increasing levels of detail, and confusing one for another is a common source of miscommunication between business stakeholders and engineers. A conceptual model captures the big ideas, a logical model adds structure and rules, and a physical model specifies exactly what gets built in a particular database. The same domain flows through all three, gaining precision at each step, and knowing which level you are working at keeps a conversation focused.
This guide explains each level, who it is for, and how a design matures from one to the next. The progression mirrors how good design actually happens: agree on the concepts first, then the structure, then the implementation details, rather than arguing about column types before you have agreed on what entities exist. You can draw all three levels in the editor at /diagrams, and the ERD tooling at /diagram-tools/erd-tool suits the logical and physical stages especially well.
The conceptual model: the big picture
The conceptual data model is the highest-level view, capturing the main things the business cares about and how they relate, with no technical detail at all. It shows entities like Customer, Order, and Product and the relationships between them, but no attributes, no keys, no data types. Its purpose is agreement on scope and vocabulary among business stakeholders, analysts, and anyone who needs to understand the domain without caring how it is stored.
Because it is deliberately free of technical detail, the conceptual model is the one you draw with non-technical people in the room. It answers questions like "does a customer relate directly to a product, or only through an order?" and "do we track suppliers as a distinct thing?" These are business questions, and settling them before descending into structure prevents the expensive situation where a schema is half-built before someone realizes a whole entity was missing. Keep conceptual models sparse; their value is precisely in what they leave out.
The logical model: structure and rules
The logical data model adds detail while staying independent of any specific database technology. Here you introduce attributes for each entity, identify primary keys, specify the exact cardinality and optionality of each relationship, and resolve many-to-many relationships into junction structures. You apply normalization at this stage. What you do not yet do is commit to a particular database's data types, indexes, or storage details; the logical model would be equally valid whether you eventually build it in PostgreSQL, MySQL, or SQL Server.
The logical model is the heart of data modeling and the level where most of the real design thinking happens. It is detailed enough to be rigorous, capturing every relationship's cardinality and every entity's attributes and keys, yet abstract enough to focus on the data's inherent structure rather than the quirks of a particular engine. This is the model an architect or senior engineer produces and reviews, and it is typically the most valuable long-lived artifact because it survives a change of database technology.
The physical model: the actual build
The physical data model is the logical model made concrete for a specific database system. Now entities become named tables, attributes become columns with exact data types (VARCHAR(255), BIGINT, TIMESTAMP WITH TIME ZONE), and you add everything the database needs to perform: indexes, partitions, storage parameters, and vendor-specific features. The physical model maps directly to the DDL that will create the database, and two physical models of the same logical design can look quite different if one targets PostgreSQL and the other targets a data warehouse.
Because it is tied to a specific engine and tuned for performance, the physical model is the engineer's artifact, and it is where decisions like "index this foreign key" and "denormalize this table for read speed" live. It is also the level most likely to drift from the others over time as production realities force pragmatic changes. Keeping the physical model, or at least an ERD of it, in sync with the running database is what makes it useful for onboarding and debugging rather than a stale relic.
How the levels relate
The three models are not competing choices; they are stages of one process, each refining the last. Understanding how they map onto audiences and decisions helps you pitch a diagram at the right level for its readers.
- Conceptual: entities and relationships only; audience is business stakeholders; answers "what things exist and how do they relate?"
- Logical: adds attributes, keys, precise cardinality, and normalization; audience is architects; database-independent.
- Physical: adds tables, exact data types, indexes, and vendor features; audience is engineers; maps to DDL.
- Each level is a refinement of the one above, gaining detail while preserving the earlier decisions.
- The logical model is usually the most valuable long-lived artifact because it survives a change of database.
- Match the diagram's level to its readers; showing data types to a business stakeholder obscures the concepts.