What is the "best" way to store international addresses in a database? Answer in the form of a schema and an explanation of the reasons why you chose to normalize (or not) the way you did. Also explain why you chose the type and length of each field.
在数据库中存储国际地址的“最佳”方法是什么?以图式的形式回答，并解释为什么您选择了规范化(或不规范化)的方式。还可以解释为什么选择每个字段的类型和长度。
Note: You decide what fields you think are necessary.
注意:你决定你认为哪些领域是必要的。
6 个解决方案
#1
24
Plain freeform text.
*格式文本。
Validating all the world's post/zip codes is too hard; a fixed list of countries is too politically sensitive; mandatory state/region/other administrative subdivision is just plain inappropriate (all too often I'm asked which county I live in--when I don't, because Greater London is not a county at all).
验证世界上所有的邮政/邮政编码太困难了;固定的国家名单在政治上过于敏感;强制性的州/地区/其他行政部门是不合适的(我经常被问到我住的是哪个郡)，因为更大的伦敦根本不是一个郡。
More to the point, it's simply unnecessary. Your application is highly unlikely to be modelling addresses in any serious way. If you want a postal address, ask for the postal address. Most people aren't so stupid as to put in something other than a postal address, and if they do, they can kiss their newly purchased item bye-bye.
更重要的是，这完全没有必要。您的应用程序不太可能以任何严肃的方式对地址进行建模。如果你想要一个邮政地址，问一下邮政地址。大多数人都不会傻到只写一个邮政地址，如果他们这么做了，他们就可以亲吻他们新买的东西了。
The exception to this is if you're doing something that's naturally constrained to one country anyway. In this situation, you should ask for, say, the { postcode, house number } pair, which is enough to identify a postal address. I imagine you could achieve similar things with the extended zip code in the US.
唯一的例外是，如果你所做的事情自然地只局限于一个国家。在这种情况下，您应该请求{postcode, house number}对，这足以识别邮政地址。我想你可以用扩展后的邮政编码在美国实现类似的事情。
#2
9
In the past I've modeled forms that needed to be international after the ups/fedex shipping address forms on their websites (I figured if they don't know how to handle an international order we are all hosed). The fields they use can be used as reference for setting up your schema.
过去，我曾在他们的网站上模仿ups/fedex的送货地址表格(我想，如果他们不知道如何处理国际订单的话，我们都被激怒了)。它们使用的字段可以用作设置模式的引用。
#3
5
In general, you need to understand why you want an address. Is it for shipping/mailing? Then there is really only one requirement, have the country separate. The other lines are freeform, to be filled in by the user. The reason for this is the common forwarding strategy for mail : any incoming mail for a foreign country is forwarded without looking at the other address lines. Hence, the detailed information is parsed only by the mail sorter located in the country itself. Like the receiver, they'll be familiar with national conventions.
通常，您需要了解为什么需要一个地址。运输/邮件吗?那么真的只有一个要求，就是国家分开。其他行是*格式，由用户填写。这是邮件的常见转发策略:任何外国邮件都是在不查看其他地址的情况下被转发的。因此，详细信息仅由位于该国本身的邮件分类器解析。和接收方一样，他们也会熟悉国家惯例。
(UPS may bunch together some small European countries, e.. all the Low Countries are probably served from Belgium - the idea still holds.)
(UPS可能会将一些欧洲小国聚在一起。所有的低等国家都可能来自比利时——这一想法仍然有效。
#4
2
I think adding country/city and address text will be fine. country and city should be separate for reporting. Managers always ask for these kind of reports which you do not expect and I dont prefer running a LIKE query through a large database.
我认为添加国家/城市和地址文本是可以的。国家和城市应该分开进行报告。管理人员总是要求您不要期望这些类型的报告，而我不喜欢通过大型数据库运行类似的查询。
#5
2
Not to give Facebook undue respect. However, the overall structure of the database seems to be overlooked in many web applications launching every day. Obviously I don't think there is a perfect solution that covers all the potential variables with address structure without some hard work. That said, combined with autocomplete Facebook manages to take location input data and eliminate a majority of their redundant entries. They do this by organizing their database well enough to provide autocomplete information in a low cost, low error way to the client in real time allowing them to more or less choose the correct location from an existing list.
不要给予Facebook不应有的尊重。然而，在每天发布的许多web应用程序中，数据库的总体结构似乎被忽略了。显然，我不认为有一种完美的解决方案，可以在不做艰苦工作的情况下，用地址结构覆盖所有可能的变量。也就是说，结合自动完成的Facebook，可以获取位置输入数据，并消除大部分冗余条目。他们通过将数据库组织得足够好，以低成本、低错误的方式向客户机提供自动完成信息，从而允许他们从现有的列表中选择正确的位置。
I think the best solution is to access a third party database which contains your desired geographic scope and use it to initially seed your user location information. This will allow you to avoid doing the groudwork of creating your own. With any luck you can reduce the load on your server by allowing your new users to receive the correct autocomplete information directly off your third party supplier. Eventually you will be able to fill most autocomplete for location information such as city, country, etc. from information contained in your own database from user input data.
我认为最好的解决方案是访问包含您想要的地理范围的第三方数据库，并使用它来初始化您的用户位置信息。这将使您避免创建自己的项目。如果幸运的话，您可以通过允许新用户直接从第三方供应商接收正确的自动完成信息来减少服务器上的负载。最终，您将能够从用户输入数据中包含在您自己的数据库中的信息中填写大多数自动完成的位置信息，如城市、国家等。
#6
-3
You need to provide a bit more details about how you are planning to use the data. For example, fields like City, State, Country can either be text in the single table, or be codes which are linked to a separate table with a Foreign Key.
您需要提供更多关于计划如何使用数据的细节。例如，像City、State、Country这样的字段可以是单个表中的文本，也可以是与具有外键的单独表链接的代码。
Simplest would be
简单的会
Address_Line_01 (Required, Non blank) Address_Line_02 Address_Line_03 Landmark City (Required) Pin (Required) Province_District State (Required) Country (Required)
Address_Line_01(非空白)Address_Line_02 Address_Line_03 Landmark City (Required) Pin (Required)省区(Required)国家(Required)国家(Required)
All the above can be Text/Unicode with appropriate field lengths.
所有这些都可以是具有适当字段长度的文本/Unicode。
Phone Numbers as applicable.
电话号码是适用的。
#1
24
Plain freeform text.
*格式文本。
Validating all the world's post/zip codes is too hard; a fixed list of countries is too politically sensitive; mandatory state/region/other administrative subdivision is just plain inappropriate (all too often I'm asked which county I live in--when I don't, because Greater London is not a county at all).
验证世界上所有的邮政/邮政编码太困难了;固定的国家名单在政治上过于敏感;强制性的州/地区/其他行政部门是不合适的(我经常被问到我住的是哪个郡)，因为更大的伦敦根本不是一个郡。
More to the point, it's simply unnecessary. Your application is highly unlikely to be modelling addresses in any serious way. If you want a postal address, ask for the postal address. Most people aren't so stupid as to put in something other than a postal address, and if they do, they can kiss their newly purchased item bye-bye.
更重要的是，这完全没有必要。您的应用程序不太可能以任何严肃的方式对地址进行建模。如果你想要一个邮政地址，问一下邮政地址。大多数人都不会傻到只写一个邮政地址，如果他们这么做了，他们就可以亲吻他们新买的东西了。
The exception to this is if you're doing something that's naturally constrained to one country anyway. In this situation, you should ask for, say, the { postcode, house number } pair, which is enough to identify a postal address. I imagine you could achieve similar things with the extended zip code in the US.
唯一的例外是，如果你所做的事情自然地只局限于一个国家。在这种情况下，您应该请求{postcode, house number}对，这足以识别邮政地址。我想你可以用扩展后的邮政编码在美国实现类似的事情。
#2
9
In the past I've modeled forms that needed to be international after the ups/fedex shipping address forms on their websites (I figured if they don't know how to handle an international order we are all hosed). The fields they use can be used as reference for setting up your schema.
过去，我曾在他们的网站上模仿ups/fedex的送货地址表格(我想，如果他们不知道如何处理国际订单的话，我们都被激怒了)。它们使用的字段可以用作设置模式的引用。
#3
5
In general, you need to understand why you want an address. Is it for shipping/mailing? Then there is really only one requirement, have the country separate. The other lines are freeform, to be filled in by the user. The reason for this is the common forwarding strategy for mail : any incoming mail for a foreign country is forwarded without looking at the other address lines. Hence, the detailed information is parsed only by the mail sorter located in the country itself. Like the receiver, they'll be familiar with national conventions.
通常，您需要了解为什么需要一个地址。运输/邮件吗?那么真的只有一个要求，就是国家分开。其他行是*格式，由用户填写。这是邮件的常见转发策略:任何外国邮件都是在不查看其他地址的情况下被转发的。因此，详细信息仅由位于该国本身的邮件分类器解析。和接收方一样，他们也会熟悉国家惯例。
(UPS may bunch together some small European countries, e.. all the Low Countries are probably served from Belgium - the idea still holds.)
(UPS可能会将一些欧洲小国聚在一起。所有的低等国家都可能来自比利时——这一想法仍然有效。
#4
2
I think adding country/city and address text will be fine. country and city should be separate for reporting. Managers always ask for these kind of reports which you do not expect and I dont prefer running a LIKE query through a large database.
我认为添加国家/城市和地址文本是可以的。国家和城市应该分开进行报告。管理人员总是要求您不要期望这些类型的报告，而我不喜欢通过大型数据库运行类似的查询。
#5
2
Not to give Facebook undue respect. However, the overall structure of the database seems to be overlooked in many web applications launching every day. Obviously I don't think there is a perfect solution that covers all the potential variables with address structure without some hard work. That said, combined with autocomplete Facebook manages to take location input data and eliminate a majority of their redundant entries. They do this by organizing their database well enough to provide autocomplete information in a low cost, low error way to the client in real time allowing them to more or less choose the correct location from an existing list.
不要给予Facebook不应有的尊重。然而，在每天发布的许多web应用程序中，数据库的总体结构似乎被忽略了。显然，我不认为有一种完美的解决方案，可以在不做艰苦工作的情况下，用地址结构覆盖所有可能的变量。也就是说，结合自动完成的Facebook，可以获取位置输入数据，并消除大部分冗余条目。他们通过将数据库组织得足够好，以低成本、低错误的方式向客户机提供自动完成信息，从而允许他们从现有的列表中选择正确的位置。
I think the best solution is to access a third party database which contains your desired geographic scope and use it to initially seed your user location information. This will allow you to avoid doing the groudwork of creating your own. With any luck you can reduce the load on your server by allowing your new users to receive the correct autocomplete information directly off your third party supplier. Eventually you will be able to fill most autocomplete for location information such as city, country, etc. from information contained in your own database from user input data.
我认为最好的解决方案是访问包含您想要的地理范围的第三方数据库，并使用它来初始化您的用户位置信息。这将使您避免创建自己的项目。如果幸运的话，您可以通过允许新用户直接从第三方供应商接收正确的自动完成信息来减少服务器上的负载。最终，您将能够从用户输入数据中包含在您自己的数据库中的信息中填写大多数自动完成的位置信息，如城市、国家等。
#6
-3
You need to provide a bit more details about how you are planning to use the data. For example, fields like City, State, Country can either be text in the single table, or be codes which are linked to a separate table with a Foreign Key.
您需要提供更多关于计划如何使用数据的细节。例如，像City、State、Country这样的字段可以是单个表中的文本，也可以是与具有外键的单独表链接的代码。
Simplest would be
简单的会
Address_Line_01 (Required, Non blank) Address_Line_02 Address_Line_03 Landmark City (Required) Pin (Required) Province_District State (Required) Country (Required)
Address_Line_01(非空白)Address_Line_02 Address_Line_03 Landmark City (Required) Pin (Required)省区(Required)国家(Required)国家(Required)
All the above can be Text/Unicode with appropriate field lengths.
所有这些都可以是具有适当字段长度的文本/Unicode。
Phone Numbers as applicable.
电话号码是适用的。