ZipCode to ZipCode Storage I'm working on a project that requires me to allow users to store/edit properties of what they call "path". A path can be any Zip Code to any other Zip Code.

The system they have now, utilizes one table for 3 digit to 3 digit system for default properties, and then if exceptions to the 3to3 are needed, there's another table that does the 5 digit to 5 digit.

123 &gt; 345
12345 &gt; 34567


Making changes is interesting for them, because it becomes a game to figure out exceptions (5 to 5) in relation to the 3 to 3 properties. Add in the fact that going from zip 12345 to zip 98765 isn't the same as doing the reverse 98765 to 12345. 

So, they'd rather have one table that has the immediate detail of "every 5 digit zip code to every 5 digit zip code". By my calculations that means close to 2 billion rows (43000 x 43000. since there's about that many valid zips in the US).

That just seems like a bad idea to me, but without going right back to the situation they already have, I can't see a way around it.

I thought about storing them as one single combination, but then having the extra fields for either direction. Like this:

Zip1: 12345
Zip2: 98765
1to2 Property 1: 
2to1 Property 1:
1to2 Property 2:
2to1 Property 2:
1to2 Property 3:
2to1 Property 3:


This would reduce the number of rows to under 1 billion. I would just setup the Zip1 to Zip2 as the primary key, and then have a unique index for the reverse Zip2 to Zip1.

Has anyone ever seen a neat trick or idea to storing data like this?
Partial answer:  Given that you need to look up ($z1, $z2):

WHERE zip1 = LEAST($z1, $z2)
  AND zip2 = GREATEST($z1, $z2)


For prep, you have only the PRIMARY KEY(zip1, zip2) and you have filled the table only with smaller zip to larger zip.  And adjusted the 1to2, etc, columns accordingly.