Conceptual, Logical, and Physical Data Models

Data models are commonly described at three levels. A conceptual model says what things exist in a business and how they relate. A logical model says how those things are represented as attributes, keys, and relationships. A physical model says how that representation is actually stored in a particular engine. The terms are industry usage rather than a single fixed standard, and teams draw the lines slightly differently, but the separation itself is widely used because each level answers a different question.

What each level decides

The conceptual level is where disagreements about meaning live: whether a cancelled order is still an order, whether a household or a person is the customer. No amount of engineering resolves those questions, which is why they belong with the business. The physical level is where platform knowledge lives, and the business should not need to care about it.

Why the separation matters: data independence

The value of keeping the levels apart is that a change at a lower level should not disturb the levels above it. This principle is usually called data independence, and it predates today’s platforms. E. F. Codd’s 1970 paper introducing the relational model opens with it: future users of large data banks “must be protected from having to know how the data is organized in the machine,” and their activities and most application programs should remain unaffected when that internal representation changes.

In practice it is a design goal, not a guarantee, and it is useful to know which changes respect it:

• Physical change — repartitioning a table, changing its clustering, adding an index. Should be invisible to consumers apart from performance.
• Logical change — renaming a column, splitting a table, changing a key. Visible to everyone who wrote a query against the old shape.
• Conceptual change — deciding cancelled orders no longer count as orders. Changes numbers, so no layer of engineering can hide it; it has to be announced and agreed.

A useful habit follows. When a proposed model change becomes contentious, first identify which level it belongs to. That usually settles who needs to be involved: a physical change is an engineering decision, a logical change needs the consumers of the affected tables, and a conceptual change needs the people who own the definition.

Common confusions

• A diagram is not automatically conceptual. An entity-relationship diagram full of column types and foreign keys is a picture of the logical or physical model.
• The logical model is not tied to normalization. A dimensional model and a normalized model are two different logical representations of the same conceptual model.
• Skipping the conceptual level does not remove it. The questions it answers still get answered — just implicitly, by whoever writes the first query.

How these levels apply when the same business is modeled once for operations and again for analysis is worked through in Operational and Analytical Models: Designed for Different Jobs.

References: E. F. Codd, A Relational Model of Data for Large Shared Data Banks (Communications of the ACM, 1970); E. F. Codd, A Relational Model of Data for Large Shared Data Banks (1970).