title: Best practices for consistent and comprehensive address storage in a database
body: <p>Are there any best practices (or even standards) to store addresses in a consistent and comprehensive way in a database ?</p>
  
  <p>To be more specific, I believe at this stage that there are two cases for address storage :</p>
  
  <ul>
  <li>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.</li>
  <li>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 ?</li>
  </ul>
  
  <p>A country specific design/solution would be an excellent start.</p>
  
  <p><strong>ANSWER</strong> : There does not seem to exist a perfect answer to this question yet, but :</p>
  
  <ul>
  <li><a href="http://www.oasis-open.org/committees/ciq/ciq.html#6" rel="noreferrer">xAL</a>, as <a href="https://stackoverflow.com/questions/126207/best-practices-for-consistent-and-comprehensive-address-storage-in-a-database#126219">suggested by Hank</a>, 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...</li>
  <li>To start one's own design (for a specific country), <a href="https://stackoverflow.com/questions/126207/best-practices-for-consistent-and-comprehensive-address-storage-in-a-database#126860">Dave's link</a> to the <a href="http://www.upu.int/post_code/en/postal_addressing_systems_member_countries.shtml" rel="noreferrer">Universal Postal Union</a> (UPU) site is a very good starting point.</li>
  <li>As for France, there is a norm (non official, but de facto standard) for addresses, which bears the lovely name of <a href="http://www.boutique.afnor.org/NEL5DetailNormeEnLigne.aspx?&amp;nivCtx=NELZNELZ1A10A101A107&amp;ts=1843530&amp;CLE_ART=FA047725" rel="noreferrer">AFNOR XP Z10-011</a> (french only), and has to be paid for. The <a href="http://www.upu.int/post_code/en/postal_addressing_systems_member_countries.shtml" rel="noreferrer">UPU</a> description for France is based on this norm.</li>
  <li>I happened to find the equivalent norm for Sweden : <a href="http://www.sis.se/DesktopDefault.aspx?tabName=@DocType_1&amp;Doc_ID=34763" rel="noreferrer">SS 613401</a>.</li>
  <li>At European level, some effort has been made, resulting in the norm EN 14142-1. It is obtainable via <a href="http://www.cen.eu/cenorm/members/national+members/members.asp" rel="noreferrer">CEN national members</a>.</li>
  </ul>