Design to Third Normal Form, Then Back Off

🌐 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 5 of 6 in the chapter Modeling Data for Meaning, chapter 2 of the Data Architect course. Each module stands on its own. Previous: Put Data Where It Belongs · Next: Turn the Model into Salesforce

The question this module answers: When should I intentionally move away from a clean normalized model?

In the last module, I told you to design for Third Normal Form.

Now I’m going to tell you to break it.

Sometimes.

That’s an important distinction. There’s a big difference between accidentally building a messy data model and deliberately denormalizing a clean one. If you don’t understand where the data belongs in the first place, copying it somewhere else isn’t architecture. It’s just duplication.

But once I understand the clean logical model, I can make a conscious decision to shape the data differently for the people and systems that consume it.

Design to third normal form where feasible. Then back off when necessary.

Notice what that line doesn’t say. It doesn’t say third normal form is easier to understand. Sometimes it’s harder: a fully normalized model can scatter one business idea across five objects, and the people using it have to reassemble it in their heads. Normalization tells me where each fact belongs. It doesn’t promise that’s the easiest shape to read, use or report on. That’s exactly why backing off is a design skill and not a failure.

Start by modeling the truth. Then decide how that truth needs to be delivered.

Sit, Stay, Model

Back to the dogs, and this time we make some deliberate choices.

Breed: a picklist, or its own table? Run it through the questions I’ll give you later in this module. Is there more than one column of information? Yes: the size, at least. How many values? Kennel clubs recognize a few hundred breeds (the American Kennel Club alone lists 205), way past my rule of thumb of about 20. So Breed earns its own table, and each dog points at its breed. That settles the question we’ve carried since module 1: Breed is a box of its own.

Skill Level: a picklist. Learning, Good, Expert. Three labels, nothing else to say about them, and they won’t change. That’s exactly what a picklist is for.

How many tricks does each dog know? The answer is already in the model: count the dog’s Dog Trick records. But the trainer wants that number on every report, to sort by it and filter on it, without building a report across two tables. So I back off, and keep a Tricks Known count right on the dog. That’s a copy. The truth still lives in Dog Trick; the count is a representation of it, and it has to stay in step. Salesforce gives us a clean way to do exactly that, and we’ll see it in module 6.

Now the twist: the dog school starts taking cats. What do we do? One option: add a Cat table next to the Dog table, and a Cat Trick next to the Dog Trick. Everything doubles: two of every screen, two of every report, and the day they take rabbits, three. The other option: step back and notice that a cat has an owner, maybe a breeder, a breed, a birth date and, with patience, a trick or two. The same facts and the same relationships as a dog. So we generalize: a Pet, with a Species, dog or cat. Each breed belongs to a species too, and that’s where the species truly lives: a Labrador is a dog. The pet carries a copy, on purpose, so every screen and report can start with “dog or cat?” without looking up the breed. Another deliberate copy, and in module 6 it gets a job of its own.

That’s designing for change, and it’s a trade-off. Generalize too early and everything becomes a vague “Thing” with a type field that nobody understands. I generalize when the business actually brings me the second kind, and the two share almost everything, the way dogs and cats do here. If cats needed very different facts, I’d think twice.

Click to enlarge

Now the same kinds of choices at Steve Trucking, where the stakes are higher.

The Cleanest Model Isn’t Always the Best System

Picture a crew at a stop. The delivery has its items. The install has a checklist. Each step needs photos. The Recipient signs.

The normalized model is beautiful: Delivery → Delivery Item → Install Checklist → Photo, each where it belongs.

Then I sit down with the people who actually use it. A crew closes about ten stops a day, in driveways, often with no signal. If my beautiful model sends them through a maze of screens and related lists to finish one stop, I have an architecture problem.

The data model isn’t the only thing we’re designing.

We’re designing a system.

Sometimes the User Experience Wins

This is one of the reasons I sometimes back away from a perfectly normalized model: user experience.

Maybe a concept could technically be its own entity, but separating it adds complexity that provides very little value. Maybe a parent with several levels of children is academically clean but miserable to enter and maintain. Maybe the concept is small, stable and used mostly as a selection.

This is where I stop asking only, “Where does this fact theoretically belong?” and start asking, “How is this system actually going to be used?”

That doesn’t mean every complaint about an extra click flattens the model. It means usability is a legitimate architectural input.

Sometimes Reporting Wins

Reporting is another common reason.

Each Retail Partner wants a scorecard: on-time deliveries by region, by month. In the clean model, a delivery knows its distribution center, and the DC knows its region. Technically, we can navigate that.

But can the reporting tool? Can the people who build their own reports? Can they answer the question without a reporting architecture more complicated than the question itself?

That’s when I might copy or derive the region onto the delivery, closer to where it’s consumed.

Now I’ve introduced duplication. So I need another question: who owns the truth?

If the source value changes, what happens to my copy? Should it change? Or should it keep what was true when the delivery happened? Those are different requirements.

Denormalization doesn’t eliminate complexity.

It moves it.

Lookup or Picklist?

Here’s a smaller example that Salesforce admins meet all the time. The user needs to choose from a set of values. Do I create another entity and relate to it, or use a picklist?

Take a delivery’s status: scheduled, loaded, out for delivery, arriving, delivered, installed, exception, returned. A short, stable list of labels. A picklist is exactly what I want.

Now take the kind of stop: delivery only, delivery with install, delivery with haul-away. That one carries information of its own: how long the stop takes, what skills the crew needs, which install checklist applies. It changes independently, and the business manages it. That’s an entity, not a label.

If I create an object for every concept that could theoretically be normalized, I can build a technically beautiful system that nobody enjoys using. If I turn every concept into a picklist because it’s easy, I can paint myself into a corner very quickly.

So I ask: does it have information of its own? Relationships? Does it change independently? Does it need to be managed as data? Could the business eventually need more from it than a label?

Here are the questions I actually ask in a design session.

Is there more than one column of information? If the value needs a description, a duration, a skill list, an owner, it’s not a label any more.

How many values? My rule of thumb is about 20. Past 20 values, a picklist starts to break down, and it’s time to think about an object. States, at fifty, are the exception we live with, and countries are worse; honestly, does anyone love country picklists? I think they’re annoying.

Are the values created over time, and by whom? Adding a picklist value is a configuration change, which means an admin. If the business adds values every week, and the people adding them aren’t admins, a picklist puts a ticket in front of every new value.

Do people need to filter and report on it? Picklists are great for that. Lookups aren’t as clean. Every lookup adds a hop: a report type follows only a short chain of related objects (a custom report type allows four object relationships at most), the screen turns into related lists inside related lists, and the code gets more complicated.

A value list often starts life as a picklist and, over time, earns itself a standalone object. Let it earn it.

Click to enlarge

Sometimes Scale Changes the Answer

A design that works beautifully with a few thousand records can behave very differently with millions.

So don’t look at the diagram alone. Know the numbers.

Steve Trucking makes about a million deliveries a year, growing 30 percent a year. Each delivery collects about eight status updates. That’s eight million status records this year, and about 13.5 million a year by year three. Add four or five million photos a year.

Same model. Very different architecture.

We’ll go much deeper in Architecting at Scale. For now, remember: volume can change a data-modeling decision. A relationship that’s logically correct isn’t automatically the best physical design at every scale.

Different Consumers Want Different Shapes

So far we’ve been thinking about the operational system. Coordinators route deliveries. Crews close stops. The system changes as the business runs.

But the business also wants to analyze years of history. Which partners and regions run late? How has delivery time changed? How often do installs need a second visit?

An analytical system may want the same information organized differently. You’ll hear terms like data warehouse, fact, dimension and star schema. We’re not unpacking them here; at Steve Trucking that’s the Snowflake warehouse, and we’ll come back to it later in the course.

For now: different consumers may need different shapes of the same truth.

AI Adds Another Consumer

And now there’s another consumer: AI.

A Recipient texts, “Where’s my delivery?” An AI assistant could look up this:

Delivery 10427, Route 3312, Stop 7, Truck 117, Window 13:00–15:00.

A traditional application is perfectly happy following those relationships. But to answer the Recipient, the assistant needs context assembled from several places:

Your Redrock dishwasher is the seventh stop on today’s route from the Phoenix distribution center. The truck is three stops away, and you’re still in your 1–3 pm window.

We’ve taken information from several related entities and assembled a flatter, context-rich representation for one particular consumer.

That doesn’t mean I destroy my normalized model. And it definitely doesn’t mean “AI likes flat data, so put everything in one giant object.” Don’t do that.

The source of truth stays clean. AI gets a representation built for the job it’s doing. It’s the same idea we just saw with analytics:

One source of truth can support multiple shapes for consumption.

We’ll go much deeper into grounding and retrieval later in the course.

Don’t Create Multiple Truths

There’s a danger here.

If I copy the region onto the delivery for reporting, send a flattened version to Snowflake, and build a context-rich version for the AI assistant, which one is correct?

That question needs an answer. I want to know where the authoritative value lives. Everything else is a representation of that truth for a purpose.

That place is the system of record. Denormalized copies can be good: the stop screen, the scorecard, Snowflake and the AI context each do their job better with a shape built for them, and referring to them is fine. But when something changes, the change starts in the system of record, typically the relational model, and flows outward to every copy. Nobody fixes the region in Snowflake, or edits the AI’s context by hand. Fix it at the source, and let the change flow.

Click to enlarge

And it matters most when data changes. If a distribution center moves to another region today, should last month’s deliveries show the new region? Maybe. Maybe not. For history, I may need the region that was true when the delivery happened. That’s not a synchronization problem anymore. That’s a business decision.

This is why denormalization should never happen by accident. Every duplicated fact raises questions about ownership, synchronization, history and trust. It’s the trade-off thinking from Chapter 1: know what you’re buying (an easier screen, a simpler report, better context for AI) and what you’re paying (duplicate data, synchronization, more complicated ownership of the truth). And because we started from a clean logical model, we can point at each copy and say, “I know this belongs over there. I’m putting a representation of it here because of this requirement.” That’s a decision we can review, challenge, and write down, so the next architect knows why.

What an Architect Decides Here

When I’m considering denormalization, I’m deciding:

• Where the authoritative source of each business fact lives.
• Whether the clean model creates real problems for users, reporting, scale or performance.
• Whether a simpler representation is worth the duplication, and how the copy stays trustworthy.
• Whether historical values should change when the current source changes.
• Whether analytics or AI needs a different shape of the data than the operational system.

The starting point never changes: know where the truth belongs. Then, when the requirements justify it, shape it for delivery.

Next, Salesforce comes back into the picture. We’re going to turn what we’ve designed into a physical data model.

Explore

• Build Powerful Reports with Custom Report Types (opens in a new tab), Salesforce Admins, 24 June 2019 (checked 2026-10-12): a custom report type “can span up to 4 object relationships.”
• Unleash the hounds, terriers and lapdogs: American Kennel Club recognizes 3 more breeds (opens in a new tab), PBS NewsHour, 2 January 2026 (checked 2026-10-12): “The AKC recognizes 205 breeds, including these three newcomers.”
• For fun: the FCI Breeds Nomenclature (opens in a new tab), Fédération Cynologique Internationale (checked 2026-10-12): the international federation’s breed list, in ten groups. Scroll through it and you’ll see why Breed earned its own table.