您认为地址表中列的最佳平衡是什么？
几乎每次构建应用程序时，我都会发现自己创建一个“地址”表来包含系统的地址。
您在“地址”表（或如果您对多个地址进行规范化的表）中创建哪些列，其背后的原因是什么？
Almost every time that I build an application, I find myself creating an 'address' table to contain addresses for the system.
What columns do you create within your 'address' table (or tables if you normalize addresses over more than one) and what is your reasoning behind it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
不包括国家/地区，所有这些字段都是文本 (varchar)。
这假设收件人存储在其他地方。
例如，丢失其中一行并将其替换为：
，因为这可以帮助验证、通过 Web 服务查找地址等。
Excluding country, all of these fields are text (varchar).
This assumes that the recipient is stored elsewhere.
It can also be appropriate to, say, lose one of the lines and replace it with:
as that can help with validation, finding the address via Web Services and so on.
我将给出一个“视情况而定”的答案。如果您的应用程序只关心正确打印的地址标签的布局，则一组行（line1、line2、line3 等）可能就足够了。文本 blob 也可能有效，具体取决于数据的输入方式。给用户一个框并让他们输入？让它们适合 3、4、5 行？任何。
但是，如果您希望能够对数据进行“处理”，例如按邮政编码排序、按城市、州和/或国家/地区分析分布，或跟踪街道地址（10 Main St.）中有多少位数字。 vs. 54321 Main St.），那么您需要为每条重要信息提供单独的列。
似乎要求将是“包含地址的空间”，并且有关地址实际完成的操作的决定将在稍后出现......届时他们将希望能够进行排序/计数/求幂/无论如何，即使他们永远不会真正这样做。根据其他帖子中引用的链接，一旦您走向国际，它确实会变得非常复杂。我想说的是，尽量保持简单合理，其中“合理”取决于需求背后的业务推理。
I'm going to toss in an "it depends" answer. If your application is just concerned with layout of properly printed address labels, a set of lines (line1, line2, line3, etc.) could be sufficient. A text blob might work as well, depending on how the data is entered. Give the user a box and let them type? Make them fit it in 3, 4, 5 lines? Whatever.
However, if you want to be able to "do stuff" with the data, such as sort by zipcode, analyze distribution by city, state, and/or country, or track how many digits in in your street addresses (10 Main St. vs. 54321 Main St.), then you'd want separate columns for each significant piece of information.
Seems likely the requirements wil be "include space for an address", and decisions regarding what actually gets done with the addresses will come up later... at which time they'll want to be able to sort/count/exponentiate/whatever, even if they will never actually do it. As per the link(s) referenced in other posts, once you go international it can get very complex indeed. I'd say try and keep it as simple as reasonable, where "reasonable" depends on the business reasoning behind the requirements.
在理想情况下，每个应用程序都会以规范格式（例如 UPU 或 BS7666）存储地址。将结构化地址合并到单个字符串中进行打印比随后从单个文本块中提取地址元素要容易得多。因为迟早会有人（也许不是你）想要用地址信息“做点什么”。如今，数据仓库非常流行。
不幸的是，实现像 BS7666 这样的东西通常需要地址验证软件。要求普通用户将地址符合 procrustean 格式是不合理的：我们不能指望他们能够理解
locality、town和之间的区别邮城。在未经验证的情况下将某些内容吹捧为标准格式是一种误导。但要寻求某种形式的结构。另外，至少使用正则表达式验证邮政编码/zip，以确保其格式正确。
In an ideal world every application would store addresses in a canonical format such as UPU or BS7666. It is considerably easier to amalgamate a structured address into a single string for printing than it is to subsequently extract address elements from a single blob of text. Because sooner or later somebody, maybe not you, will want to "do stuff" with the address information. Data warehouses are very fashionable these days.
Unfortunately, implementing something like BS7666 usually requires address validation software. It is unreasonable to ask a regular user to fit a address to a procrustean format: we cannot expect they will understand the difference between a
locality, atownand apost town. And it is misleading to tout something as being in a standard format when it hasn't been validated as such.But go for some form of structure. Also, at least validate the postcode/zip with regex to ensure it is in the right format.