Preferred method of storing zipcodes 1

[jimoo]

Silly question...

I am trying to find the best way (best practice) for storing zip codes including the zip4.

I know they are stored in a variety of ways such as:

1. Two separate fields (zip and zip4)

2. A field that contains zip + a dash + zip4 (10 characters counting the dash)

3. A field that contains zip + zip4 (9 characters excluding the dash.

4. Other??

What is the general conscious or best industry practice.

Jim

 

[markros]

I think 2 fields would be the best storage. We used to have 2 fields in our database.



PluralSight Learning Library

 

[RiverGuy]

I think I might go with #2 -- a column that could handle a postal code from any of your contacts. For example, the Canadian postal code has a different format than the U.S. code.

 

[djj55]

Two fields buys you the advantage that if there is no zip4 value it does not affecting the overall length. That being said do not forget that when you concatenate a null, the result is null.

I think it comes down to what you are most comfortable with.
Make sure the front end checks for that leading zero.

Just do not do what someone did here and make zip a float (Microsoft helping out from Access).


djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

How many rows do you have in your table? If you have less than a couple million, it probably doesn't really matter unless you have queries that filter on the +4 part without the first 5 characters of a zipcode.

Just do not do what someone did here and make zip a float (Microsoft helping out from Access).

Guilty. I used to store them as integers which is equally bad. You really need to store them as characters because (as RiverGuy mentioned), not all countries have pure numerical postal codes.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jimoo]

Thanks everyone - quick responses.

Interest... It is kind all over, like the databases I have worked with in the past. I was hoping for a best practice, but the truth be there might not be one and it might just depend on the data.

The systems I am working on has just over 500,000 contacts and it contains foreign addresses. The zipcodes are stored in providence code.

I'll think these solutions through, but am leaning on 2 fields - zip and zip4 and not storing a dash.








Jim

 

[gmmastros]

With only 1/2 a million rows, I would suggest that you keep it all in one column.

Think of it this way... the difference between a varchar(5) and a varchar(10) is exactly 5 bytes per row. For your 1/2 million rows, that's just 2.5 megs of storage. I recently bought a terrabyte drive for less than $100. So... the cost of storage is non-existent. The performance difference would be non-existent. In fact, I see no compelling reason to use 2 columns. I also see no reason to add and/or remove the dash. Personally, I would store the data exactly as the user entered it.

The ONLY reason I would consider making 2 columns would be IF you query on the zip4 data without also querying on the zip data. By separating the columns, you can create additional indexes. Even still, if this was your compelling reason, you could create a computed column and index that.

Unless someone has a more compelling reason other than storage size, I recommend you leave it in one column.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

I'll think these solutions through, but am leaning on 2 fields - zip and zip4 and not storing a dash.

One reason I do not like this approach is that if you are to use data from the table to display a fully formatted address on a screen or printed paper, you need to have special logic to say "if this is a U.S. address, add a dash between the first column and the second column. Furthermore, if it is a U.S. address and the second column is null, then don't add the dash."

It would be much simpler to just display the value of the column instead of using conditional logic.