Draw the Relationships

🌐 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 2 of 6 in the chapter Modeling Data for Meaning, chapter 2 of the Data Architect course. Each module stands on its own. Previous: Listen for the Nouns · Next: Build the Logical Model

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

We have nouns on the board. Now I want to know about the lines between the boxes, because a collection of entities isn’t really a data model yet. The relationships are where much of the meaning lives. A Retail Partner operates distribution centers. A manifest lists deliveries. A delivery goes to a Recipient. A crew runs a truck. Every one of those statements tells me something about the business, and every one gives me another question to ask.

Start With Business Language

I still don’t want to talk about Salesforce. I’m not asking whether this should be a lookup or a master-detail relationship, thinking about Salesforce Junction Objects, or deciding what happens when a parent record is deleted. I’m trying to understand the business relationship.

Take “a Retail Partner operates distribution centers.” It sounds simple. But can a Retail Partner have one DC or many? At Steve Trucking, each Retail Partner operates a regional distribution center in every region Steve Trucking serves, so many. Can a DC belong to more than one Retail Partner? Does a DC matter on its own, with its own staff and history, or is it just an address? If a Retail Partner leaves, do we still care about its DCs?

Those aren’t Salesforce questions. They’re business questions, and the answers can completely change the model.

Read the Lines

Before we go any deeper, I want to give you the language we’ll use on every diagram from here on. It’s a small one. A line between two boxes says the two things are related, and the marks on its ends say how many.

Start with a plain line, no marks at all. That’s where I start on the whiteboard: I know these two nouns are related, I just haven’t asked how yet. Then the ends get marks. A single bar across the line, the cross, means one. The crow’s foot, the end that splits into three like a bird’s foot, means many. Later in this module you’ll see a second mark appear next to the first, a bar or a circle, and that one answers a different question: can there be none?

The one rule to remember: you read each mark at the box it touches. The mark nearest the box is the most there can be; the next mark out is the least.

Click to enlarge

Sit, Stay, Model

Before Steve Trucking, back to the dogs from module 1. We had five boxes: Owner, Breeder, Dog, Breed and Trick. Now I ask about the lines between them.

One or many? An owner can have more than one dog; Ana has two. A dog has one owner, at least in our model. That’s one-to-many: a bar at the Owner, a crow’s foot at the Dog. The breeder is the same shape: one breeder breeds many dogs.

Can there be none? Max came from the pound, so he has no breeder at all. The Breeder end gets a circle: a dog has zero or one breeder. Every dog has an owner, though, so that end gets a second bar: exactly one.

Many to many? A dog knows many tricks, and a trick, say Sit, is known by many dogs. That’s many-to-many, and that’s exactly where I go looking for a hidden concept. Is there anything about this dog knowing this trick? How well she does it, and when she learned it. The relationship has facts of its own, so it becomes a thing of its own: Dog Trick, with the dog’s name first, because the dog is the one that drives it.

What does each line mean? An owner owns a dog. A breeder breeds it. A dog knows its tricks. Name the lines, and anyone can read the model back to you.

Now, or over time? Does anybody care how good Bella is at Sit today, or how she got there? If a trainer checks her every few months, each check is a record of its own: a Trick Assessment, with the date, the skill level and who did the checking, under the Dog Trick it belongs to. Even a relationship that became a thing can have history of its own.

Breed I’m leaving unconnected for now. It’s still the question from module 1, a box of its own or a word on the dog, and module 4 settles it.

Click to enlarge

Every one of those questions works the same way on a real business. Now let’s ask them of Steve Trucking.

Ask “One or Many?”

One of the first things I want to understand about a relationship is its cardinality. That’s the database term; the question is much simpler: how many of these can relate to how many of those? At the conceptual level there are three basic patterns.

This is an important step in the process: take two different nouns and understand their relationship. In most situations it’s a one-to-many, or a many-to-one. Only in rare circumstances will you run into a one-to-one.

One-to-many is everywhere. One Retail Partner has many distribution centers. One manifest lists many deliveries. One route has about ten stops. When I hear language like that, I start drawing the line.

And now the line gets its marks. Each end tells you how many of the box it touches: the bar for one, the crow’s foot for many. Read it from each box: one Retail Partner has many Distribution Centers.

Click to enlarge

One-to-one relationships exist, but they’re rare, and when I find one I ask some questions. Are these really two different nouns, or the same one? If they’re different, what is the one-to-one relationship between them? And will it always be one-to-one, or only one-to-one right now? These are key questions in designing the data model. A crew and a truck look one-to-one on any given morning: two people, one truck, one route. Over a month they’re not, and that’s where the most interesting relationship in this model is hiding.

Many-to-many relationships are especially interesting, because they often tell us there’s another concept in the business. Can a crew drive more than one truck over time? Yes. Can a truck be driven by more than one crew? Over its life, certainly. So crews and trucks have a many-to-many relationship. But now I want to know more. Does the business care when a crew had a truck? For which route, on which day? Who made the assignment? If the relationship itself carries information we care about, we’ve discovered another business concept: the Crew Assignment.

That’s an important modeling habit. Sometimes the most interesting thing isn’t one of the nouns we heard at first. It’s hiding in the relationship between two nouns.

Relationships Can Become Things

This is one of the places I want admins to start thinking differently. In Salesforce we’re used to talking about objects and fields, so it’s easy to treat the boxes as the important part and the lines as plumbing. But the relationship itself can have business meaning.

Consider a delivery and the products on it. A delivery can carry several products, a washer and a dryer, say. A product, the same refrigerator model, shows up on thousands of deliveries. Easy enough. But what if I need to know how many of that product are on this delivery, whether it’s being installed, or whether the old one is being hauled away? I’m no longer interested only in a relationship between Delivery and Product. I’m interested in a Delivery Item, and that concept deserves an entity of its own.

If you’re coming from Salesforce, you may already be thinking, “That’s a junction object.” You’re not wrong. But I don’t want to start there. A Delivery Item is a business concept before it’s a Salesforce Junction Object. Business first, implementation second.

A few habits of mine for these relationship entities, wherever they end up being built.

Name it. If the business already has a word for it, use theirs. If it doesn’t, take the names of the two parents and put them together. Think of an order and a product. The thing between them is the Order Item: the order, and the item on the order. It’s a child of both parents, and in Salesforce it may end up as a Salesforce Junction Object. But which name goes first: Item Order, or Order Item? Look at what kind of data each parent is. The product is reference data; the order is transactional, and it’s the order that drives it. So it’s Order Item, or Order Product, the same way Salesforce has Opportunity Product. At Steve Trucking it’s the Delivery Item. The driving name goes first, and the record sorts next to its owner in every list you’ll ever look at.

Give it its own key. It’s tempting to say a Delivery Item is identified by Delivery plus Product and stop there. That’s a compound key, and compound keys are much harder to deal with: every system that points at the record has to carry both halves. Give the relationship entity its own identifier, and keep Delivery plus Product as a rule about uniqueness, not as the identity.

Click to enlarge

Notice what isn’t on it: a serial number. A quantity and a serial number don’t belong on the same record. A quantity counts several of a thing; a serial number identifies one particular thing. If the business needs serial numbers, each unit becomes its own record, and five refrigerators are five records. I can’t see a case for a Delivery Item with a Delivery Item Detail under it. It’s one or the other: an aggregate with a quantity, or one record per unit. Here, it’s the quantity.

Watch the attributes that want history. Some relationship entities are settled once they’re created: the washer on this delivery stays the washer on this delivery. Others keep changing. A crew has truck 117 this week, and next week the truck goes to another crew. When the relationship changes and the business cares about what it used to be, the relationship entity needs dates, and the Crew Assignment is the one to watch. Ask early whether the business cares about now or over time; we’ll come back to it in a moment.

Relationships Have Meaning Beyond Cardinality

“One-to-many” isn’t enough to understand a relationship. I also want to know what the relationship means.

A delivery has a location. Which one? The distribution center it leaves from? The Recipient’s home it goes to? Those aren’t interchangeable. Two models can have exactly the same boxes and the same cardinality, and the lines can mean completely different things. That’s why a good model isn’t just boxes connected by lines. The lines need names. If I hand another architect my diagram, they shouldn’t have to guess what a relationship means, and if I’m showing it to the business, it should use language they recognize.

Click to enlarge

Optionality Matters Too

There’s another question hiding inside “how many?”: is the relationship required? Can a delivery exist before it’s been put on a route? It has to: the manifest arrives a day or two before delivery, and routes are finalized the evening before. Can a delivery exist without a Retail Partner? No; every delivery comes from a partner’s manifest. That’s optionality.

Again, don’t jump straight to a Salesforce Required checkbox. There’s a difference between saying “every delivery must have a Retail Partner” and saying “Salesforce should require the Retail Partner field when the record is created.” They may end up as the same implementation, but they aren’t the same statement. The first is a business rule; the second is one way of enforcing it. As architects, we want to understand the first before deciding the second.

On the diagram, optionality is the second mark on each end. The mark nearest the box is the most there can be: the bar for one, the crow’s foot for many. The next mark out is the least: another bar means at least one, a circle means it can be zero. So every Delivery is on exactly one Manifest, and a Manifest lists one or many Deliveries. A Delivery is at zero or one Stop, none until it’s been routed, and a Stop drops off zero or many Deliveries.

Click to enlarge

Time Changes Relationships

Here’s a question I ask a lot: do you mean now, or do you mean over time?

A crew runs a truck. Right now? For today’s route? For as long as those two people work together? Those produce different models. If the business only cares which truck a crew has today, the model can stay simple. If it needs every truck a crew drove, when, and on which route, we need history, and that Crew Assignment we discovered suddenly matters a lot.

Click to enlarge

This is a common source of modeling mistakes. We model the current state because that’s what we can see on the screen. Then, weeks later, a Retail Partner files a damage claim and somebody asks, “Which crew had truck 117 on March 17?” And we discover we’ve been overwriting the answer every time the assignment changed.

One way to keep that history is to make the assignment a record of its own. When a crew is assigned, we create a Crew Assignment with a start date. When the assignment changes, we put an end date on the old record and create a new one with its own start date. Now “who had that assignment six months ago?” is a simple question to answer, instead of a dig through field history or a log of data changes. And once a crew has had many assignments, someone will ask which one is the current one, so I’d add an Active flag too.

Click to enlarge

So when someone tells me about a relationship, I don’t just ask how the things are related. I ask: does that relationship change, and do we care about its history?

Draw Before You Configure

Our Steve Trucking model is becoming useful. We know which relationships are one-to-many, where a many-to-many hides another concept, which relationships are optional, and which ones change over time. And we still haven’t created a Salesforce object. That’s intentional.

Click to enlarge

This diagram should be something I can put in front of the business, and they shouldn’t need to know Salesforce to tell me whether it’s right. If they can’t validate it because it’s full of Salesforce terminology, I’ve moved too quickly into the physical design.

What an Architect Decides Here

When I’m working through the relationships in a conceptual model, I’m deciding:

• How the major business entities relate to each other.
• Whether each relationship is one-to-one, one-to-many or many-to-many, now and over time.
• Whether each relationship is required or optional.
• Whether a many-to-many relationship is hiding a meaningful business concept.
• Whether the relationship changes over time, and whether we need its history.

Lookup or master-detail, junction configuration, cascade behavior and sharing are physical decisions; we’ll get there. Right now we’re still answering the more important question: how does this business actually work? Once we understand the entities and the relationships between them, we can start adding the details that describe them. That’s when our conceptual model begins to become a logical data model.