Build the Logical Model

🌐 Also in 28 languages

Español · 日本語 · Português (Brasil) · Français · Deutsch · Italiano · 한국어 · 简体中文 · Português (Portugal) · 繁體中文 · Bahasa Indonesia · Türkçe · Polski · Tiếng Việt · ไทย · Nederlands · العربية · עברית · Українська · हिन्दी · Русский · Svenska · Dansk · Norsk bokmål · Suomi · Español (México) · Français (Canada) · Deutsch (Schweiz)

Image generated with AI

Module 3 of 6 in the chapter Modeling Data for Meaning, chapter 2 of the Data Architect course. Each module stands on its own. Previous: Draw the Relationships · Next: Put Data Where It Belongs

The question this module answers: How do I turn a conceptual model into a logical data model?

The conceptual model told us what exists at Steve Trucking and how it relates: Retail Partners and their distribution centers, manifests and deliveries, Recipients, routes and stops, crews, trucks and crew assignments. That’s enough for a very productive conversation with the business. But eventually I need more detail. What do we need to know about a delivery? What makes one distribution center different from another? How do we identify a Recipient? What belongs to a crew assignment rather than to the crew or the truck? Those questions move us from a conceptual model toward a logical data model, and we’re still not building Salesforce yet.

Sit, Stay, Model

First, back to the dogs. We have the boxes and the lines. Now two new questions: what do we know about each thing, and how do we tell one from another?

What describes it? A dog has a name and a birth date. An owner has a name, a phone number and an address. A trick has a name and a difficulty. Each fact goes on the box it describes.

Then the junction. How well Bella does Sit, and when she learned it: does that describe Bella? Not quite; she’s better at some tricks than others. Does it describe Sit? No; every dog does Sit differently. It describes Bella and Sit together, so it belongs on the Dog Trick: the skill level and the date learned. That’s why the junction exists: some facts only belong to the pair.

Which dog is which? Try the name first. There are a lot of dogs named Max, so the name isn’t unique. The owner’s phone number? Marcus has two dogs on the same phone, people change numbers, and it’s somebody’s personal data, which would get copied into every system that ever points at a dog. The microchip number looks better: it’s unique, and it doesn’t change. But a new puppy may not be chipped yet, and a key can’t be blank. So the microchip stays, as something the dog is also known by, and the dog gets its own Dog ID: a number that does one boring job, telling one dog from another.

Click to enlarge

Those are the same questions we’ll now ask of Steve Trucking, and the reasons the name, the phone and the microchip each fall short turn into a rule I’ve held for years.

Conceptual Tells Me What Exists; Logical Tells Me What It Means as Data

Take Truck. What do I need to know about a truck? Maybe its unit number, VIN, make, model, model year, license plate, current mileage and status. Those are attributes of Truck.

Now take Delivery. Maybe the Retail Partner’s order number, the delivery date, the promised window, the actual arrival time, the status, and whether it includes an install or a haul-away. Again, attributes.

The conceptual model told us that Delivery exists. The logical model starts telling us what a delivery means as data. A simple box like Truck or Delivery may stand for a whole set of fields.

Click to enlarge

Even so, in most of our diagrams the boxes stay simple, for three reasons. The number of fields grows. Fields quickly become obsolete. And each field has details that are hard to show in an object model: its API name, label, data type and length. Those details will definitely be captured, just not in the first data model. When showing a few key fields really helps tell the story, I’ll draw a detailed data model: a focused slice, with only the fields that matter to the point.

Attributes Should Describe the Entity

Here’s a simple question I use when I look at an attribute: what does this describe? Truck make clearly describes the truck. But what about the crew members’ names on a delivery? Do they describe the delivery? Or do they describe a crew, which had a crew assignment, which covered the route, which included this stop?

This is where a data model starts exposing things that look perfectly reasonable on a screen. A coordinator says, “I want to see the crew’s names on the delivery.” That’s a valid user-experience requirement. It isn’t necessarily a requirement to store the crew’s names on the delivery. Those are two different things, and we’ll come back to it with normalization. For now, get into the habit of asking what thing this fact actually describes. That question will save you a lot of trouble.

Then We Have to Talk About Unique IDs

Attributes describe things. But there’s another problem to solve: how do I know which thing is which?

Two Recipients are named John Smith. Are they the same person? Maybe; maybe not. A name isn’t enough. An address? People move. Phone numbers change. Email addresses change. Even a Retail Partner’s name can change.

This brings us to keys: what lets us uniquely identify something in our model. It’s an area where I’ve developed a pretty strong opinion over the years. For an identifier, I want four things:

Unique. Unchanging. Meaningless. Never PII.

Unique. An identifier has to identify exactly one thing. If two deliveries can have the same value, it isn’t a reliable delivery identifier. Names usually aren’t unique, addresses aren’t necessarily unique, and phone numbers get shared. Even values that look unique today may not be once you have enough data. So ask: can two of these ever have the same value? If the answer is yes, be very careful about using it as the record’s unique ID.

Unchanging. This one gets missed. If we identify a Retail Partner by its name and the partner renames itself, did we get a new partner? No. The business entity stayed the same; one of its attributes changed. A record’s unique ID should survive changes to the thing it identifies, which rules out names, email addresses, phone numbers, addresses and usernames, however convenient they look when we’re first building a system.

Meaningless. This sounds strange the first time you hear it. Why would I want an identifier to be meaningless? Because meaning changes. Imagine someone builds a delivery ID that encodes the DC, the Retail Partner, the delivery date and a sequence number: PHX-KEY-20261002-0042. It looks clever. Then the Recipient reschedules to Friday. Do we change the identifier? If we don’t, it’s lying to us. If we do, we’ve changed the delivery’s unique ID because one of its attributes changed, and every system that stored the old ID has just lost track of it. The identifier was trying to do two jobs: identify the delivery and describe it. I’d rather let the attributes describe it and let the identifier do one boring job: identify it. Boring identifiers are good identifiers.

Never PII. This one is important enough that I keep it separate. Don’t use personally identifiable information as an identifier just because it looks unique. At Steve Trucking it’s tempting to recognize a Recipient by phone number. But a key gets copied everywhere systems need to connect records: integrations, exports, logs, status files back to the Retail Partners, the data warehouse, test data. Make a phone number the key, and you’ve spread a Recipient’s personal data through the whole architecture.

A key also has to be there for every record: never null or blank. If some people won’t, or shouldn’t have to, give you a value, that value can’t be the key. That’s why systems issue their own internal identifiers, and personal data, if you need it at all, becomes a protected attribute.

So my rule is: unique, unchanging, meaningless, never PII.

Natural Keys and Surrogate Keys

Database people have names for some of this. A natural key already exists in the business data: a truck’s VIN is a good example. Our application didn’t invent it, and it already has meaning. A surrogate key is created specifically to identify the record; it doesn’t describe anything, it just identifies.

In most architectures I prefer a surrogate as the internal record ID and keep the important natural identifiers as attributes. That doesn’t make natural identifiers bad; they’re often extremely important. I just don’t automatically make them the record’s unique ID. Ask what happens if the value is corrected, reformatted, replaced, reused, missing, or arrives from more than one source system. That’s where a simple-looking decision becomes an architecture decision.

One Thing Can Have Several Useful Identifiers

The internal record ID isn’t necessarily the identifier every other system uses. Steve Trucking might identify a delivery internally with a generated ID. Each Retail Partner knows the same delivery by its own order number: Keystone in its CSV manifest, Redrock in its API, Brightwave in its EDI messages. And the eight years of history in RouteDesk, the legacy dispatch tool, use RouteDesk’s own delivery IDs.

Those identifiers matter. We need to keep them and map them correctly, because a status update has to go back to each partner under its number. But they don’t all have to become the delivery’s unique ID. “What’s its ID?” can have more than one answer, and the architect has to know which ID is authoritative in which context. We’ll come back to this with master data and integration.

Relationships Depend on Unique IDs

Now go back to the lines we drew. A manifest lists deliveries. A Recipient receives deliveries. A crew has crew assignments. Those relationships only work because we can reliably identify the things at both ends.

Here’s where it bites at Steve Trucking. A Retail Partner revises its manifest on the day: an order cancelled, an item swapped, a new address. If we can’t match the revised line to the delivery we already have, by the partner’s order number, we create a second delivery for the same refrigerator, and a crew may show up twice. Boxes, attributes, keys and relationships aren’t separate ideas. They’re all parts of the same model.

Click to enlarge

At this point you may be thinking, “Salesforce gives every record an ID.” Correct, and we’ll get there. If the data lives in Salesforce, every record gets a Salesforce-generated ID. It’s cryptic and hard to read, so we usually also expose an ID field people can use, like a delivery number D-000184227, an Auto Number field. People often think that number is the record’s ID, but it isn’t: the Salesforce ID is still there underneath. An external system or database, Oracle or SQL Server, has its own unique IDs too. But the architecture may eventually include Salesforce, the finance ERP, Snowflake, Data 360, the partners’ systems and systems that don’t exist yet. The logical model describes the business data without making every decision depend on one technology. How Salesforce physically implements it is a later step.

What an Architect Decides Here

As I turn a conceptual model into a logical model, I’m deciding:

• Which attributes actually describe each entity.
• How each entity is uniquely identified, and whether that identifier is stable enough to be its unique ID.
• Which business identifiers (partner order numbers, legacy IDs) need to be kept and mapped.
• Whether sensitive information is being used as a key.
• Whether relationships connect entities through reliable unique IDs.

We now have entities, relationships, attributes and unique IDs: the beginnings of a real logical data model. But there’s still a problem. It’s very easy to put perfectly valid data in the wrong place. That’s where normalization comes in.

Explore

• Custom Field Types (opens in a new tab), Salesforce Help (checked 2026-10-09): Auto Number assigns each record a unique number with a display format such as a prefix and leading zeros; it isn’t the record’s Salesforce ID. See also Auto-Number Fields Are Not Always Sequential (opens in a new tab) (checked 2026-10-09).