Zip Codes - good practices and patterns 1

[net123]

I wanted to know what is good practice in implementing zip codes in a .aspx web form and SQL Server 2k. For example, most zip codes that will be entered have 5 digits, but there are also those cases that have that 4 digit extension at the end. Should I create 2 fields in the database, such as ZipCode char(5) and then another column with ZipCodeExt char (4) and then have 2 text boxes on the web form respectively? Or should I just have 1 textbox on the web form named ZipCode to all 5 or 9 digit strings into one column on the backend db such as ZipCode varchar(9)? I think the former is better since we should always work at the most atomic level when working with databases.

I would like to know what is the best practice to implement this.

Thanks in advance.

 

[Isadore]

net -

I'd agree with you. I would create two fields, that way, it would seem to me, it would be easier to deal with related reporsts and queries in which you could check of a "NV" or NULL value, etc... Unless someone has a good reason for keeping the two fields together I would argue that the atomic level here is ideal.

 

[BoulderBum]

As far as validation goes, the RegularExpressionValidator control insures proper input, and has the ability to assign the appropriate RegEx through the browse button of the ValidationExpression property (it's "U.S. Zip Code" in the regular expression editor, or "\d{5}(-\d{4})?" if you'd like to just hard-code it).

Of course, "\d{5}(-\d{4})?" will match 0 or 1 occurances of ##### or #####-#### in its given state.

 

[Isadore]

Boulder --

That's a good point, that you can assure validation of an extended zip code.

My experience, admittingly limited, is that since there is a "mix" of 5 digit zips and 9 digit zips, it is easier to handle as separate fileds, concatenating in reports, etc... Seems in the past when I had a 9 digit zip in one filed, with mix values, it was problematic.

 

[abds]

IMHO, best practice is whatever is easiest for the user. If you are dealing with a professional mailing company that gets a substantial discount from the PO if it sorts down to the 9 digit field, then by all means keep it as 2 fields. If you are dealing with a small company that never uses anything but 1st class mail, leave it as a 10 byte alpha field and let them put the dash in.
Phil

__________________________________________________
We are drowning in data, but we are starving for information.

 

[Isadore]

abds - good point. Just weight out your company's requirements and needs, and go from there - really does depend on the circumstances one is facing. If its not a to do, and the number of user are small, sure, one field will do it.

 

[chiph]

Any need for Canadian postal codes? They have letters in them.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[blueark]

One small point I'd like to add (from personal experience) is that unless your web form is limited to US users, please, please, please don't make it a required field. Nothing is more frustrating than entering all your details only to have it rejected because it's not a valid zip code. It's even more frustrating when there is a drop down list to select your country on the same form!

 

[Isadore]

blue --

That brings up another point, a good one. Zip Codes control County, City, and State, i.e., if you choose a Zip Code, these fields can be automatically populated. This is the way I handle address changes on the net. The user is forced to select a "Zip Code" and has no editing capability over selecting their County, City and State. Just a thought.

We have found that if you allow the user to enter a City, or County, the invariably get them wrong, as well as the Zip Code, hence, the forcing of Zip Code, which assures the remaining fields are correct.

 

[blueark]

That is true. I certainly don't mean to suggest that Zip codes are pointless, but for most countries, they are meaningless. Some have post codes that follow a different format, others don't have any real system at all. My point is that if someone is asked to select what country they are from, you shouldn't expect them to fill out a Zip code if it's not an american address. Unfortunately, many sites out there assume this is the case, and lose a lot of international customers in the process!

 

[Isadore]

blueark -

All of these arguments are good arguments when it comes to Zip Codes; so good stuff; your points are well taken and I considered them valuable when I read your first thread -- that's what makes tek-tips a pretty neat place, people can just drop by and give their 2 cents. I'm sure net got his monies worth on this one.

 

[net123]

Thanks everyone who contributed to my thread. Fortunately, I am only working with U.S. addresses so it shouldn't be that difficult.

Good stuff!