What I’m trying to do is to generate a primary key in database. The easiest way is probably using auto increment on id to do that. But in this case the table is partitioned by id, auto increment won’t provide load balance on the table. As the unique key is zipcode (5 digits) + date (8 digits). it’s natural to generate an primary key based on that. You may think it’s easy to just add date behind zipcode as id something like this 9000120130928.
But the problem is due to certain condition, I can only use Int type instead of BigInt. So simple add up won’t fit in an Int type of which the max id is 2^32 = 4.2B, where as my id can easily go up to 9000B as shows above.
Theoretically, we have 43000 zipcodes in U.S., and we store history data from 1830 to present. So there will be in total 43000 * 365 * 200 = 3B records which should be able to fit in a regular Int type. The key is to find a function that map all of them to this int type. So it comes as find f(x) fit to use.