Alex Rivera | Logout

Best practices for consistent and comprehensive address storage in a database

Asked 2008-09-24T09:29:53.943
27

Are there any best practices (or even standards) to store addresses in a consistent and comprehensive way in a database ?

To be more specific, I believe at this stage that there are two cases for address storage :

  • you just need to associate an address to a person, a building or any item (the most common case). Then a flat table with text columns (address1, address2, zip, city) is probably enough. This is not the case I'm interested in.
  • you want to run statistics on your addresses : how many items in a specific street, or city or... Then you want to avoid misspellings of any sorts, and ensure consistency. My question is about best practices in this specific case : what are the best ways to model a consistent address database ?

A country specific design/solution would be an excellent start.

ANSWER : There does not seem to exist a perfect answer to this question yet, but :

  • xAL, as suggested by Hank, is the closest thing to a global standard that popped up. It seems to be quite an overkill though, and I am not sure many people would want to implement it in their database...
  • To start one's own design (for a specific country), Dave's link to the Universal Postal Union (UPU) site is a very good starting point.
  • As for France, there is a norm (non official, but de facto standard) for addresses, which bears the lovely name of database standards modeling
Edit
Report

3 Answers

35

I've been thinking about this myself as well. Here are my loose thoughts so far, and I'm wondering what other people think.

xAL (and its sister that includes personal names, XNAL) is used by both Google and Yahoo's geocoding services, giving it some weight. But since the same address can be described in xAL in many different ways--some more specific than others--then I don't see how xAL itself is an acceptable format for data storage. Some of its field names could be used, however, but in reality the only basic format that can be used among the 16 countries that my company ships to is the following:


enum address-fields 
{
    name,
    company-name,
    street-lines[], // up to 4 free-type street lines
    county/sublocality,
    city/town/district,
    state/province/region/territory,
    postal-code,
    country
}

That's easy enough to map into a single database table, just allowing for NULLs on most of the columns. And it seems that this is how Amazon and a lot of organizations actually store address data. So the question that remains is how should I model this in an object model that is easily used by programmers and by any GUI code. Do we have a base Address type with subclasses for each type of address, such as AmericanAddress, CanadianAddress, GermanAddress, and so forth? Each of these address types would know how to format themselves and optionally would know a little bit about the validation of the fields.

They could also return some type of metadata about each of the fields, such as the following pseudocode data structure:


structure address-field-metadata 
{
    field-number,     // corresponds to the enumeration above
    field-index,      // the order in which the field is usually displayed
    field-name,       // a "localized" name; US == "State", CA == "Province", etc
    is-applicable,    // whether or not the field is even lo
answered 2009-01-05T22:54:29.870
1

In the US, I'd suggest choosing a National Change of Address vendor and model the DB after what they return.

answered 2008-09-24T10:57:44.200
0

I asked something quite similar earlier: Dynamic contact information data/design pattern: Is this in any way feasible?.

The short answer: Storing adderres or any kind of contact information in a database is complex. The Extendible Address Language (xAL) link above has some interesting information that is the closest to a standard/best practice that I've come accross...

answered 2008-09-24T10:17:24.220

Your Answer