Search that works when nobody knows the reference

A clerk who knows the customer's name, the town and the last thing they bought still cannot use a box that accepts only the invoice number. Search works when it takes what the person remembers — a name as they pronounce it, three digits of a phone number, part of a reference, a date they half-remember — and ranks results by which record they will need next. It flips at the till, where the input is a barcode and one scan must open one product: there a forgiving search is slower than the scanner and adds a way to pick the wrong item.

What does the person at the counter actually type?

Watch twenty searches at a counter and write down the keystrokes. Almost none is a primary key. Four inputs repeat.

What is typed	What it stands for	What an exact-match box returns
محمد or Mohamed	a name, in one of its spellings	nothing, if the record uses the other
0555 12	a phone fragment	nothing, unless the whole number matches
B104	a reference copied off a paper slip	nothing, if the record says B-104
last Tuesday	a day they half-remember	nothing, unless they guess the date

None of these is a mistake. The record holds a different spelling, order or separator, and the knowledge the clerk has — who this is — stops being usable when it matters.

Why does an exact-match box feel broken?

Because the box promises that typing what the record holds will bring it back. Zero results cannot tell the clerk whether the customer is absent or spelled differently, so both look identical — and the answer is the same: ask for a number the customer does not know, then open the paper ledger.

The cost is not one failed search. A system holding a complete record earns a reputation for missing records, and the team keeps its own list beside it: the second set of books. The opposite failure ends the same way: a box that returns three hundred rows in alphabetical order is also one the clerk stops opening.

How do you handle محمد, Mohamed and Mohammed?

The same customer is محمد in the shop's Arabic records, Mohamed on the invoice the accounting package prints, and Mohammed in a supplier's message. Three strings, one person. A search that compares strings is testing spelling.

• Normalise when the record is written, not when it is searched. Strip diacritics and tatweel, fold أ إ آ to ا, ة to ه and ى to ي in the search copy, and leave the original untouched for display and print.
• Fold the Latin variants the region produces. kh and h, ou and u, doubled consonants, ee and i, dh and d. A consonant skeleton catches most of the rest, while the screen shows the name as the customer writes it.
• Let the counter teach the system. When a clerk types a new spelling and picks the right customer, save that spelling as an alias on the record. The variants grow from real use and nobody maintains a dictionary.
• Keep one identifier that never changes, and let name, phone and spelling be the ways in. Identifiers another record can point at without ambiguity are what the W3C's data-on-the-web guidance asks of datasets — if a customer's ID changes when a name is corrected, the invoice and payment that pointed at it break.

What order should results come back in?

Rank by what the person will do next: an exact or prefix match above a fuzzy one; a customer who ordered this month above one dormant for two years; the clerk's own branch above another wilaya; a hit in the phone above a hit buried in a street name.

Show why each row matched, with the fragment highlighted: a result the clerk cannot account for is one they scroll past, including the right one. State the count and offer one way to widen: "4 results, include archived and other branches". A box that silently excludes records is worse than a slow one: the exclusion looks like absence.

What makes it fast enough to use while a customer waits?

It has to answer with somebody standing there: under a second, on the connection the shop actually has, not the developer's laptop.

Most slow searches share a cause: a %term% match over a large table, joins evaluated before anything is filtered, five entity tables queried in a loop. The fix is structural. Build one table for search — entity type, entity id, normalised text, a rank hint, a timestamp — fill it when records change, and query it once. Index what fragments land in: a trigram index for "contains", a plain index for "starts with" and for the phone column. Cap the result set at twenty rows and join detail only for those.

How do you build it, in the right order?

1. Sit with the person who searches and write down twenty real queries, including the ones that failed.
2. For each entity the search covers, list the fields people use to find it: name, phone, reference, date.
3. Normalise at write time, keep the original for display, store the aliases the counter teaches.
4. Build the single search table and the job that keeps it current.
5. Index the fragment columns, cap the result set, rank by what the person needs next.
6. Log every search that returns nothing and review it weekly — the best requirements document you will get after launch.

Where this goes wrong

• Searching only the primary key. The number is on the invoice the customer left at home. Ask which field the customer can produce, not which one the schema prefers.
• Normalising at query time. Every keystroke calls a function, the index goes unused, and two screens built by two people drift until the same name finds different rows.
• No scoping decision. Scoped to the branch, a record that exists becomes invisible; unscoped, one branch reads another's credit history. One of the two must be written down.
• Fuzzy matching with no way to tell twins apart. A phonetic key will eventually join two real people; show phone, town and last order so a human can separate them.
• The search box becomes a report. "All unpaid invoices" belongs in a filter. In the one box everybody shares, results grow and it loses the speed that made it useful.

What this does not cover

This is search inside a tool over records a team already keeps, not a site search box or a catalogue. It does not design permissions beyond the note that scoping is a deliberate decision, and it does not cover relevance ranking across long documents. The folding rules here cover Arabic, Latin and French spellings; other languages need a speaker to write them.

We build search into the tools we write, so a name and half a phone number are enough to find the record.