在SQL数据库中存储地址的最佳实践/标准 [英] Best Practice / Standard for storing an Address in a SQL Database

问题描述

我想知道是否存在某种标准"用于在数据库中存储美国地址?看来这是一项常见的任务，应该有某种标准.

I am wondering if there is some sort of "standard" for storing US addresses in a database? It seems this is a common task, and there should be some sort of a standard.

我正在寻找的是一个特定模式，该模式关于数据库表应如何工作和交互的方式已经以第三种标准形式出现，包括数据类型(MySQL).一个好的UML文档将起作用.

What I am looking for is a specific schema of how the database tables should work and interact, already in third normal form, including data types (MySQL). A good UML document would work.

也许我只是在偷懒，但这是一个非常常见的任务，并且我确信有人发布了一种有效的方法来在某处做到这一点.我只是不知道在哪里寻找，而Google却无济于事.请指出我的资源.谢谢.

Maybe I'm just being lazy, but this is a very common task, and I am sure someone has published an efficient way to do this somewhere. I just don't know where to look and Google isn't helping. Please point me to the resource. Thanks.

编辑

尽管这更是一个普遍的问题，但我想澄清一下我的具体需求.

Although this is more of a general question, I would like to clarify my specific needs.

地址将用于指定事件位置的道路地址.这些地址必须采用可以最好地分解和搜索的格式，并且还可能被最终可能将数据源链接到的任何第三方应用程序使用.

Addresses will be used to specify road addresses of locations of events. These addresses will need to be in a format that can be best broken down and searched, and also used by any third-party applications I may end up linking my data source to.

也.数据将在输入时进行地理编码(长，纬度)并分别存储，因此，无论哪种地理编码器/应用程序/库，数据都必须符合(尚未确定)协议.

ALSO. Data will be geo-coded (long, lat) on entry and stored separately, so it must fit the (yet undecided) protocol of whatever geocoder / application / library does that.

推荐答案

http://www.upu.int 具有国际地址的格式标准. http://usps.com 上的出版物28具有美国格式标准.

http://www.upu.int has the format standards for international addresses. Publication 28 at http://usps.com has the U.S. format standards.

USPS希望将以下未标点的地址部分连接在一行上:

The USPS wants the following unpunctuated address components concatenated on a single line:

* house number
* predirectional (N, SE, etc)
* street
* suffix (AVE, BLVD, etc)
* postdirectional (SW, E, etc)
* unit (APT, STE, etc)
* apartment/suite number

例如102 N MAIN ST SE APT B.

Eg, 102 N MAIN ST SE APT B.

如果将整个地址行都保留在数据库中的单个字段中，则输入和编辑很容易，但是搜索可能会更加困难(例如，如果SOUTH EAST LANE是S EAST LN中的EAST或EAST LN，则是EAST的街道.像SE LANE ST一样是LANE?).

If you keep the entire address line as a single field in your database, input and editing is easy, but searches can be more difficult (eg, in the case SOUTH EAST LANE is the street EAST as in S EAST LN or is it LANE as in SE LANE ST?).

如果您将地址解析为单独的字段，则搜索街道名称或公寓之类的内容会变得更加容易，但是您必须将所有内容附加在一起以进行输出，您需要CASS软件才能正确解析，并需要邮政信箱，乡村路线地址，和APO/FPO地址具有特殊的解析.

If you keep the address parsed into separate fields, searches for components like street name or apartments become easier, but you have to append everything together for output, you need CASS software to parse correctly, and PO boxes, rural route addresses, and APO/FPO addresses have special parsings.

在一个具有多个地址的物理位置是一个多单元建筑物，在这种情况下，APT和STE等单元后面的字母/数字指定了地址，或者是商业邮件接收机构(例如，UPS商店)和一个邮件投递站/private邮箱号码会被附加(例如100 MAIN ST STE B PMB 102)，或者这是一家拥有一个USPS传递点的公司，并且在USPS传递后路由邮件(这通常需要公司可能需要一个单独的mailstop字段，但USPS赢得了不想在地址行上.

A physical location with multiple addresses at that location is either a multiunit building, in which case letters/numbers after units like APT and STE designate the address, or it's a Commercial Mail Receiving Agency (eg, UPS store) and a maildrop/private mailbox number is appended (like 100 MAIN ST STE B PMB 102), or it's a business with one USPS delivery point and mail is routed after USPS delivery (which usually requires a separate mailstop field which the company might need but the USPS won't want on the address line).

具有多个物理地址的联系人通常是具有街道地址和邮政信箱的企业或个人.请注意，每个地址通常都有不同的邮政编码.

A contact with more than one physical address is usually a business or person with a street address and a PO box. Note that it's common for each address to have a different ZIP code.

非常典型的是，一项业务交易可能具有送货地址和账单地址(同样，具有不同的邮政编码).我为每个地址保留的信息是:

It's quite typical that one business transaction might have a shipping address and a billing address (again, with different ZIP codes). The information I keep for EACH address is:

* name prefix (DR, MS, etc)
* first name and initial
* last name
* name suffix (III, PHD, etc)
* mail stop
* company name
* address (one line only per Pub 28 for USA)
* city
* state/province
* ZIP/postal code
* country

我通常会在人名与公司之间的某个地方打印邮件停靠站，因为该国家/地区包含州/邮编，该州/邮政编码包含一个城市，该城市包含一个包含公司的地址，该公司包含一个包含该人的邮件站.输入或编辑时，我使用CASS软件来验证和标准化地址.

I typically print mail stops somewhere between the person's name and company because the country contains the state/ZIP which contains the city which contains the address which contains the company which contains the mail stop which contains the person. I use CASS software to validate and standardize addresses when entered or edited.

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