views:

681

answers:

9

From first glance, it would appear I have two basic choices for storing ZIP codes in a database table:

  1. Text (probably most common), i.e. char(5) or varchar(9) to support +4 extension
  2. Numeric, i.e. 32-bit integer

Both would satisfy the requirements of the data, if we assume that there are no international concerns. In the past we've generally just gone the text route, but I was wondering if anyone does the opposite? Just from brief comparison it looks like the integer method has two clear advantages:

  • It is, by means of its nature, automatically limited to numerics only (whereas without validation the text style could store letters and such which are not, to my knowledge, ever valid in a ZIP code). This doesn't mean we could/would/should forgo validating user input as normal, though!
  • It takes less space, being 4 bytes (which should be plenty even for 9-digit ZIP codes) instead of 5 or 9 bytes.

Also, it seems like it wouldn't hurt display output much. It is trivial to slap a ToString() on a numeric value, use simple string manipulation to insert a hyphen or space or whatever for the +4 extension, and use string formatting to restore leading zeroes.

Is there anything that would discourage using int as a datatype for US-only ZIP codes?

+11  A: 

Are you going to ever store non-US postal codes? Canada is 6 characters with some letters. I usually just use a 10 character field. Disk space is cheap, having to rework your data model is not.

Tom
Not that Canada's the only other place n the world, just using it as an example.
Tom
The UK also uses alpha numerical post (zip) codes
ChrisF
even if you only need US ZIP codes now, as soon as marketing/sales at your company realizes they can make money elsewhere, you'll need to support other ones :) It takes no extra effort to support it now, but will take a lot later.
rmeador
Plus the leading-zero issue means you're not storing the customer's real data, you're storing some adjusted version of it that will require mental gymnastics to remember you need to always look out for too-short codes. And what if they included the +4?There's no advantage to storing them in numeric format that I can see. The only operation I've ever done on zip codes is to use them in long/lat calculations and that requires you to treat them like strings anyway: you just grab the first 3 characters (leading zero problem again).
Tom
+9  A: 

Use a string with validation. Zip codes can begin with 0, so numeric is not a suitable type. Also, this applies neatly to international postal codes (e.g. UK, which is up to 8 characters). In the unlikely case that postal codes are a bottleneck, you could limit it to 10 characters, but check out your target formats first.

Here are validation regexes for UK, US and Canada.


Yes, you can pad to get the leading zeroes back. However, you're theoretically throwing away information that might help in case of errors. If someone finds 1235 in the database, is that originally 01235, or has another digit been missed?

Best practice says you should say what you mean. A zip code is a code, not a number. Are you going to add/subtract/multiply/divide zip codes? And from a practical perspective, it's far more important that you're excluding extended zips.

Mark
It's cool --- we've got and used regexes in the past for validationg. I'd have to anyway, as users still would input via textbox string, whether it would be parsed and stored numerically or not, I'd still have to validate the textbox before submission.
Yadyn
I work a *lot* with addresses, in particular address cleansing. Your point regarding removal of leading zeros is far more important in real terms than your the point regarding the semantics of whether or not it's a number. When it comes to data cleansing, needing to know if the data was entered incorrectly or whether it is missing leading zeros is far more time consuming than you might first imagine.
BenAlabaster
A: 

Integer is nice, but it only works in the US, which is why most people don't do it. Usually I just use a varchar(20) or so. Probably overkill for any locale.

Eric Petroelje
If I am not mistaken, zip codes in the US can start with a 0, so that may make the integer out of the running.
TheTXI
@TheTXI - true, but if you are assuming US only anyways, then you can always pad to 5 digits with leading zeros for display purposes.
Eric Petroelje
Maybe, although that loses redundancy and possible error-checking. Are there ZIP codes that start with four zeros (in Maine or somewhere)? If so, how do you tell the difference between, maybe, a Bangor ZIP+4 and a ZIP from somewhere that isn't New England?
David Thornley
I believe the smallest US zip code has two leading zeros - 00210, Portsmouth, NH. So, even with zip + 4, you can still know that a zip must be either 5 digits or 9 digits. If it were 7 or 8 digits you would know it was a zip + 4 with one or two leading zeros. So, it would of course be a PITA, but it's possible to do without ambiguity if you really wanted to.
Eric Petroelje
+9  A: 

Normally you would use a non-numerical datatype such as a varchar which would allow for more zip code types. If you are dead set on only allowing 5 digit [XXXXX] or 9 digit [XXXXX-XXXX] zip codes, you could then use a char(5) or char(10), but I would not recommend it. Varchar is the safest and most sane choice.

Edit: It should also be noted that if you don't plan on doing numerical calculations on the field, you should not use a numerical data type. ZIP Code is a not a number in the sense that you add or subtract against it. It is just a string that happens to be made up typically of numbers, so you should refrain from using numerical data types for it.

TheTXI
+23  A: 

A numeric ZIP code is -- in a small way -- misleading.

Numbers should mean something numeric. ZIP codes don't add or subtract or participate in any numeric operations. 12309 - 12345 does not compute the distance from downtown Schenectady to my neighborhood.

Granted, for ZIP codes, no one is confused. However, for other number-like fields, it can be confusing.

Since ZIP codes aren't numbers -- they just happen to be coded with a restricted alphabet -- I suggest avoiding a numeric field. The 1-byte saving isn't worth much. And I think that that meaning is more important than the byte.


Edit.

"As for leading zeroes..." is my point. Numbers don't have leading zeros. The presence of meaningful leading zeros on ZIP codes is yet another proof that they're not numeric.

S.Lott
Unfortunately (is it unfortunate?) you make a really good semantic point. ;)
Yadyn
@Yadyn: I don't think it is due to fortune that he made a really good point! :)
Bill Karwin
+4  A: 

From a technical standpoint, some points raised here are fairly trivial. I work with address data cleansing on a daily basis - in particular cleansing address data from all over the world. It's not a trivial task by any stretch of the imagination. When it comes to zip codes, you could store them as an integer although it may not be "semantically" correct. The fact is, the data is of a numeric form whether or not, strictly speaking it is considered numeric in value.

However, the very real drawback of storing them as numeric types is that you'll lose the ability to easily see if the data was entered incorrectly (i.e. has missing values) or if the system removed leading zeros leading to costly operations to validate potentially invalid zip codes that were otherwise correct.

It's also very hard to force the user to input correct data if one of the repercussions is a delay of business. Users often don't have the patience to enter correct data if it's not immediately obvious. Using a regex is one way of guaranteeing correct data, however if the user enters a value that doesn't conform and they're displayed an error, they may just omit this value altogether or enter something that conforms but is otherwise incorrect. One example [using Canadian postal codes] is that you often see A0A 0A0 entered which isn't valid but conforms to the regex for Canadian postal codes. More often than not, this is entered by users who are forced to provide a postal code, but they either don't know what it is or don't have all of it correct.

One suggestion is to validate the whole of the entry as a unit validating that the zip code is correct when compared with the rest of the address. If it is incorrect, then offering alternate valid zip codes for the address will make it easier for them to input valid data. Likewise, if the zip code is correct for the street address, but the street number falls outside the domain of that zip code, then offer alternate street numbers for that zip code/street combination.

BenAlabaster
Yes, I can't count the number of times I've put SW1A 1AA because of excessive validation. If you can subscribe to an address database, you'll enormously improve the quality of your data. You'll also be able to identify gaps in the database (and so continue improving it).
Mark
We subscribe to the Canada Post database for ours and the basic database is only the beginning. It takes quite a bit of fuzzy logic and some creative algorithms to make it really useful.
BenAlabaster
+1  A: 

Hi,

Unless you have a business requirement to perform mathematical calculations on ZIP code data, there's no point in using an INT. You're over engineering.

Hope this helps,

Bill

Bill Mueller
A: 

If you were to use an integer for US Zips, you would want to multiply the leading part by 10,000 and add the +4. The encoding in the database has nothing to do with input validation. You can always require the input to be valid or not, but the storage is matter of how much you think your requirements or the USPS will change. (Hint: your requirements will change.)

Steve
A: 

ZIP code is really a coded namespace, if you think about it. Traditionally digits, but also a hyphen and capital letters:

"10022-SHOE"

http://www.saksfifthavenue.com/main/10022-shoe.jsp

Realistically, a lot of business applications will not need to support this edge case, even if it is valid.

benc