在数据库 (RDBMS) 中存储邮政地址的最佳实践? [英] Best practices for storing postal addresses in a database (RDBMS)?

问题描述

是否有任何关于在 RDBMS 中存储邮政地址的最佳实践的好的参考资料?似乎有很多权衡可以进行，每个权衡都有很多优点和缺点需要评估——这肯定已经一次又一次地完成了吗?也许有人至少在某处写过一些经验教训?

Are there any good references for best practices for storing postal addresses in an RDBMS? It seems there are lots of tradeoffs that can be made and lots of pros and cons to each to be evaluated -- surely this has been done time and time again? Maybe someone has at least written done some lessons learned somewhere?

我正在谈论的权衡示例是将邮政编码存储为整数与字符字段，是否应将门牌号码存储为单独的字段或地址行 1 的一部分，是否应将套房/公寓/等号码标准化或只是在地址行 2 中存储为一大块文本，您如何处理 zip +4(单独的字段或一个大字段，整数与文本)?等

Examples of the tradeoffs I am talking about are storing the zipcode as an integer vs a char field, should house number be stored as a separate field or part of address line 1, should suite/apartment/etc numbers be normalized or just stored as a chunk of text in address line 2, how do you handle zip +4 (separate fields or one big field, integer vs text)? etc.

此时我主要关注美国地址，但我想有一些最佳做法可以让自己为走向全球的可能性做好准备(例如，适当地命名字段，如地区而不是州或邮政编码，而不是邮政编码等

I'm primarily concerned with U.S. addresses at this point but I imagine there are some best practices in regards to preparing yourself for the eventuality of going global as well (e.g. naming fields appropriately like region instead of state or postal code instead of zip code, etc.

推荐答案

对于更多的国际使用，要考虑的一种模式是 Drupal 地址字段.它基于 xNAL 标准，似乎涵盖了大多数国际案例.深入研究该模块将揭示一些用于解释和验证国际地址的好方法.它还有一组很好的带有 ISO 代码的行政区域(省、州、州等).

For more international use, one schema to consider is the one used by Drupal Address Field. It's based on the xNAL standard, and seems to cover most international cases. A bit of digging into that module will reveal some nice pearls for interpreting and validating addresses internationally. It also has a nice set of administrative areas ( province, state, oblast, etc ) with ISO codes.

这是从模块页面复制的架构要点:

Here's the gist of the schema, copied from the module page:

country => Country (always required, 2 character ISO code)
name_line => Full name (default name entry)
first_name => First name
last_name => Last name
organisation_name => Company
administrative_area => State / Province / Region (ISO code when available)
sub_administrative_area => County / District (unused)
locality => City / Town
dependent_locality => Dependent locality (unused)
postal_code => Postal code / ZIP Code
thoroughfare => Street address
premise => Apartment, Suite, Box number, etc.
sub_premise => Sub premise (unused)

我的经验教训:

• 不要以数字形式存储任何内容.
• 尽可能将国家和行政区域存储为 ISO 代码.
• 如果您不知道，请在要求字段时松懈.某些国家/地区可能不会使用您认为理所当然的字段，即使是诸如 locality 之类的基本内容.路途.

• Don't store anything numerically.
• Store country and administrative area as ISO codes where possible.
• When you don't know, be lax about requiring fields. Some country may not use fields you take for granted, even basic things like locality & thoroughfare.

这篇关于在数据库 (RDBMS) 中存储邮政地址的最佳实践?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！