Four Candidate Keys for One Lookup

Four keys. One lookup.

The acceptance criteria named one. The technical design named another. The QA assessment named a third, and a fourth was in circulation in the same conversation. Postcode, Service_ID, S-Number, UPRN — all four alive at the same time, for a single decision the system has to make once per customer.

That disagreement was the symptom. It was not the defect.

What the Feature Does

A postcode-driven catalogue selection feature. The customer's postcode is mapped to a product catalogue in Custom Metadata, and the catalogue determines the price list they see.

Simple, declarative, easy to test. Give it a postcode, assert the catalogue. You could write those cases in an afternoon and they would all be correct.

The Question the Four Keys Forced

When four identifiers are proposed for one lookup, the natural reading is that people have not finished agreeing. Somebody will pick one, the others get struck through, done.

That reading assumes the four are interchangeable — different ways of naming the same thing, at the same level.

They are not. A postcode identifies a group of addresses. Service_ID, S-Number and UPRN each identify something narrower. So before asking which key wins, the question worth asking is the one underneath: what exactly is being decided here, and what is that fact a property of?

The Fact Underneath

The catalogue depends on whether a property has been overbuilt.

Overbuilt is a fact about an individual address. Not about a street, not about a delivery area, and not about a postcode. One property can be overbuilt while the property next door is not, and nothing about the postcode they share records that difference.

The key is one level coarser than the fact it encodes.

Why That Is a Defect and Not a Risk

This is the part that changes how the whole thing gets reported.

Any postcode that contains both overbuilt and non-overbuilt properties will show the wrong price list to some of its customers. Not occasionally. Not under load, not on bad data, not when the mapping table is incomplete.

Always, by construction. The mapping has exactly one answer available for that postcode and two kinds of customer behind it, so whichever answer is configured is wrong for the rest of them. The rate of failure is set by how mixed the postcodes are, and that is a property of the physical world, not of the software.

A perfect implementation delivers that outcome perfectly.

What It Was Not

It was not an inconsistency. The design is internally consistent — the mechanism it describes does exactly what it says.

It was not a data quality problem. Every row in the mapping could be correct and the defect stands.

And it was not a naming disagreement, though that is precisely what it looked like from the outside. The four circulating keys were not four teams being sloppy about vocabulary. They were four people each sensing, without saying so, that the identifier in front of them was not fine-grained enough for what it was being asked to decide, and reaching for a narrower one.

Key churn is the visible symptom of a granularity mismatch. When a document cannot settle on an identifier, that is usually not indecision. That is the design telling you the level is wrong.

The Check

Do it during design review, before anything is built, because after the build this defect is indistinguishable from correct behaviour in every environment you have.

Take the lookup. Write two lines:

• The fact this decides is a property of _____. Address. Account. Order line. Contract. Be specific and be honest — "customer" is usually a dodge that hides which of four objects you mean.
• The key I am given is a property of _____. Then compare the two levels.

If the key is coarser, you have found a defect you can raise from the document, with a worked consequence attached and no environment required. If the key is finer, you have a different and much cheaper problem — an over-specified lookup that will need aggregation rules nobody has written yet.

The reason this is worth a dedicated pass is that no test case finds it. Every test you write against the specified mechanism will pass, because the system will implement the wrong key flawlessly. The failure only appears in production, in the mixed postcodes, one customer at a time.

For the lookup you are testing this sprint: is the key a property of the same thing the answer is a property of?