Drawing the same model three times

Why describe the same information over again at three different levels of detail?

You are asked for a conceptual model, then a logical model, then a physical model. Same subject each time, in increasing detail, and the physical one is the only one the database will ever read.

The suspicion is obvious and reasonable: this is ceremony. Draw the thing once, in the detail that matters, and skip the two rehearsals. So the burden of proof sits with the three-model habit — what does the extra drawing buy that a single detailed diagram cannot?

Start with a question that seems unrelated. Over the next five years, what will make you change your schema?

There are three quite different answers. The business might change what it is — it starts selling subscriptions as well as products, so a thing exists now that did not before. Or the information requirements might change — the same business needs to record two addresses per customer instead of one. Or the platform and the workload might change — the query pattern shifts, the volume grows tenfold, you move to a columnar engine and the old partitioning is wrong.

Notice these arrive at different rates and from different people. The first comes from strategy and is rare. The second comes from analysts and is steady. The third comes from operations and is frequent.

Now the point. In a single detailed diagram, all three kinds of fact are mixed together in one artefact — so a change of the third kind, purely about performance, forces you to edit the same document that records what a customer is. And that document is the one the business was supposed to be able to read and agree with. Every physical decision pollutes it.

The three elevations separate facts by why they will change. Which is the same reason we separate anything in engineering.

Follow the separation through and each layer's content falls out on its own:

The conceptual model holds only what the business would recognise: the entities it names, and how they relate. Customer places Order; Order contains Line. No keys, no datatypes, no tables. If the business has not changed, this has not changed — and that stability is what makes it useful as a shared agreement rather than a technical document.

The logical model adds the information structure: attributes, identifiers, cardinality, normalisation, what is optional and what is mandatory. It answers what facts do we need to record, and it does so without naming a database product. A logical model can be implemented on a relational engine, a document store, or a warehouse, and it is still the same model.

The physical model is where the platform finally enters: real table and column names, vendor datatypes, indexes, partitioning, and any denormalisation done for speed. It answers how do we store this on this system, for this workload.

And here is the payoff that justifies the effort. Denormalising a table for performance is a physical decision. If your only artefact is the physical model, that decision silently rewrites the record of what the business means — six months later nobody can tell whether two customer columns exist because the business genuinely has two customer concepts or because someone was avoiding a join. Keeping the logical model means the intent survives the optimisation.

One honest correction, because the terms get conflated: this trio is not the ANSI/SPARC three-schema architecture of 1975, which divides a database into external, conceptual and internal schemas — user views, the community view, and the storage layout. The ideas are cousins and the middle word is even shared, but they are different frameworks and citing one as the other causes real confusion.

And a second honest note: many teams skip the conceptual model, and for a small system with one clear domain that is often defensible. What is much less defensible is skipping the logical one, because that is where the reasoning lives.

Three refinements.

First, the misconception that these are three stages of a project, done once in order and then filed. They are three views, each maintained for as long as the system lives. The order is a first-pass heuristic, not a lifecycle — and in practice the flow goes both ways, since a physical constraint discovered late genuinely can send you back to reconsider what you were modelling.

Second, the boundary between logical and physical is where most disputes actually happen, and the useful test is portability. If a decision would survive moving to a different database engine, it is logical. If it would have to be reconsidered, it is physical. Choosing to store a computed total rather than derive it: logical, because it changes what facts you keep. Choosing a clustered index on that column: physical.

Third, the elevations differ in audience as much as in detail. The conceptual model is a negotiating instrument with the business; the logical model is the analyst's and developer's reference; the physical model is the database administrator's. Detail is a consequence of that, not the purpose. This is why "just add the detail to one diagram" fails — it produces an artefact none of the three audiences can use.

• Where the logical and physical boundary really sits in a warehouse, where denormalisation is deliberate from the start.
• Whether document and graph stores need the same three elevations or collapse two of them.
• How to keep a conceptual model current enough to stay trustworthy.

Every term the collection defines is gathered in the glossary.