A customer arrives with a number stamped on a broken part. Whether they leave with the right item depends entirely on your cross-reference data. That mapping is the most durable asset a parts distributor builds — it survives supplier changes, price changes and staff changes — and it is usually the least deliberately maintained thing in the business.
The schema
Four tables. The temptation is to collapse them into two, and that is the source of most cross-reference problems.
products your SKU, name, category, material
cross_references product → an OEM number this replaces + source + confidence
fitments product → an equipment model this goes into + source
supplier_parts product → what each supplier calls it
Cross-references and fitments must be separate tables. This is not schema pedantry. A cross-reference is another name for the part; a fitment is the machine it goes into. A customer who orders against a fitment expecting a component receives a machine — or, more often, receives nothing and concludes your data is wrong.
Concretely: a seal in our catalogue cross-references to other seal numbers, and separately fits equipment models CV340, CV460 and CV580. One field called "compatible with" containing both is a returns generator.
The evidence rule
Every entry gets a source, and the source determines whether it is usable.
| Evidence | Confidence |
|---|---|
| Manufacturer literature or printed catalogue | Strong |
| Source text says Replaces / Same as / OE Replaces | Strong |
| Two or more independent sources agree, no contradiction | Strong |
| One source, no contradiction | Weak — do not publish as equivalent |
| Numbers merely appear together in a listing title | Not evidence |
That fourth row is the one worth internalising. When we tested a batch of entries whose only support was a single source, the error rate was 19%. Entries with two or more independent confirmations showed none in the sample we checked.
A single source is not confirmation. It is one person's keyword field.
Where the errors come from
Most cross-reference data is built by mining marketplace titles, and titles are keyword fields rather than specifications. Five patterns produce almost all the bad entries:
The number belongs to the assembly, not the part. A cartridge listed with its valve's number, or a valve listed with its cartridge's number. They appear in the same titles constantly because anyone servicing one searches for the other.
It is a case SKU. Source text saying Must order case of 12 means the number is a pack, not a piece. A customer ordering 50 gets 600.
Sibling models pointing at each other. Family members are listed together and compared, and scrapers read adjacency as equivalence. Three sizes of the same valve are not interchangeable.
The parent number. A single grid cross-referenced to the complete grid assembly. Both numbers legitimately appear in documentation; the difference is scope, which titles do not record.
One title, several relationships. We had a title containing five numbers. Read as a list of equivalents that is five cross-references. Read carefully it described two different o-rings and a kit containing them — only two were aliases.
Two audits worth running on a schedule
Audit 1: contradiction between tables. Look for a number registered both as a cross-reference and as a fitment on the same product. Logically both cannot be true — either it is another name for the part or it is the machine the part goes into. Where both appear, one is wrong.
This single query found nine bad entries in our data.
⚠️ It also produces false positives you must check before deleting. Two numbers that look identical after stripping punctuation — one written with a space, one with a hyphen — got flagged as contradictory. They are genuinely different things: one a filter tank model, one a cartridge part number. Normalisation creates collisions. Check before you act.
Audit 2: sibling detection. Flag entries where the OEM number differs from your own part number, or from another entry, by one or two characters. Most will be legitimate; the false-positive rate is high enough that this is a review queue rather than a delete list.
Deduplication and normalisation
You need a normalised form for matching — uppercase, punctuation stripped — because customers type spx-1600-z2 and SPX1600Z2 and mean the same thing.
Store both. Match on the normalised form, display the original. And keep the collision risk above in mind: normalisation makes CFR 50 and CFR-50 identical, and if those are two different things in your catalogue, your matching will conflate them.
Maintenance, not a project
The mistake is treating this as a one-time import. Three habits:
Log the searches that returned nothing. Every zero-result search on your own site is a customer telling you about a gap — either a part you should carry or a cross-reference you are missing. This is the highest-value data source you already own and almost nobody captures it.
Record every fitment complaint against the entry that caused it. When a customer reports a wrong part, trace it to the cross-reference and fix the entry, not just the order.
Re-audit annually. Data decays: equipment lines get revised, numbers get superseded, and entries added in a hurry stay in forever unless something looks for them.
What good looks like
A defensible database is not the biggest one. It is one where every entry has a source you could show a customer, contradictions have been queried for rather than waited for, and weak entries are marked as weak rather than published as facts.
Size follows from that. Ours currently holds around 1,200 OEM numbers across the catalogue — a number that went down during the last audit, and is more useful for it.
Sourcing
POOLPOINT maintains cross-reference and fitment data as separate datasets with sources recorded, and re-audits rather than treating the import as final. If you are building your own catalogue we will supply the data for the parts you buy in an importable form — including telling you which entries we hold as weak rather than confirmed.