Data modeling is usually divided into conceptual, logical and physical levels. Each answers a different question: what the system manages, how that information is represented, and how it will be implemented in a chosen DBMS.
A small forum schema shows what is added at each level. The later sections also cover when teams combine levels and why the three-schema architecture is a separate concept despite the similar terminology.
Why data modeling is split into levels at all
The split exists because each level has to settle some questions and leave others open.
Nobody gets anywhere debating VARCHAR lengths in a requirements meeting. And if you reach the week before development without agreement on what data you're keeping, no amount of careful typing saves you from a redraw. Splitting the work is a way of separating what has to be decided now from what can wait.
There's a second reason: each level has a different audience. Business stakeholders read the conceptual model, engineers read the logical model in design review, and developers and DBAs read the physical model when they build. Put indexes on a conceptual diagram and the people who most need to read it can't.
Conceptual data model: what do we manage?
Conceptual design focuses on which entities exist and how they relate.
For a forum, that's members, posts and comments, connected by "a member writes posts" and "a post has comments". Three boxes and two lines is a complete diagram at this level.
The simplified conceptual model in this guide omits column lists, primary keys, and data types. Some methods and organizations do show a few key attributes or identifiers at this level, so the mere presence of an attribute does not make a model non-conceptual. Names stay in business language — Member, not members. This is the diagram you put in front of the business to ask whether the list of things you're tracking is complete.
The usual stumbling block here is telling entities from attributes. Is a category an attribute of a post, or an entity of its own? Once a category needs anything beyond its name, it becomes an entity. If that call is unclear, the entity-selection criteria in How to Draw an ERD are a good starting point.
Logical data model: attributes and keys arrive
At the logical level, entities get attributes. Members gain an email address, a nickname and a join date; posts gain a title, body and creation date. Each entity also gets an identifier that distinguishes one row from another.
Relationships get specific too. Members and posts aren't just connected: the relationship is one-to-many or many-to-many, mandatory or optional. Every post must have an author, so the member is mandatory from the post's side, while a member may have written nothing, so posts are optional from the member's side.
Normalization is logical-level work as well. Storing the category name directly on posts means renaming a category rewrites every post row, so the category splits out and becomes a reference. Which structures to split and why is worked through with examples in Database Normalization.
Identifying versus non-identifying relationships also belong here. If the parent's key becomes part of the child's primary key, the relationship is identifying; if it lands as an ordinary foreign key column, it's non-identifying. A post-tag junction table with a composite key is the identifying case, and most relationships you'll meet at work are non-identifying.
By the end of the logical level you can hold a full design review without having chosen a DBMS. Names are still Member and Email address, and types are no more specific than text, number and date.
Physical data model: types and indexes get decided
Physical design starts by naming the DBMS. Going with MySQL, the names become members and posts, the email becomes VARCHAR(255), and the creation date becomes DATETIME.
Roughly, this is what gets added:
- Real table and column names, following the team's naming convention.
- Data types and lengths, with VARCHAR(200) instead of "text" and BIGINT instead of "number".
- Indexes on the columns that queries filter and sort by.
- Constraints, including NOT NULL, UNIQUE, defaults and foreign key delete rules.
- Performance adjustments, including any denormalization you decide to accept.
A physical model maps almost one-to-one onto DDL. The forum above, in MySQL:
CREATE TABLE members (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE COMMENT 'Email address',
nickname VARCHAR(50) NOT NULL COMMENT 'Display name',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Join date'
) COMMENT='Members';
CREATE TABLE posts (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
member_id BIGINT NOT NULL COMMENT 'Author',
category_id INT NOT NULL COMMENT 'Category',
title VARCHAR(200) NOT NULL COMMENT 'Title',
content TEXT NULL COMMENT 'Body',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created',
CONSTRAINT fk_posts_member FOREIGN KEY (member_id) REFERENCES members (id),
INDEX idx_posts_member_created (member_id, created_at)
) COMMENT='Posts';The logical model's "email address" became VARCHAR(255) NOT NULL UNIQUE, and an index appeared for listing a member's recent posts. Neither decision existed one level up.
Decisions to settle before you cross into physical
Start writing DDL the moment the logical model is done and every table arrives with slightly different rules. A few things are worth agreeing on first.
- Naming conventions. Singular or plural table names, and how far abbreviations are allowed. Settle it late and you end up with a
membertable and auserstable in the same schema. - Type mapping. Write down how logical text, number and date turn into concrete types. Get this wrong and money ends up in a FLOAT, with reconciliation drifting by fractions of a cent.
- Identifier strategy. Business value as the primary key, or a surrogate? Key a table on something mutable like an email address or a tax ID and changing it later means editing every table that references it.
- Common columns. Whether every table carries created_at, updated_at and an author column, and what those columns are called.
- Delete rules. Restrict or cascade on each foreign key. Business rules land here: what happens to a member's posts when the account closes.
Agreeing on those five speeds up the physical model and cuts down the cleanup that follows when each table brings its own conventions.
The three ERD levels side by side
| Conceptual | Logical | Physical | |
|---|---|---|---|
| Question answered | What do we manage? | How is it represented? | Where and how is it built? |
| Names | Business terms (Member) | Logical names (Member, Email address) | Physical names (members, email) |
| Attributes | Omitted or limited to key concepts | Key attributes and identifiers | Every column, with types |
| Relationships | Exists or doesn't | Cardinality and optionality | Foreign keys and delete rules |
| DBMS | Irrelevant | Irrelevant | Chosen |
| Audience | Business stakeholders | Designers and engineers | Engineers and DBAs |
Drawn out, the same forum changes like this at each level.
The order in which the boxes fill up tells you what each level is for: names only, then attributes and keys, then types and constraints. Relationship symbols start carrying meaning at the logical level, and the ERD notation guide covers how to read them.
Not to be confused with the three-schema architecture
External, conceptual and internal schemas (the ANSI/SPARC three-schema architecture) share vocabulary with the levels above, but they describe something else entirely.
Three-schema architecture is about how a database presents itself: the per-user view (external), the overall logical structure (conceptual), and the storage details (internal). Conceptual, logical and physical modeling is about the stages you go through while designing. The word "conceptual" appears in both, which is the whole source of the confusion. Treat one as structure and the other as process, and the overlap stops being a problem.
How far teams actually split them
Some shops produce all three documents; plenty collapse two of them.
Government work and large systems-integration contracts usually name conceptual, logical and physical models as separate deliverables. They're review items, so they get built and signed off in sequence.
Smaller teams typically keep the conceptual model on a whiteboard and draw the logical and physical models as one diagram. The DBMS is already decided, so maintaining separate logical and physical names buys little.
Either way, the sequence is worth keeping. Skip it and you end up choosing data types first, then bending the business to fit them. Even when you produce a single document, answer the three questions in order.
Questions that come up in reviews and interviews
The distinction shows up constantly in design reviews, and it's a standard interview question for anyone touching schema design. A few things worth having ready.
Which level owns which decision. Types and indexes are physical. Normalization and identifiers are logical. Entities and relationships are conceptual. Most questions resolve against that mapping.
Identifiers. Know the difference between candidate keys, primary keys and composite keys, and the qualities a primary key needs: uniqueness, minimality, stability and presence.
Identifying and non-identifying relationships. Parent key inside the child's primary key means identifying, drawn as a solid line; parent key as a plain attribute means non-identifying, drawn dashed.
Normalization and denormalization. First through third normal form, the update anomalies each prevents, and when breaking them is justified. That ground is covered in Database Normalization.
Three things people get backwards
Normalization gets filed under physical design and indexing under logical design. It's the other way around: normalization is logical, indexing is physical.
Solid and dashed lines get swapped. Solid is identifying, dashed is non-identifying.
Minimality drops out of the primary key definition. Being unique isn't enough; a primary key also has to be the smallest set of attributes that stays unique.
FAQ
What is the difference between conceptual, logical and physical data models in one line?
The conceptual model decides what you manage, the logical model decides how it's represented as attributes and keys, and the physical model decides where and in what form it gets built. It's the same business described at increasing resolution, so each level carries more information than the one before it.
What exactly separates a logical data model from a physical one?
A logical model is designed without committing to a DBMS; a physical model is designed after that commitment. Logical uses business names like Member and Email address, with types no more specific than text, number or date. Physical uses members and VARCHAR(255), plus indexes, constraints and delete rules, so it maps almost one-to-one onto DDL.
Is a conceptual data model the same thing as an ERD?
In practice people draw conceptual models as ERDs, so the two get used interchangeably. Strictly, the conceptual data model is the model of what you manage, and an ERD is one notation for drawing it. The same ERD notation works at all three levels — what changes is how much detail you put in the boxes.
Do teams really produce all three levels?
It depends on the shop. Government and large systems-integration projects usually list all three as deliverables and review them in order. Smaller teams often keep the conceptual model on a whiteboard and draw logical and physical as one diagram, since the DBMS is already decided. Even when the documents collapse, it pays to answer the three questions in order.
The conceptual model defines what is managed, the logical model defines its structure, and the physical model defines its implementation. Keeping those decisions in order prevents storage details from driving the requirements.
Once the physical model is settled, you can paste the DDL into WorksCove ERD to get a diagram and a table specification out of it. Working from an existing database instead? SQL to ERD starts from the dump.