Menu
A postal address database is a contact database that contains street address information about a client, customer, or other contact. A postal address database helps companies organize and format their contact information.
String data types are normally used to store names, addresses, descriptions or any value that contains letters and numbers including binary data, like image or audio files.
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 Universal Postal Union (UPU) provides address data for lots of countries in a standard format. 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 can hold components such as:
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.
Have a look at Database Answers. Specifically, this covers many cases:
(All variable length character datatype)
AddressId
Line1
Line2
Line3
City
ZipOrPostcode
StateProvinceCounty
CountryId
OtherAddressDetails
Ask yourself what is the main purpose of storing this data? Do you intend to actually send mail to the person at the address? Track demographics, populations? Be able to ask callers for their correct address as part of some basic authentication/verification? All of the above? None of the above?
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.
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.
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:
<street address>
<zip> <town> <region>
<country>
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:
Also check out tag 'postal-code'.
Edit: Reverse order of region and town - per UPU
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...
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:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With