A code is a join, not a label

Three kinds of key on one record

A record's code looks like a label on a card. It is not. Other tables hold on to it, and reports group by it. Changing one is a data change, not a cosmetic one. This lesson is about choosing a numbering scheme before the first record exists.

Open a buyer at Riche Terre and you can see two of its three identifiers. BUY-104 is printed at the top. Its ERP code, if somebody has mapped this buyer to an accounting system, sits on the record as a second key belonging to somebody else's software. The third is the database row identifier, and you will never see it, which is deliberate.

The visible code used to be derived from that hidden row identifier, and the application's own notes explain why that had to change. The internal counter survives a data wipe, so a freshly cleaned workspace carried on counting from the demo records it no longer had. A new customer's first style came out as STY-138. Codes are now their own thing, with their own counter, set per workspace.

Every screen, field and rule named on this page was read out of the application itself. Where the screen in front of you disagrees with the page, the screen is right and the page is out of date.

Thirteen counters

Settings → Numbering at /settings/numbering lists every numbered record type, one row each, with its prefix, its padding and the number the next record will get. There are thirteen.

Three things are editable per row, and each has a bound the screen enforces.

• Prefix — one to eight letters or digits, stored upper-cased. Anything else is refused in words.
• Padding — zero to eight digits. Zero means no padding at all.
• Next number — at least one, and refused above one hundred million as too large to be real.

The screen states the consequence of an edit in one sentence, and it is the sentence to remember: changing a scheme never renumbers existing records.

The counter is not a count

Claiming the next number is one database statement. The increment and the read-back happen together, so two people creating a record at the same instant can never be handed the same number. That is the guarantee.

The guarantee runs one way only. The application's own note on the mechanism is exact: a failed create leaves a harmless gap, never a duplicate.

At Riche Terre the style library's highest code is STY-1043, and the style list shows 921 styles. Styles start at STY-101, so 1,043 minus 101 plus 1 is 943 codes claimed against 921 real styles. Twenty-two numbers are missing somewhere in the middle. Yashvin spent an afternoon looking for twenty-two lost styles that were never created.

Nothing is wrong. A code is a claim ticket, not a tally. If you want to know how many styles you have, count styles.

Padding is a decision you make once

Riche Terre never changed its style numbering. It took the default — prefix STY, padding 0, first code STY-101 — and left it alone for three years, which is the sensible-looking thing to do.

Three hundred styles a year later the library passed STY-999 and carried on into four digits. Nothing broke. Then somebody exported the style list to a spreadsheet and sorted it, and the newest forty-four styles were at the top.

Read that table twice. The four-digit codes do not sit after the three-digit ones, which would be merely wrong. They interleave: ten four-digit codes, then STY-101, then ten more, then STY-102. The forty-four newest styles are scattered through the first forty-eight rows, and the oldest style in the building is in eleventh place. Sorting by code no longer sorts by anything.

The reason is one character. Comparing STY-1000 with STY-101 as text, the first four characters match, and then a zero meets a one. Zero is the lower character, so STY-1000 wins, and the number after the digits never gets read at all.

Setting padding to four now does not help. It changes what the next code looks like, and the next code is already four digits. It leaves STY-101 through STY-999 exactly as they are, because changing a scheme never renumbers existing records, and there is no renumber action anywhere in the application. The 899 three-digit codes are permanent.

Had Yashvin set padding to four on day one, the first code would have been STY-0101. Every code since would have been four characters wide, and a text sort would have stayed a chronological sort for the next nine thousand styles. It costs nothing on day one, and on day one thousand you cannot buy it back.

What actually joins on what

A reference value is stored on records, and this is where a rename stops being cosmetic. The application had to repair a set of duplicated reference values once, and the repair had to know exactly which column holds what. Its configuration is the clearest map available of how these joins really work.

Read the last three rows carefully, because they are the ones that catch people.

Defects are recorded by label. Rename Skipped stitch to Skipped stitches in Settings and every defect recorded before that moment still says Skipped stitch. The two are now different strings, and a report that groups by defect type will show two rows where there was one. Nothing errors. The number is simply split.

Styles are worse, in an interesting way. The column holds whichever of the two the record happened to be created with. That is why the duplicate repair matches on both, and it is why a fabric type added inline keeps its label as its code. Making the two identical is the only reliable way to be safe against a column that might hold either.

The rename that has to reach two tables

Buyers are the exception that shows the rule properly, and it is worth knowing because you will do it.

An order stores two things about its buyer: the identifier of the buyer record, which is a real join, and the buyer's name at the time, which is a snapshot. The snapshot exists because reports group by it, and an order list has to show a name without opening a second record.

So renaming a buyer cannot be one update. When Cazaubon Mode becomes Cazaubon Group, the app writes two statements as one batch: the buyer's own name, and the snapshot on every order belonging to that buyer. Riche Terre had forty-one such orders, and all forty-one snapshots move together. A crash between the two cannot leave them disagreeing.

One detail in that batch is a deliberate omission. The orders' own last-modified timestamps are not touched, because renaming a buyer is not editing an order. If it bumped them, forty-one orders would appear on somebody's recently-changed list, and the list would be lying about what happened.

That is the whole lesson in one change. A code is a join. A snapshot is a copy that has to be kept in step. And a timestamp is a claim about what somebody did, which is why the app declines to make a false one.

Help me choose a numbering scheme for every record type in my workspace, on the understanding that changing it later never renumbers anything already created.

For each record type — styles, orders, purchase orders, purchase requests, goods received notes, materials, suppliers, buyers, quotations, requests for quotation, production orders, inspections and material issues — ask me three things and then recommend: what prefix my people already say out loud, how many of this record I expect to create in five years, and whether the code will ever be typed by somebody outside my factory.

From my answers, recommend a prefix and a padding width. Justify the padding by the five-year volume rather than by taste, and show me what the codes look like sorted as text at the start, in the middle and at the end of that range. If I choose no padding, show me explicitly what happens when the count passes nine, then ninety-nine, then nine hundred and ninety-nine, and where those codes land in a sorted export.

Then tell me three things about what a code is not. It is not a count, because a failed create can leave a gap and the system guarantees no duplicates rather than no gaps. It is not a label, because other records and other people's systems are holding on to it. And it is not private, because it will end up on a document a buyer reads.

Last, ask me whether any of my records also carry a code from another system, such as an accounting package. If so, tell me to keep that code in its own field rather than shaping mine to match it, and explain what breaks if the two ever have to be told apart.

One rule. Do not recommend renumbering anything that already exists. Tell me what it would cost and then tell me not to.