For the website redesign, the product showcase needs little explanation: a few photos, some text on the craft, a carousel. What really took the time was the 'Dealer Lookup' entry.
What the Client Handed Over Was One Excel
500+ stores in a single sheet: province, city, store name, address, phone, contact. Problems were visible on first open: the same province written several ways — 'Inner Mongolia,' 'Inner Mongolia Autonomous Region,' 'Inner Mong' all mixed together; the city column contained values like 'downtown' and 'flagship store' that aren't cities; 20+ phone numbers had extensions, written as strings like 010-12345678-802.
Import that as-is, and province filtering would fracture into three different 'Inner Mongolias.'
Cleaning Took Longer Than Building the Frontend
We did three things.
For provinces we built a dictionary table — 32 rows — mapping aliases to one canonical name. Imports were checked against the mapping; anything unmatched was not silently skipped but listed on a separate 'pending confirmation' page. That time 11 rows were left over, and the client verified them by phone.
Phones were split into two fields, phone and ext, joined back at display time, with dial links using only the phone part. In the original single-string format, the extension couldn't be dialed on mobile anyway.
The city column got its own non-empty and validity checks; values like 'flagship store' were moved into a store-type field.
How the 11 Pending Rows Were Resolved
For the 11 rows the province dictionary couldn't match, the client spent two days verifying by phone. Four were different spellings of the word 'province' — such as 'Inner Mongolia Province' — so we added aliases to the dictionary; three had dealers fill a city name into the province field, like 'Shenzhen'; the remaining four were empty — the client said those dealerships had since ended cooperation, so we marked them 'inactive' rather than deleting them.
The 'inactive' status was improvised at the time; it turned out to become the most used feature. Dealer lists churn every year: the frontend hides inactive ones, the admin keeps them, and exports put them on a separate sheet. The next year the client moved 20+ dealers into inactive status on their own, without ever calling us.
The Two Phone Fields
After splitting into phone and ext, one case slipped through: some stores left a mobile number with no extension, leaving ext empty. The frontend now checks for this — if ext is empty, no dash is shown, avoiding half-numbers like '138xxxx-'. The client's clerk caught it the day after launch with a screenshot; we fixed it as reported. It never made it into the requirements document.
One more small thing: on some Android phones, a tel link dials the extension too, and the client said someone had complained about failed calls. So dial links now take only phone, with the extension displayed separately for people to type themselves. That's as far as the system can go.
Looking at That Excel Again
We kept the original 2017 file, and in 2024, while building the dealer admin, we dug it out once more to compare fields. Of its 11 columns, 2 had never been used in all those years: contact gender and remark 2. We hadn't deleted them back then for fear of needing them — seven years on, still never used. The file name contains the word 'original,' and it still sits on the first level of the project folder, untouched by anyone. The inactive-status column was carried over verbatim into the 2024 admin, field name and all. The client's clerk says that status is the most used — more used than adding new records.
The Frontend Has Exactly Two Controls
A province dropdown and a keyword box. No cascading city selector, because the client said most people only remember a store name or a district name — direct search is faster. Search runs on MySQL LIKE plus a plain index on the city column; with 500+ rows even a full scan is fast enough, so no Elasticsearch.
The result list shows 20 per page, long addresses wrap, and phone numbers are tappable tel links. No map integration — the client said stores change too often, and a wrong map pin is worse than no pin.
The Admin Side
The admin keeps Excel batch import. Before importing, a validation pass lists the row numbers with duplicate store IDs, missing provinces, or malformed phones for people to fix. We also added de-duplication: store name + city + the first 6 characters of the address as a key. In the first three months the client imported 4 times on their own, without calling us.
On launch day — December 21 — we sat in the client's office that afternoon watching their clerk run a test import. She uploaded the file, the preview showed 3 rows of 'province unmatched,' and she fixed them herself. Good. That's how it should be.
