Logical vs. Physical Data Models: Why the Separation Matters

Most ERD tools show you one diagram: boxes with column names, types, and arrows for foreign keys. That’s a physical model, and for a lot of projects it’s all you’ll ever need. But the moment a schema has more than one kind of reader — engineers who write the DDL and domain experts who don’t — a single physical diagram starts doing two incompatible jobs at once, and it does both of them worse than it would do either alone.

Two audiences, one schema

A logical model describes the business: entities like Customer and Order, relationships like “a customer places many orders,” attributes named the way a domain expert would say them out loud — Customer Identifier, Order Placement Date. It has no opinion about VARCHAR lengths, index strategy, or which database engine is running underneath. Its job is to be reviewable by a product manager or a business analyst who has never opened a SQL console and shouldn’t have to.

A physical model describes the implementation: tables and columns with concrete types, primary and foreign keys enforced by constraints, indexes chosen for query patterns, naming that’s been abbreviated and dialect-adjusted (cust_id, INT, snake_case). Its job is to compile — to become a CREATE TABLE statement that a specific database will accept and execute efficiently.

The trouble starts when a team tries to make one diagram serve both audiences. Show the physical names and types to a domain expert reviewing the model, and they either nod along without really validating anything — cust_id INT NOT NULL doesn’t map cleanly back to “does every customer have exactly one loyalty tier?” — or they start proposing changes to abbreviations and types they have no stake in. Show the business-friendly logical names to the engineer writing migrations, and they’re missing the type, nullability, and constraint information they need to write correct DDL. A single-layer diagram forces a choice, and whichever audience loses ends up rubber-stamping reviews they can’t evaluate, or working from a document missing what they need to do their job.

What belongs in each layer

The two layers aren’t the same model shown at different zoom levels — they answer different questions and use different vocabularies for the same underlying thing.

Reading down the table, each row is the same idea seen from two directions. Money as a logical domain says “this attribute holds a currency amount, non-negative, two decimal places of precision” — a rule that’s true regardless of whether the underlying database is Postgres or MySQL. DECIMAL(10,2) is what that rule becomes once you commit to a specific engine and a specific column. The logical layer is the contract; the physical layer is one valid way to fulfill it.

The classic tooling: heavyweight and expensive

This separation isn’t a new idea — it’s decades old. Tools like ERwin and PowerDesigner built their entire product around it: a logical model and a physical model as first-class, separately maintained artifacts, connected by the tool so a rename or a new attribute in one could be pushed to the other. Enterprises with large, long-lived schemas and dedicated data-modeling teams have used this pattern for a long time, and it works — but the tools that implement it are typically licensed per seat, desktop-installed, and aimed at organizations with a dedicated data architect role to operate them.

Most web-based ERD tools that have emerged since don’t carry this forward. They model a single physical layer: tables, columns, types, foreign keys — useful for visualizing and iterating on a schema, but without a separate representation of the business vocabulary the schema is supposed to express. That’s a reasonable trade-off for a tool aimed at fast, collaborative schema design rather than formal enterprise data governance, not a criticism of any one product — it’s just where the category has settled. The gap it leaves is the one this post is about: nothing keeps the physical names honest against the business terms they represent, because there’s no separate record of what those terms were.

Naming drift: what happens without the link

Here’s the failure mode concretely. A team names a column cust_id early on, back when “customer” was the only kind of party in the system. A year later, the product grows to support both individual and business customers, and in a planning meeting the business side renames the concept from “Customer” to “Account Holder” — a change that ripples through documentation, product copy, and support scripts. Nobody renames the column. Now cust_id sits in the schema as a fossil of a term nobody uses anymore, and every new engineer who joins has to be told, out of band, that “account holder” means cust_id in the database. Multiply this by every renamed entity and attribute over a schema’s multi-year life, and the physical model stops being a readable reflection of the business it serves — it’s an archive of whatever the business was called at the moment each column was created.

The fix isn’t discipline or a reminder to “update all the names when you rename something” — that instruction gets followed for a while and then quietly dropped under deadline pressure, the same way undocumented conventions do. The fix is structural: give the logical attribute a stable identifier that’s independent of its name, and have the physical column carry a reference to that identifier rather than being a one-time copy of the name. When someone renames Customer to Account Holder in the logical model, the physical layer doesn’t need a human to notice and follow up — it looks up the current physical name for that identifier and regenerates itself. The identifier is what’s stable; the names on both sides are free to change without breaking the link between them.

Glossary as the bridge

The mechanism that makes the stable link useful, rather than just theoretically possible, is a glossary: a shared mapping from business terms and their approved physical abbreviations, checked once and reused everywhere. Instead of an engineer typing out a physical name by hand for each attribute — which is exactly how cust, cstmr, and customer end up coexisting in the same schema — the physical name becomes a derived value, generated by applying the naming convention and the glossary’s term-to-abbreviation mapping to the logical name.

A minimal version of the derivation looks like this:

Logical attribute:  "Customer Identifier"
Glossary lookup:     Customer  -> cust
                      Identifier -> id
Naming convention:   snake_case, join with "_"
Derived physical:    cust_id

The same mechanism handles the rename case from the previous section. Rename the logical attribute to “Account Holder Identifier,” and as long as the glossary has (or gains) an Account Holder -> acct_hldr entry, the physical name regenerates to acct_hldr_id automatically — no separate step, no missed column. The one exception a system like this needs is an escape hatch: some physical names are constrained by legacy data, a DBA’s explicit preference, or a downstream integration that can’t be renamed. Those get an explicit override flag that opts a specific column out of automatic regeneration, so “the glossary drives the name” and “sometimes a human overrides it deliberately” can coexist without the override silently reverting on the next rename.

When a single layer is fine

None of this means every project needs two layers. A solo project, a prototype, or an internal tool with one developer and no separate business stakeholders doesn’t have two audiences to serve — no one reviewing “Customer Identifier” is uncomfortable reading cust_id. Building a logical model, a glossary, and a derivation pipeline for a schema only one person will ever see is process for its own sake, and skipping it is the correct call, not a shortcut you’ll regret.

The value of the separation scales with two things: how many people with different vocabularies need to agree on the schema, and how long the schema is expected to live. A team schema reviewed by product and support staff who don’t write SQL, or a schema that will still be in production five years from now after multiple rounds of renaming, is exactly the case where naming drift compounds and a stable logical-to-physical link pays for itself. A weekend prototype is exactly the case where it wouldn’t. The separation isn’t a best practice to apply universally — it’s a tool to reach for once a schema has outgrown the assumption that one person, or one moment in time, gets to decide what everything is called.