在数据库中实现一致和全面的地址存储的最佳做法 [英] Best practices for consistent and comprehensive address storage in a database

问题描述

有没有任何最佳实践（甚至标准）以一致和全面的方式在数据库中存储地址？

更具体地说，我相信阶段，地址存储有两种情况：

• 您只需要将地址关联到某人，建筑物或任何项目最常见的情况）。然后，具有文本列（address1，address2，zip，city）的平面表可能就足够了。这不是我感兴趣的。


• 您想要对您的地址运行统计信息：在特定街道，城市或...中有多少项目...然后您想避免任何类型的拼写错误，并确保一致性。我的问题是关于此特定情况下的最佳做法：对一致的地址数据库建模的最佳方法是什么？

/解决方案将是一个很好的开始。

ANSWER ：似乎不存在这个问题的完美答案， p>

• xAL ，作为由Hank建议的，是最接近的一个全局标准弹出。


• 要开始自己的设计（针对特定国家/地区），您可以在 Dave的链接 万国邮政联盟（万国邮联）网站是一个非常好的起点。 li>
  
• 对于法国来说，地址有一个规范（非官方，但事实上的标准），其中包含可爱的名字 AFNOR XP Z10-011 （仅限法语），必须支付。法国的 UPU 说明基于此规范。


• 我偶然发现了瑞典的等效规范： SS 613401 。


• 在欧洲层面，我们做出了一些努力，导致EN 14142-1标准。可通过 CEN本国成员获取。

解决方案

我会使用 Address 正如你所建议的，我将根据 xAL 。

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 AFNOR XP Z10-011 (french only), and has to be paid for. The UPU description for France is based on this norm.
• I happened to find the equivalent norm for Sweden : SS 613401.
• At European level, some effort has been made, resulting in the norm EN 14142-1. It is obtainable via CEN national members.

解决方案

I'd use an Address table, as you've suggested, and I'd base it on the data tracked by xAL.

这篇关于在数据库中实现一致和全面的地址存储的最佳做法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！