[eluser]jedd[/eluser]
It's a tough call, because I really hate de-normalising a database. Duplicating two fields, with the second field being programmatically determined from the first, means you probably should do it (only) whenever you need it. Ie, at data-retrieval time rather than at data input.
As I said, it depends how much you're going to mung the information on input. My suggestion was as you understood it - query the first (munged or 'local product code') column for an exact hit, if that fails, then use a more expensive query, trying to break down what the user has entered and being a bit fuzzier in your matching to the original product field. This lends itself to an environment where users may search around for product codes the first time, and then get used to whatever the internal (second field) product code is, and use that from then on. Obviously this also depends on just how bad your source data is, and whether it's easier to clean that up (it sounds like it isn't) rather than work around it like this.
If the problem really is as simple as 'abc-123' being in the DB and people entering 'abc 123' instead - then it's quite easy. You just search on those two strings in the same product id. But if they're typing 'abc123' and expecting to hit a field containing 'abc-123' then this becomes more complex. You
could start sub-stringing based on changes between alpha and numeric, for example - but then all it takes is an original product code like 'abc-123-456' and a user entering 'abc123456' - there's no sensible algorithm that gets you from what the user entered to the original product code.
Instead, you could start doing sub-string searches and returning possible options to the user for them to select amongst. This is tried and trusted technology, especially if you do some dynamic ajaxy stuff to show the user potential matches as they type.
There are also libraries - I've worked with a particularly expensive one on an Oracle system some years ago - that do the kind of searching you're looking at. Soundex for product codes, as it were. There may well be some free ones out there for PHP/MySQL by now.
