Geeks With Blogs
BizTalk Blog by Chris Han System Design for Enterprise Agility,

My friend told me this morning, he's little confused with his UPS shipment tracking result.

We all know that UPS' using 18 bits tracking number to 'uniquely' identify a shipment. But after I just saw my friend's tracking result page, I'm not sure about the 'unique' part any more.

On the 'Package Progress' pane, UPS lists all the history of the shipment associated with the number:

Package Progress
Location Date Local Time Description
06/25/2007 9:47 A.M. DELIVERY
  06/25/2007 7:19 A.M. OUT FOR DELIVERY
06/22/2007 11:38 P.M. ARRIVAL SCAN
06/22/2007 10:29 P.M. DEPARTURE SCAN
  06/22/2007 8:11 P.M. ORIGIN SCAN
11/06/2006 4:53 P.M. DELIVERY

See the two high-lighted lines? Apparently, the tracking number is reused by UPS! I don't what's the policy for reusing a tracking number at UPS. But the common sense would be unless you have used all other possible numbers, right? That means all 18 bits of numbers have been used in roughly half year (11/06/2006~06/25/2007)! That's 10^17*26= 2,600,000,000,000,000,000 shipments! Man, 2.6 trillion, what a business!

I guess it's a good news for sales department at UPS but headache for DBAs. Most our SQL server database are still using 'int' type as the unique id, which only supports 2^31-1 (2,147,483,647), even 'bigint' type which supporting up to 2^63-1 (9,223,372,036,854,775,807) - about 3.5 times of what UPS has used out in 6 months. It's about 1.7 yeas of OLTP data. After that, you have to move the data to a data warehouse! DBAs or database designer probably never think of such a big number in such a short time. Similar problem as Y2K, but this comes much quicker than any of us can ever imagine. Even Mr. Moore needs to adjust his law.

What a great time for all geeks like you who’s reading my blog. But be prepared! Love yo’ll.

Posted on Monday, June 25, 2007 11:51 AM SQL Server | Back to top

Comments on this post: Is UPS tracking system creating another Y2K problem?

# re: Is UPS tracking system creating another Y2K problem?
Requesting Gravatar...
Thanks, Jason. Good to know.
Left by chris on Jun 28, 2007 9:33 AM

# re: Is UPS tracking system creating another Y2K problem?
Requesting Gravatar...
The tracking number is comprised of several pieces of information which include the senders account number and type of service for the oackage. The last digit is a check-digit. Only six or seven digits (i don't recall exactly) are a counter and they will eventually roll-over for each sender account. How often they roll depends on the senders volume of packages.
Left by Alan Houston on Dec 31, 2007 1:29 PM

# re: Is UPS tracking system creating another Y2K problem?
Requesting Gravatar...
I am doing a research paper on UPS and I was wondering if any of you might happen to know anything else about the UPS tracking system??
Left by Rebecca on Feb 29, 2008 1:59 PM

Your comment:
 (will show your gravatar)

Copyright © Chris Han | Powered by: