Flow
Pieces of the link, in order
1Link table
Its own rows: register product, ordering item, status, optional store.
2Strict matcher
Brand plus name; one match links, zero or many link nothing.
3Two read rules
Add stock across links; take sales from one route, then fall back.
4Honest counts
Only all-digit IDs count as register products in sync reports.
Two lists of the same products live in the ordering system, and the old link between them was an ID that the imported items don't have.
One list comes from the store registers. It records what happened: what sold, and what's on the shelf. The other list comes from vendor spreadsheets. It holds what I decide: names, case sizes, costs, and what I can order. The planner asks about the second list, but the answers live in the first. When an item didn't line up, it looked like nobody bought it. That's my reading of the old code, anyway.
Keep the links in their own table
The change adds a separate table for links. Each row says which register product points at which ordering item, plus a status and an optional store. The sync stores every register product as "unmapped" first. It keeps the facts without making anything orderable, and without guessing.
Link only when there's one clear match
After each sync, a step matches brand plus name, in lowercase with extra spaces removed. One match makes a link. No match, or two or more, makes nothing, and the ties get counted. If a link was rejected, or points somewhere else already, it leaves it alone. This will miss real matches, and I'm fine with that. A missing link shows up in a count. A wrong one would put one product's sales on another and nobody would notice.
Read stock and sales differently
Stock is a snapshot, so it's safe to add the item's own stock to the stock of its linked products in each store. Sales are events, so adding two routes counts the same sale twice. Use one route first and only fall back for the rest.
Items count as covered when the linked route returned rows for them, and only the rest go through the old route.
One catch: "covered" here means "had sales in this time window", not "has a link". A linked item with a dead week falls back to the old route. Usually both say zero, but I haven't proven it.
Watch your counts
Only count all-digit IDs as register products, or spreadsheet items will pad the numbers. Have every sync report how many products it found, how many are linked, and which ones aren't. Test the matcher with a supplier that sells two brands, a name under the wrong brand, and two items with the same name.
The part I'd rather not have
The read code now checks whether it's talking to a fake test database, and if so it skips the new path. So the old tests pass without running the new joins. Next I'd point the linked reads at a real database, with one item fed by two register products, and check that the totals add up once.