规范地址 [英] Normalize an Address

问题描述

我正在尝试规范化一个地址。

下图显示了我相信的这个问题的相关表。我想知道如何将ZipCode集成到模型中。这将是国际地址，所以我知道一个ZIP / PostalCode没有使用到处。我想City :: ZipCode是1 :: 0-n（我已经看过别人说这并不总是这样，但他们从未提供证据）。如果它们是正确的，那么我想这将是一个多对多的关系。由于每个地址最多只能有一个ZipCode，而ZipCode可以包含很多地址，所以我在如何标准化这个模型时迷失了。

由于地址可能包含或不包含a包含一个ZipCode我需要避免把它作为一个可空的FK在地址表。

编辑：只是想强调， strong>所提供的实体和属性从实际DB 大幅缩小。它仅作为参考，并且用于解决我在哪里将邮政编码包含在模型中。

解决方案

添加一个表Address-ZipCode表，用外键地址ID和邮编;和主键地址Id - 与地址表中的相同。然后通过使用地址和新表之间的左连接来包括邮政编码。新的表格只会在地址有邮政编码时填写。

但是，我建议，如果您想容纳国际地址，那么您所拥有的架构很可能不足 - 您将需要多个地址行和更多级别的类别，如图所示。类别错过包括国家，分区域，城镇和其他可能的其他。

我的回答（这是非常长的）显示了全面处理国际地址（和其他事情）需要什么。这是巨大的过度杀戮，除非你正在处理多个国家/地区的数百万个地址。

I am trying to normalize an address.

The diagram below shows the relevant tables for this question I believe. I want to know how ZipCodes should be integrated into the model. This would be for international addresses so I know that a Zip/PostalCode is not used everywhere. I think City::ZipCode is 1::0-n (I have read others saying this is not always the case but they never provided evidence). If they are correct then I guess this would be a many-to-many relationship. Since each Address can only have at most one ZipCode while a ZipCode can contain many addresses I am lost at how to normalize this model.

Since the Address may or may not contain a contain a ZipCode I need to refrain from having that as a nullable FK in the Address table.

EDIT: Just want to emphasize that the entities and attributes provided are drastically scaled back from the actual DB. It is only used as a reference and to address my concern of where to include zipcodes into the model.

解决方案

To normalise the schema you have; add a table Address-ZipCode table, with foreign keys Address ID and Zip Code; and primary key Address Id - identical to that in the Address table. Then include the Zip codes by using a Left Join between address and the new table. The new table will only be populated when an address has a zipcode.

However, I would suggest that if you are trying to accommodate international addresses, the schema you have is likely to be inadequate - you will need multiple address lines and more levels of category than shown in your diagram. Categories missed include country, sub-region, town, and possibly others.

My answer here (which is extremely long) shows what is needed to deal with international addresses (and other things) comprehensively. This is massive overkill unless you are dealing with millions of addresses in each of multiple countries.

这篇关于规范地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！