Whats the best way to store and retrive postal addresses using a sql server database and the .NET framework?

Question

I'm looking for a common pattern that will store and access global addresses in database. Components or other technologies can be used. The following criteria must be adheard to...

• Every line of the address is saved for every country
• Postal codes are tested with a regular expression before being saved
• Country of original is saved in it's own field When the data is displayed, the [address is formatted] (http://en.wikipedia.org/wiki/Postal_address) in the style of that country
• When the data is input using a form the label fields are as descriptive as possible, so the labels ned to be dynamic to the country of origin.
• The addresses take up the minimum space possible

Answer 1

How about storing the addresses as text (allowing newlines). The postal code will have to be extracted from the address with a regex (selected based on a country dropdown), and should be stored in a separate column.

This doesn't deal with the "as descriptive as possible" requirement, but in general, enforcing more constraints about the format of the data will result in a percentage of valid addresses being rejected. It will also take more space than a single varchar column. Therefore, there will always be a compromise between the requirements you listed.

Answer 2

I will refer you to my blog post - A lesson in address storage for reasons why you shouldn't store addresses in the format currently accepted and should be normalized properly! Don't be lazy with address storage, it will cause you nothing but headaches in the future!

Also, there is another StackOverflow question that asks this question. Entitled How should international geographic addresses be stored in a relational database.