Sunday, February 12, 2006

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: - - -
-IceRocket Tags: - - -

8 comments:

Beth said...

Just stopping by to say "hi" as I am not a coder or programmer. LOL

Bill said...

Beth - Regardless, I'm always gald to see you stop by!! (A 'geeks only' kinda place is just not my style!!)

Flash said...

I know how it is with the perfectionist curse. I'm the same way. And I always find myself tweaking, frame by frame, my films, only to find out that I've spent 26 hours working on a section of the film that basically accounts for 2 1/2 seconds. Fine if I'm working on my own time...byt man, did that hurt me in school.

Patrick M. Tracy said...

Bill,

Who knew that postal codes were so short-lived? I certainly didn't. Makes me glad that I don't have to run an international business.

Lorna said...

I've had the same postal code for 13 years; my mother has had hers for more than 30---that doesn't seem too iffy. On the other hand, I'm neither a perfectionist or a programmer

Bill said...

Flash - You truly are a perfectionist brother!! I console myself, by telling myself, it's *all* in the details... and for us it is I think!! It is definitely a two edged sword!!

Firehawk - I don't know if they're all short lived, my home town has had ther same Zip since they started using zip codes... but the fact is the do 'expire'.. the question is, how do we deal with that!

Lorna - Yep, my Mom too.. and here where I live for over ten years.. which is one of the reasons the changes were such a surprise!!

Unknown said...

Bill,

Wow, that's a good one. I'm embarrassed to say that I didn't know how frequently these changes occurred.

My first thought is a Web service. One example might be Microsoft's Map Point product. You might just Google "postal code" "web service" and see what you get. Good luck!

Bill said...

CA - I'm all over that, web services are the ticket in many cases. Unfortunately, the 'MainFrame' folks don't know web services from an Intel chip!!

The mid-tier, Sun and Java folks are hip to that though.

We've got a ton of legacy mainframe stuff in this shop, probably 80% of what is in use is in COBOL, believe it or not. Most of the mid-tier is running on Java, and Sun servers, and that seems to be the direction where possible.

Having to be the hub for an industry seems to force a considerable amount of 'standardization' to facillitate intercompany communications. Most all communications are still EDI, XML streams look promising for 2008 though!!

Thanks for the well wishes.. I think for the moment we've resolved the Mexican states issue, and for the near term I'll just hand out (as in email) the spreadsheets of effected customers and locations monthly.

Always good to see ya!