Crazy busy….

I know I’ve been running at a flat 100 (as an old friend “Shorty” Lane used to say) for several weeks now.

The increased pace and pressure has showed up in some of my work and it’s really beginning to upset me. Normally, being the perfectionist sort I am, I check, recheck and test everything several times before I hand it off.

Lately, I’ve felt like time was such an issue that I’ve slipped a bit in the testing and checking phases.

The case in point is, postal codes, and Mexican state abbreviations in particular. Who knew there were several ‘standards’ in use? I sure didn’t.

It turns out Mexico has 2, 3 and 4 character codes for their 31 states and 1 district. Compound that with the fact that most postal code suppliers attempt to translate between the languages and often the translations don’t agree.

So today, I’m building a little conversion utility to convert the known spellings of those 32 Mexican States to a unique 2 character code (so it fits the exiisting table structures), which can then be used to span the various sources of data.

While I’m talking about postal codes, did you know that postal codes expire? Expire as in they exist as a viable mailing location one month, and the next, poof, they’re nowhere to be found in the database!!

Yeah I didn’t know that either. One of the things my (now increased) testing revealed is that Canada, the US and Mexico as well are adjusting postal codes, and doing so on a fairly regular basis.

If your company has a fairly local focus, only does business in the USA, or has fairly tight controls on its customer/marketing database, it’s probably not all that big a problem.

When you extend the business reach to all three countries and provide centralized location data for shipments and deliveries, for an entire industry, well the expiration of these codes can pose a problem.

In some cases, say in a small town with only one postal code for the town, simply finding the town name in the database, and identifying the new code is fairly simple. If you take a city like Los Angeles, or New York however, there are dozens of possibilities, possibly hundreds of customers affected, and no simple means to determine the new postal code.

You’d think, with all the technology we have, and that these ‘postal data’ companies exist for only one reason, and that’s to provide postal data, they would have a cross referencing system in place and that it would be a part of the service.

For you programmers and developers out there… did you know that postal (zip) codes went away, at the rate of 300 to 400 a month between these three countries?

No one I’ve talked to was aware… Makes you wonder about the wisdom of storing an actual postal code in our datastores, doesn’t it? I’ve seen attempts to externalize geographical location data, to place a ‘company issued geo-code’ in the database and allow that to reference records in the actual postal table.

Every implementation instance of this I’ve worked with however, still had problems of its own and, after a period of time, became out of touch with the official postal table.

Interesting challenge this. It’s a fairly easy task to identify the entries in the various tables that are effected by a removed postal code, gather them up in some exchangeable format like say Excel, and hand them off for manual adjudication. The real trick though is finding some repeatable process that can then be automated and integrated into the monthly update procedures.

So… my question is, for my fellow coders, designers, developers and problem solvers… Have you run into this situation? Have you seen any sort of automated process that’s working, and has worked well over say a 12 month time span? I’d love to hear any experiences, good, bad or in between!

Technorati Tags: Zipcode - Postal - Data Design - Busy
-IceRocket Tags: Zipcode - Postal - Data Design - Busy