Store zipcode+date in an integer type in mysql

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.

Advertisements
Store zipcode+date in an integer type in mysql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s