CODEWITHSUNDEEP
mysqldatabase-design
Zach
Zach

Reputation: 1185

Building Database Table for Zip Codes

I'm attempting to build a database table that holds zip code data (that is then later referenced in my application).

I am grabbing the .csv file from http://www.populardata.com/zipcode_database.html which has the following data:

What I've attempted so far is:

CREATE TABLE `finder_zip_codes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `zip` mediumint(128) DEFAULT '0',
  `latitude` varchar(128) DEFAULT NULL,
  `longitude` varchar(128) DEFAULT NULL,
  `city` varchar(128) DEFAULT NULL,
  `state` varchar(128) DEFAULT NULL,
  `county` varchar(128) DEFAULT NULL,
  `zip_class` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

But since I'm newer to MySQL, best-practices, etc. have snuck up on me. For example, the zip table seems to strip the beginning of the zip codes. I looked into ZEROFILL but I don't think that' what I want here, I basically want to use zip as an int field, but have it not auto-correct the data inside of it (or truncate it).

Any help would be greatly appreciated - I've been trying a few doc sites and this is what I've come up with so far - so any additional tips would be awesome. Thanks!

Upvotes: 1

Views: 4737

Answers (4)

user2810910
user2810910

Reputation: 287

It appears you are limiting zip codes of the united states. You may want to consider foreign zip codes too. In that case, you will need to restructure your database schema to include the country. This will require more than one table (normalization) with foreign key associations between the tables. Example: country_id and state_id.

Also, the zip code format appears different between countries. ref: http://en.wikipedia.org/wiki/List_of_postal_codes.

I suggest you provide a more meaningful name for the table name instead of finder_zip_codes. We usually don't use the plural form for table name (drop the trailing 's'). Also, the table name should be a noun, 'finder' implies a verb. A better name would be usa_zip_code.

Also, the primary key is often the name of the table followed by 'id'. Example: usa_zip_code_id. This way, when you have usa_zip_code_id in another table as a foreign key, you immediately know what table it refers to. Lastly, consider which columns should be not null, and/or unique.

Upvotes: 0

user1473461
user1473461

Reputation: 369

We get this question a lot, since we sell ZIP Code data. Microsoft products by default strip out the leading zeroes and Puerto Rico, New England and parts of New Jersey ZIP Codes have one or more leading zeroes. The best way that I've seen with SQL is to use: RIGHT(100000 + zip, 5).

This also works for Mexican Postal Codes and any integer that you want the leading zeroes. You just change the number of zeroes and the integer expression after the comma. For instance, if you needed a 6-digit check number field to have leading zeroes you would have: RIGHT(1000000 + check_num, 6)

Upvotes: 0

RB.
RB.

Reputation: 37232

You probably don't want to store a ZIP code as an integer field, because it's not really an integer. It looks like one, but it wouldn't make sense to perform integer operations on it (e.g. You would never do 12034 + 32523 - it would be nonsensical).

It would be better to store this as a char(5) field, because really that's what it is - a 5 character string, that happens to be constrained to only contain digits.

EDIT

Latitude and Longitude can be stored as a numeric datatype (probably a decimal or double) as they actually are numbers :)

Upvotes: 3

Akber Choudhry
Akber Choudhry

Reputation: 1785

Integers are stored the same way - as numbers. Most probably, it is not 'stripping' - the interfaces you are using to look into the table is removing leading zeros to make it human-friendly.

Similarly, you should also pad with zeros when you produce it on a screen or a report.

Upvotes: 1

Related Questions