SQL数据库设计最佳实践（地址）

Question

当然，我意识到设计 SQL 数据库并没有一种“正确的方法”，但我想听听在我的特定情况下哪种设计更好些，哪种更差些。

目前，我正在设计一个订单输入模块（Windows.NET 4.0 应用程序和 SQL Server 2008），对于可以应用在多个地方的数据，我在两个设计决策之间犹豫不决。在这个问题中，我将具体提到地址。

地址可以被各种对象使用（如订单、客户、员工、装运等），它们几乎总是包含相同的数据（地址 1/2/3、城市、州、邮政编码、国家等）。最初，我打算将每个字段作为相关表中的列包含进来（例如，订单将包含地址 1/2/3、城市、州等，而客户也将包含相同的列布局）。但我的一部分想要将 DRY / 规范化原则应用于这种情况，即创建一个名为“地址”的表，在适当的表中通过外键引用它。

CREATE TABLE DB.dbo.Addresses
    (
        Id          INT
                    NOT NULL
                    IDENTITY(1, 1)
                    PRIMARY KEY
                    CHECK (Id > 0),

        Address1    VARCHAR(120)
                                NOT NULL,

        Address2    VARCHAR(120),

        Address3    VARCHAR(120),

        City        VARCHAR(100)
                    NOT NULL,

        State       CHAR(2)
                    NOT NULL,

        Country     CHAR(2)
                    NOT NULL,

        PostalCode  VARCHAR(16)
                    NOT NULL
    )

CREATE TABLE DB.dbo.Orders
    (
        Id          INT
                    NOT NULL
                    IDENTITY(1000, 1)
                    PRIMARY KEY
                    CHECK (Id > 1000),

        Address     INT
                    CONSTRAINT fk_Orders_Address
                    FOREIGN KEY REFERENCES Addresses(Id)
                    CHECK (Address > 0)
                    NOT NULL,

        -- other columns....
    )

CREATE TABLE DB.dbo.Customers
    (
        Id          INT
                    NOT NULL
                    IDENTITY(1000, 1)
                    PRIMARY KEY
                    CHECK (Id > 1000),

        Address     INT
                    CONSTRAINT fk_Customers_Address
                    FOREIGN KEY REFERENCES Addresses(Id)
                    CHECK (Address > 0)
                    NOT NULL,

        -- other columns....
    )

从设计角度来看，我喜欢这种方法，因为它创建了一个标准的地址格式，很容易更改。例如，如果我需要添加Address4，我只需要在一个地方添加，而不是每个表都要添加。然而，我可以看到构建查询所需的JOIN数量可能会变得有些疯狂。

我想知道是否有任何企业级SQL架构师成功地使用过这种方法，或者这导致的JOIN数量会造成性能问题？

- Vince Fedorchak

---

在 Stack Overflow 上肯定有相关的问答 - 现在正在寻找它们。 - Jonathan Leffler

以下是一些可能会对您有帮助的链接：297855，310540，373084，929684，3094126，4840928。 - Jonathan Leffler

@Jonathan：所有的帖子都很好！但愿你可以在评论上点+1。 :) - Vince Fedorchak

这是一个很好的惯例，点赞！ - Alix Axel

在美国和加拿大以外的地区，州名可以是三个或更多字母的缩写，例如澳大利亚的“NSW”代表“新南威尔士州”，或者英国所有郡县都是三个字母（AVN-“阿文”，MCH-“曼彻斯特”）。 - binderbound

我知道这已经很老了，但我想补充一点。你可能会发现在某些表中将地址存储为FK会导致你不想要的行为。是的，如果客户地址更改，您只需要在一个地方更改即可，但有一些位置您可能不希望更新。其中之一就是订单历史记录。如果客户下订单、收到货物、感到满意，然后搬家并更新他们的地址，过去的订单地址不应更改。它已经被运送和交付，更改其地址意味着它现在是错误的。这也意味着您失去了实际运送到的地址。 - Air

Answer 1

通过将地址拆分成自己的表，您已经朝着正确方向迈出了一步。我想再提出一些建议。

1. 考虑将顾客/订单表中的地址 FK 列取出并创建联接表。换句话说，现在在设计中将顾客/地址和订单/地址作为多对多关系，以便未来可以轻松支持多个地址。是的，这意味着引入更多的表和连接，但所获得的灵活性是值得努力的。

2. 考虑为城市、州和国家实体创建查找表。地址表的城市/州/国家列则由指向这些查找表的 FK 组成。这样可以确保所有地址的拼写一致，并为将来需要的情况提供存储其他元数据（例如城市人口）的位置。

- Joe Stefanelli

---

@VinceFedorchak 这就是我想表达的意思。 - Joe Stefanelli

它在搜索操作中的表现如何？假设我想要从特定地区（地址/城市/州）搜索客户？ - vivex

一个地图表用于用户和地址，另一个地图表用于订单和地址？ - Jashwant

Answer 2

我只是有一些警告。对于这些问题，有多种解决方法。

首先，规范化并不意味着“用ID号码替换文本”。

其次，你没有一个关键字。虽然你声明了一个“PRIMARY KEY”列，但这还不够。

insert into Addresses 
  (Address1, Address2, Address3, City, State, Country, PostalCode)
values
  ('President Obama', '1600 Pennsylvania Avenue NW', NULL, 'Washington', 'DC', 'US', '20500'),
  ('President Obama', '1600 Pennsylvania Avenue NW', NULL, 'Washington', 'DC', 'US', '20500'),
  ('President Obama', '1600 Pennsylvania Avenue NW', NULL, 'Washington', 'DC', 'US', '20500'),
  ('President Obama', '1600 Pennsylvania Avenue NW', NULL, 'Washington', 'DC', 'US', '20500');

select * from Addresses;

1;President Obama;1600 Pennsylvania Avenue NW;;Washington;DC;US;20500
2;President Obama;1600 Pennsylvania Avenue NW;;Washington;DC;US;20500
3;President Obama;1600 Pennsylvania Avenue NW;;Washington;DC;US;20500
4;President Obama;1600 Pennsylvania Avenue NW;;Washington;DC;US;20500

如果没有其他约束，你的“主键”标识一行数据，而不是地址。通常仅仅标识一行数据是不够的。

第三点，“Address1”，“Address2”和“Address3”不是地址属性，而是邮寄标签的属性（邮寄标签上的行）。这种区别可能对你来说并不重要，但对我来说非常重要。

第四点，地址有生命周期，在出生和死亡之间，它们有时会发生变化，比如街道被重新规划、建筑物被分割或合并，有时当城市工作人员喝醉了时，甚至自然灾害也可能导致整个社区被摧毁。有时建筑物编号也会改变。在我们的数据库中（与大多数数据库相比），约有1%每年都会发生这样的变化。

当一个地址失效时，你需要做两件事情：

• 确保没有人使用该地址邮寄或者发货等操作。
• 确保它的失效不会影响历史数据。

当一个地址本身发生更改时，你需要做两件事情：

• 一些数据必须反映这种变化，请确保它们进行了更新。
• 一些数据则不能反映这种变化，请确保它们没有被更新。

第五点，DRY原则不适用于外键。它们的整个目的就是重复使用。唯一的问题是关键字有多宽？一个id号很窄，但需要联接（10个id号可能需要10个联接）。地址很宽，但不需要联接（我在这里谈论的是真正的地址，而不是邮寄标签）。

这就是我能想到的所有内容。

- Mike Sherrill 'Cat Recall'

---

谢谢你的建议。你说仅仅识别一行是不够好的，但是你没有提供替代方案。你的意思是我的主键应该由表中多列组合而成，而不是一个自增的INT类型？那么我如何在外键中引用特定的地址呢？ - Vince Fedorchak

你正在重复外键；你只是重复一个整数而不是文本。当外键是有意义的文本时，“ON UPDATE CASCADE”允许您在单个位置更新值，除非您的DBMS不支持该功能（Oracle不支持）。但是，当您处理地址时，通常需要特别小心以避免更新某些历史数据类型，例如账单和发票上的地址。无论您的外键是文本还是整数，这都是正确的。 - Mike Sherrill 'Cat Recall'

请记住，如果在自然键上放置唯一索引，则仍然可以使用代理键。这样做可以兼顾两全，因为您无法输入重复数据，但是您可以使用较小的内容进行连接，并且不必更改数百万个子记录，因为邮政编码已更改。 - HLGEM

那么如何唯一标识逻辑地址记录呢？我们应该做一些哈希键，它是由AddressLine1、City、State和Zip组合而成的，这样代码就可以识别传入的地址是否已经存在于PK/Id=1（在上面的示例中）中了。 - Shiva

除非在非常罕见的情况下，否则生成一个没有相应自然键的代理键是一个不好的想法。如果不更新，则变得非常困难，除非采取措施，否则会创建许多重复条目（如@mike-sherrill-cat-recall所示）。而且，许多代理键和多对多交集表的连接开销也不可忽略。我不是说不要创建地址表。我是说要仔细考虑数据的整个生命周期，以确定它是否真正适合您。 - Charlie Reitzel

Answer 3

如果地址是独立实体，具有身份（意味着两个对象指向相同或不同的地址很重要）并且与其他实体有自己的生命周期，那么您希望地址在单独的表中。如果您的领域是这种情况，我认为这将是完全明显的，您不需要问这个问题。Cade's answer解释了地址的可变性，例如送货地址是订单的一部分，不应该在所属订单之下更改。这表明送货地址没有自己的生命周期。尝试将其处理为单独的实体只会导致更多的错误机会。"规范化"特指从数据中删除冗余，以便您不必在不同位置表示相同的项。在这里，唯一的冗余在DDL中，而不在数据中，因此这里不相关于"规范化"。（JPA有嵌入类的概念可以解决冗余）。简而言之：如果地址真的是一个具有独特身份和生命周期的实体，请使用单独的表。否则不需要。

- Nathan Hughes

---

我不认为这是规范化，但标准化确实有一些好处——基本地址表设计对于所有“地址”都是一致的，如果发生设计更改，只需在一个地方进行修改即可。 - Cade Roux

@Cade：是的，保持一致性是一个很好的目标，但也可以通过使用脚本生成DDL等其他方式来减少重复。 - Nathan Hughes

实际上，如果您在同一实体中有多个地址，可能处于不同的角色或不是，那么规范化将适用，然后开始跨实体进行操作更具有意义。 - Cade Roux

又一篇好文章。说得好，Nathan Hughes。 - Mosia Thabo

@Mosia：谢谢，我很感激你的反馈意见。我重新审视了一下并且稍作修改。 - Nathan Hughes

Answer 4

我认为您可能没有意识到的一个问题是，这些数据中有一些是与时间相关的。您不希望您的记录显示您将订单发往芝加哥州际35号街道，伊利诺伊州，而实际上您将其发送到西弗吉尼亚州马丁斯堡市国王街10号，但客户在订单发货后两年搬家了。因此，是的，请构建一个地址表，以获取该时刻的地址，只要像客户这样的人的地址发生任何更改，都会导致一个新的地址ID而不是更改当前地址，这会破坏订单历史记录。

- HLGEM

---

大多数情况下，使用者会更喜欢当前地址，并从中受益而进行规范化。但正如你所说，还有其他需要“截至”地址的用例。对于这些情况，通常需要将当前值的快照存入冻结表中。这个逻辑适用于许多类型的数据，不仅仅是地址。它可以是产品（停产）或价格（变动）等等。 - Charlie Reitzel

Answer 5

你需要回答的问题是，在日常语言中使用的"同一地址"是否实际上在你的数据库中也是"同一地址"。如果有人"更改了他的地址"（口语），他实际上是将自己链接到另一个地址。只有当街道更名、邮政编码改革或核弹爆炸时，地址本身才会发生变化。而这些事件很少发生（希望大多数情况下如此）。你的主要利润就在于：在一个地方改变多个行（多个表中的行）。

如果您在模型中实际更改地址-即在地址表上进行更新-可能对链接到它的其他行起作用，也可能不会起作用。此外，根据我的经验，即使是完全相同的地址，也必须针对不同的目的看起来不同。理解语义差异，您将得到最能代表您现实世界的正确模型。

我有许多数据库都使用一个共同的“街道”表（其中使用城市表（其中使用国家表，...））。与街道编号结合考虑，它是地理编码（lat / lon），而不是“街道名称”。不同的表（或行）之间不共享地址。街道名称和邮政编码的更改是级联的，而其他更改则不是。

- Erwin Brandstetter

---

您介意再解释一下您最后一段的内容吗？我不太理解其中任何一部分，而且我对您如何处理地址很感兴趣。您所说的“共同”的街道表是什么意思？街道号码是否存储在同一张表中？您如何将用户与地址联系起来？比如订单？当您说地址不是共享的时，您是指您街道表中的任何一行都只被另一张表中的一行“拥有”吗？更改街道名称和邮政编码会以何种方式级联？您所说的只是id方面的级联，还是您会“盖章”字段？ - Vorpulus Lyphane

Vorpulus - 想象一下有一个包含地址的表格，以及用于城市和国家的单独表格。事实上，城市或国家的生命周期更长，每个注册用户都会选择一个已知的城市，因此您可以为他们创建一个始终可选的表格。但是地址（街道号码和名称）随时可能会发生变化。因此，您只需通过ID引用城市和国家即可。 - Mosia Thabo

Answer 6

在城市、州和国家方面，应该维护一些主表。这样可以避免这些实体的不同拼写可能会导致将同一城市映射到某个不同的州/国家。

可以像下面展示的那样，将CityId在地址表中作为外键进行简单映射，而不是在地址表本身中将所有三个字段（城市、州和国家）作为纯文本分开。

Address: {
    CityId
    // With other fields
}

City: {
   CityId
   StateId
  // Other fields
}

State: {
   StateId
   CountryId
 // Other fields
}

Country: {
  CountryId
  // Other fields
}

如果在地址表中维护所有三个ID（CityId、StateId和CountryId），最终你将不得不针对这些表进行连接。因此，我的建议是仅使用CityId，然后通过与上述表结构的连接检索所需的其余信息。"最初的回答"

- Anant Bandewar

---

这也是一个非常好的想法。可能是我最近采用的方法。 - Mosia Thabo

我们在国际方面使用这种格式遇到了一些问题。 - Lucas Zientek

Answer 7

通常情况下，您应该尽可能地规范化数据，因此请使用“地址”表。

您可以使用视图对数据进行反规范化，这些视图使用索引，应该提供了一种通过简单的引用访问数据的方法，同时完全保持底层结构规范化。

连接数量不应是主要问题，基于索引的连接不会带来太多开销。

- dougajmcdonald

Answer 8

拆分地址表是可以的。

但是，您必须避免让多个行引用同一地址而没有适当的系统来管理选项，以便用户决定是否以及如何更改地址并拆分新地址更改的行。例如，您的账单和收货地址相同。然后用户说他们的地址正在更改。首先，旧订单可能需要保留其收货地址，因此您不能在原地更改它。但是用户还可能需要说这个我要更改的地址只会更改收货地址。

- Cade Roux

---

非常好的观点。我考虑处理这个问题的方式是不允许多行引用相同的ID，并为每个新地址在Addresses表中创建一个新条目，即使它之前已经存在。这仍然可以通过UNION或SELECT DISTINCT找到唯一的地址。这样做可以吗？ - Vince Fedorchak

现在我在思考，也许另一个解决方案是向地址表添加一个新列，并称其为ChangedTo，如果原始地址被修改，则最终将引用Address表中的新记录。这将启用一个轨迹，显示地址的每次更新回到原始状态（其中ChangedTo为NULL）。 - Vince Fedorchak

@VinceFedorchak 我不会将审计建立在这个设计里面，而是使用像AutoAudit（http://autoaudit.codeplex.com/）这样的通用审计方法。至于唯一地址，它是否相关取决于您的报告要求，以及您为什么要寻找唯一地址等等。我认为地址是共享一个公共设计的东西，可以简化数据库并标准化一个组件，即使它在可能相当不同的角色中使用。 - Cade Roux

Answer 9

我更喜欢使用一个包含对人员/企业表的FK引用、对地址表的FK引用以及通常对角色表（家庭、办公室等）的FK引用的XREF表来划分实际地址类型。我还包括一个活动标志，以便我可以选择忽略旧地址，同时保留维护地址历史记录的能力。

这种方法允许我为每个主要实体维护多个不同类型的地址。

- Steve S