Is there common street addresses database design for all addresses of the world?

It is possible to represent addresses from lots of different countries in a standard set of fields. The basic idea of a named access route (thoroughfare) which the named or numbered buildings are located on is fairly standard, except in China sometimes. Other near universal concepts include: naming the settlement (city/town/village), which can be generically referred to as a locality; naming the region and assigning an alphanumeric postcode.

Note that postcodes, also known as zip codes, are purely numeric only in some countries. You will need lots of fields if you really want to be generic.

It is possible to represent addresses from lots of different countries in a standard set of fields. The basic idea of a named access route (thoroughfare) which the named or numbered buildings are located on is fairly standard, except in China sometimes. Other near universal concepts include: naming the settlement (city/town/village), which can be generically referred to as a locality; naming the region and assigning an alphanumeric postcode.

Note that postcodes, also known as zip codes, are purely numeric only in some countries. You will need lots of fields if you really want to be generic. The UPU Universal Postal Union provides address data for lots of countries in a standard format.

See upu.int/post_code/en/universal_postcode_... upu.int/en/resources/postcodes/about-pos.... Note that the UPU format holds all addresses (down to the available field precision) for a whole country, it is therefore relational. If storing customer addresses, where only a small fraction of all possible addresses will be stored, its better to use a single table (or flat format) containing all fields and one address per row.

A reasonable format for storing addresses would be as follows: Address Lines 1-4 Locality Region Postcode (or zipcode) Country Address lines 1-4 can hold components such as: Building Sub-Building Premise number (house number) Premise Range Thoroughfare Sub-Thoroughfare Double-Dependent Locality Sub-Locality Frequently only 3 address lines are used, but this is often insufficient. It is of course possible to require more lines to represent all addresses in the official format, but commas can always be used as line separators, meaning the information can still be captured. Usually analysis of the data would be performed by locality, region, postcode and country and these elements are fairly easy for users to understand when entering data.

This is why these elements should be stored as separate fields. However, don't force users to supply postcode or region, they may not be used locally. Locality can be unclear, particularly the distinction between map locality and postal-locality.

The postal locality is the one deemed by a postal authority which may sometimes be a nearby large town. However, the postcode will usually resolve any problems or discrepancies there, to allow correct delivery even if the official post-locality is not used.

(Yeah, I know I could find it - but the best answers don't make people do the search. ) – Jonathan Leffler May 31 '09 at 2:05 Try upu.Int/post_code/en/… and choose the appropriate country in the drop-down – barrowc May 31 '09 at 3:30 Added URL for UPU Post*Code product – Edward Ross Jun 2 '09 at 9:48 3 Also, some countries (Republic Of Ireland for example) do not use Zip codes. If I had a cent for the number of times I've had to entere na (not applicable) as a zip code because it's a required field man .

.. I'd have five or six cent by now :) – Binary Worrier Jun 2 '09 at 10:08.

Have a look at Database Answers. Specifically, this covers many cases: AddressId Line1 Line2 Line3 City ZipOrPostcode StateProvinceCounty CountryId OtherAddressDetails.

Good link Mitch, thanks – ArsenMkrt May 30 '09 at 12:52 would the downvoter please leave a comment. Thanks. – Mitch Wheat May 30 '09 at 13:47 I didn't downvote, but I think the only way this could work was if all fields but AddressId and Line1 were optional.In which case, it is not too useful.

– anon May 30 '09 at 22:50 3 Data types are important--not every country has integer ZIP codes! Had a coworker find this out quick with a customer in Canada. – Eric May 30 '09 at 22:53 1 For country ID, you should use the ISO 3166 2-letter (or 3-letter) country code.

The proposed schema allows you to store an analyzed address; it does not tell you about how to format it. (Oh, and the UK has alphanumeric post codes - IP31 3GH, SE1W 9PQ, etc.I think the second group is always NAA; the first group starts with A and contains at least one N (A = alpha, N = digit), but nothing would surprise me. ) – Jonathan Leffler May 30 '097 at 2:09.

Depending on your actual need, you will determine either a) it doesn't really matter, and you can go for a free-text approach, or b) structured/specific fields for all countries, or c) country specific architecture.

Excellent advice: model for your purpose, +1. – Alix Axel Oct 10 at 15:22.

For international addresses, it is remarkably hard to find a way to format the information if it is broken down into fields. As a for instance, an Italian address uses: Such as Via Eroi della Repubblica 89861 Tropea VV Italy That is rather different from the order for US addresses - on the second line. See also the SO questions: How many address fields would you use for a UK database?

Do you break up addresses into street / city / state / zip? How do you deal with duplicate street suffixes? Best practices for storing postal addresses in a database (RDBMS)?

Also check out tag 'postal-code'. Edit: Reverse order of region and town - per UPU.

Sometimes the closest you can get to a street address is the city. I once had a project to put all the Secondary Schools in India in Google Maps. I wrote a spiffy program using the Google API and thought it would be quite easy.

Then I got the data from the client. Some school addresses were things like "Across from the market, next to the barber" or "Near old bus stand". It made my task much harder since, unfortunately, the Google API does not support that format.

Depends on how free-form you are prepared to go with the fields. One free-form address field will obviously always do, but be of relatively little help narrowing down geography. The problem you'll have is that there is too much variation in the level of geographical hierarchy across countries.

Heck, some countries do not even have 'street addresses' everywhere. I recommend you don't try to make it too clever.

Differently of other answers here, I believe it's possible to have an structured address database. Just out of the hat, I can think of the following structure: Country Region (State / Province) Locality (City / Municipality) Sub-Locality (County / other sub-division of a locality) Street But how to query it fast enough? One way I always think it can be accomplished is to ask for the ZIP Code (or Postal Code) which varies from country to country, but is solid within the country.

This way you can structure your data around the information provided by the postal offices around the world.

Len Silverston of Universal Data Model fame recommends a separate hierarchy of GEOGRAPHIC BOUNDARIES and depending on how much free-formed-ness you're willing to accept either simple STREET ADDRESS LINEs or per-country derivatives.

The model is crazy complex though... – Alix Axel Oct 10 at 13:11 The reality is crazy complex too... – David Schmitt Oct 13 at 7:28 1 True, and the models Silverston came up with are pretty good and cover a lot of ground but I still don't think such complexity is applicate to the web (at this point), specially from the end-user perspective. In the end, usuability (almost) always wins. – Alix Axel Oct 13 at 9:07.

Maybe this is useful: https://gist.github. Com/259744 For a project I collected a table of informations about all countries of the world, including ISO codes, top level domain, phone code, car sign, length and regex of zip. Country names and comments unfortunately only in German...

Thanks @Hraban great link – ArsenMkrt Dec 16 '10 at 7:20.

No, absolutely not. If you compare the way US and Japanese addresses work, you'll see that it's not possible. UPDATE: On second thought, anything can be done, but there's a trade-off.

One approach is to model the problem with address and address_attribute tables, with a 1:m relationship between them, anything can be modeled. The address_attribute table would have a pk, a name, a value, and an fk that points back to its address parent's pk. It's almost like using a Map with name, value pairs.

The trade-off is having to do a JOIN every time you want an address. You also have to interrogate the names of the address_attributes to figure out what you're dealing with each time. Another approach would be to do more comprehensive research on how addresses are modeled around the world.In an object-oriented world you might have the western Address class (street1/street2/city/state/zip) and others for Japan, China, as many as needed to tile the address space.

Then you'd have a master Address table and child tables to the other types with a 1:1 relationship between them. How does Amazon or eBay do it? They ship internationally.

Do they have locale-specific UI features? I've only used the US locale.

– ArsenMkrt May 30 '09 at 12:56 Sorry, I'm not following you here. – duffymo May 30 '09 at 14:27.

Your design should strongly depend from your purpose. Some people have posted how to structure data. So if you simply want to send s-mail to someone, it will do.

Things begin to complicate if you want to use this data for navigation. Car navigation will require additional structures to contain traffic info (eg one-way roads), while foot navigation will require a lot of additional data. Here is small example: in my city, my neighborhood is near the park.

Next to the park is former airfield (in fact, one of the oldest in Europe) turned into aviation museum. Next to aviation museum is a business park. Street number for museum is 39, while business park numbers start with 39A.So it may seem that 39 and 39A are close – but it takes about a mile to walk from one to another (and even longer if going by car) .

This is just a small example taken from my city, I think you can probably find a lot of exceptions (especially in rural or wilder parts of every country).

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.