如何在数据库中最好地表示地址

Question

可能的重复问题：
是否有适用于世界所有地址的通用街道地址数据库设计？
在数据库中存储国际地址的“最佳”方法是什么？
在数据库中进行一致和全面的地址存储的最佳实践

我目前有四个表，客户、联系人、设施和客户端。

这些表中每个都有以下字段： AddressLine1、AddressLine2、City、StateOrProvince、PostalCode。

我想把地址移到一个单独的表中，并能够指定地址类型（账单、发货、主要等）。

我的解决方案如下：

1. 从Customers、contacts、Facilities和Clients中删除AddressLine1、AddressLine2、City、StateOrProvince和PostalCode。
2. 创建Addresses表，包括字段AddressID(PK)、AddressLine1、AddressLine2、City、StateOrProvince、PostalCode、LastUpdateUser和LastUpdateTime。
3. 创建AddressTypes表，包括字段AddressTypeID、AddressTypeName、AddressTypeDescription、AddressTypeActive、LastUpdateUser和LastUpdateTime。
4. 创建CustomerAddresses表，包括字段CustomerID、AddressID、AddressTypeID、CustomerAddressActive、LastUpdateUser和LastUpdateTime。
5. 创建ClientAddresses表，包括字段ClientID、AddressID、AddressTypeID、ClientAddressActive、LastUpdateUser和LastUpdateTime。
6. 创建ContactAddresses表，包括字段ContactID、AddressID、AddressTypeID、ContactAddressActive、LastUpdateUser和LastUpdateTime。
7. 创建FacilityAddresses表，包括字段FacilityID、AddressID、AddressTypeID、FacilityAddressActive、LastUpdateUser和LastUpdateTime。

我正在寻求指导，确定是否有比我设计的更好的解决方案。大家怎么看？

编辑：目前我不关心美国以外的任何事情，也不关心如何存储街道地址，即街道号码与整个街道地址的区别。我关心的是从数据库设计和表结构的角度考虑。

- Michael Wheeler

---

请参阅以下链接： https://dev59.com/AXVD5IYBdhLWcg3wTZxm https://dev59.com/onVC5IYBdhLWcg3w-mZO https://dev59.com/C3NA5IYBdhLWcg3wgeSk https://dev59.com/xHVC5IYBdhLWcg3wZwFT 等。 - Welbog

拥有多个地址表有什么问题？ - NoChance

Answer 1

我曾经工作的一个DBA告诉过我这个好方法，在我们这里运行良好（前两步与您的解决方案相同）：

1. 从Customers、Contacts、Facilities和Clients表中删除AddressLine1、AddressLine2、City、StateOrProvince和PostalCode字段。
2. 创建AddressTypes表，包括AddressTypeID、AddressTypeName、AddressTypeDescription、AddressTypeActive、LastUpdateUser和LastUpdateTime字段。
3. 创建Addresses表，包括AddressID(PK)、AddressTypeID(FK)、AddressLine1、AddressLine2、City、StateOrProvince、PostalCode、LastUpdateUser、LastUpdateTime、CustomerID(FK)、ClientID(FK)、ContactID(FK)和FacilityID(FK)字段。
4. 在地址表上设置约束条件，以便每次只能有一个CustomerID、ClientID、ContactID或FacilityID外键为非NULL。

这样，您就可以将所有地址放入一个表中，可以引用任何需要的记录，您的参照完整性得到保证，而且无需遍历中间表。

缺点是，如果要向新类对象添加地址（例如Employee表），则必须在Addresses表中添加EmployeeID列，但这很容易。

- Edward Robertson

---

添加这么多主键的意义何在？反过来做可以更轻松地建立关系，使得客户（只是一个示例）可以拥有多个地址。 - Cem Kalyoncu

客户可以有多个地址。地址表可以有多行引用相同的CustomerID。但是同一个地址不能同时引用客户和联系人。 - Edward Robertson

如果一个地址引用了两个不同的客户，因为他们住在同一所房子里，该怎么办？ - nonsensecreativity

@nonsensecreativity，您可以有两个地址具有相同的AddressLine1、City、StateOProvince和PostalCode。 - danilo

Answer 2

我们数据库中还有一件你可能需要考虑的事情，那就是在地址表中设置一个对应标志，并使用触发器来强制只能将一个地址作为通信地址。我们向数据库中的人发送了很多邮件，知道每个人的三个地址中哪一个是发送邮件时需要使用的地址是非常宝贵的。这也使得在查询时更容易抓取每个人的一个地址，以避免某些报告中出现一个人对应多条记录。

- HLGEM

Answer 3

我会考虑使用一个单独的AddressLink（名称？）表

LinkTypeID (Customer,Client,Contact,Facility) -- needs additional TypeID table
ID (CustomerID,ClientID...)
AddressID
AddressTypeID
AddressActive
LastUpdateUser
LastUpdateTime

添加新地址链接类型意味着添加一个新的LinkTypeID，而不需要新的[TypeID]Addresses表，无需修改任何查询，如果您正在寻找地址的所有用途（用于删除等），只需要查看一个地方。

这与我们的做法非常相似。

哦，我们在我们的地址（等效）表中有一个AddressLine3，用于一些奇怪的异常情况。

- DaveE

---

如何在这个结构上强制执行外键约束？或者你不打算这样做吗？ - APC

插入/修改触发器验证ID列相对于LinkTypeID的有效性。 LinkTypeID、AddressID和AddressTypeID列上的FK，以及父表上的“删除限制”。我认为这是通过声明完成的，但也可能是触发器。(MS SQL Server 2005) - DaveE

Answer 4

我还想再补充一件事，为了简单起见，请创建视图将地址信息展开到您的表中，否则您可能会因为设计数据库的方式而讨厌自己。

- Cem Kalyoncu