Addresses - How To Store?

[Goksly]

The company I work for currently has a large address database:
Name, Address 1, Address 2, Address 3, Post Code

We're increasingly doing more international work, so I want to use this as an opportunity to rework it (if required) rather than just putting in a "Country" column. There seems to be so many different formats / standards out there.

Royal mail:
Organisation Name
Department Name
Sub Building Name
Building Name
Building Number
Dependent Thoroughfare Name
Dependent Thoroughfare Descriptor
Thoroughfare Name
Thoroughfare Descriptor
Double Dependent Locality
Dependent Locality
Post Town
Postcode
PO Box

Google:
street_address
route
intersection
political
country
administrative_area_level_1
administrative_area_level_2
administrative_area_level_3
administrative_area_level_4
administrative_area_level_5
colloquial_area
locality
ward
sublocality
neighborhood
premise
subpremise
postal_code
natural_feature
airport
park
point_of_interest
floor
establishment
point_of_interest
parking
post_box
postal_town
room
street_number

So, yeah has anyone had any experience at trying to create a usable address database, with fields that will keep various data providers happy and is suitable for holding addresses in the vast majority of countries world wide?

 

[AHarvey]

I'd probably be looking at a table per country, have it laid out in the format of addresses from that country + include the way the courier would request the data.

Sounds complicated but I think it would be better than a single address table with dozens of columns trying to cover everything in each one. I would guess it would also cut down on the search time when looking for addresses as you'd only query a single table with the correct info in.

 

[Goksly]

I won't be able to do that - there will be a minimum of 100 countries we're going into. Also, most of our projects will span across multiple countries (I suppose the title was a bit misleading - the database is part of a complex application), so retrieving that data would be a nightmare over so many tables.
I don't necessarily want to be 100% compatible with each countries way of doing thing, but being able to "work" with their data. If that makes sense!

 

[planty]

I've only used the royal mail format previously so can't help much. However Google already handles international addresses so wouldn't it be easier for you to use their format for everything as they must surely have already solved most of the problems you are going to encounter?

 

[Nazbit]

We started with royal mail format and added in Europe and US later. We kept RM attributes and added country and it's been fine. (40 million records).

The one tip that I would give is be sure to protect against control characters and unrecognised characters keyed by the user in foreign addresses. They're a real pain.

Where is your validation done is it front end or back end? If it's back end then separate tables for each country would probably be the way to go - I know you've said that doesn't look like an option.

Some discussion here: https://stackoverflow.com/questions/4840928/iso-standard-street-addresses