01 · NOTES

When a Cross Reference Table
Is the Right Tool

A practical look at when reference data can make an integration more flexible, rather than simply more complicated.

A Cross Reference Table, or CRT, is a data structure that lets an integration look up a value rather than calculate it or retrieve it from another system at runtime.

There can be a meaningful performance benefit to that. Retrieving a value from memory is considerably simpler than making another database or connector call.

But in my experience, performance isn't usually the reason I reach for a CRT.

The bigger benefit is flexibility.

I've found Cross Reference Tables particularly useful in three situations: when I need to produce a result from multiple data points, when the source doesn't provide enough information, and when two systems use different naming conventions.

Translation: When two systems
speak different languages.

One of the most straightforward uses for a CRT is translating one value into another.

Maybe the HR system identifies a cost centre as 10101, while WFM knows that same business unit as "Nursing".

Neither system is necessarily wrong. They're simply using different naming conventions.

A CRT gives the integration somewhere to maintain that relationship without embedding it directly into the process. The same idea applies to item codes, units of measure, status values, state or province abbreviations, and other situations where System A calls something one thing and System B calls it something else.

This is where a CRT starts to become more than a performance optimization. It becomes configuration.

Expansion: When the source
doesn't give you enough.

Another situation I've run into is when the source system simply doesn't provide enough information to produce the result the target system needs.

Sometimes you have several pieces of information that, when combined, can determine the value you actually need. Other times, the source provides a code but not the corresponding business description or classification.

A CRT can provide that missing layer.

In those cases, the table isn't necessarily translating one value to another. It can provide a way to derive or expand the information available to the integration.

This can be particularly useful when changing the source system isn't practical, or when the additional information is really integration-specific rather than something that belongs in the source system itself.

Logic: When the rule is simple enough
to belong in configuration.

There are other places where I use CRTs that aren't quite as obvious.

They can be useful for simple switch/case or if/else-style logic, runtime-specific default values, connection defaults, and values that need to change depending on the environment, customer or deployment.

For example, if the same integration is being deployed for multiple locations or customers, a CRT can provide the values that vary between those deployments without requiring the process itself to be rewritten.

That doesn't mean every business rule belongs in a CRT. If the logic is complicated, changes constantly, or really belongs in a source system, I'd rather solve the problem somewhere else.

A CRT works well when the relationship is simple, understandable and reasonably stable — but still expected to change.

The part I value most:
change without rebuilding.

This is probably where I've come to appreciate CRTs the most.

Businesses change. A new business unit gets added. A pay rule changes. An Accrual Profile is replaced. A customer uses a different naming convention. A deployment needs a different default value.

If those relationships are buried inside the integration itself, a relatively small business change can turn into an integration change — followed by development, testing, deployment, and all the other steps that come with it.

If the relationship lives in a CRT, the integration can often remain exactly where it is.

The table changes. The process doesn't.

That's a fairly powerful distinction.

It also means the integration can be designed with growth in mind. Within reason, the CRT can grow along with the business rather than requiring the integration to be rebuilt every time a new value appears.

In many cases, that means the people responsible for maintaining the business configuration can manage the change themselves, rather than having to wait for a development cycle.

A CRT isn't magic, and not everything is CRT-worthy. And with a couple questions in hand:

I've found a few questions useful when deciding whether a lookup belongs in one:

01

Is it really a lookup?

Is there a relatively simple relationship between the input and the value the integration needs? or Would keeping the value in configuration make future business changes easier?

02

Does it belong here?

Is this integration-specific information, or does it really belong in a source system or another system of record?

03

Is the rule simple enough?

If the logic starts becoming complex, a CRT may be hiding a business-rules problem rather than solving it.

04

Will it help the business adapt?

Can a business change be handled as configuration instead of requiring the integration itself to be rebuilt?

The integration handles the process. The CRT handles the things that are expected to change.

As the business grows, this separation can make it easier to adapt without having to changing the underlying machinery.

← Back to notes