database-design - 如何在任何 SQL 数据库中最好地设计地址位置？

Question

概述

我正在为加利福尼亚开发一些紧急服务报告和 map 应用程序(有点奇怪，考虑到那里的火灾，现在......)。我们需要为内部政府单位绘制人口统计和紧急数据。

我们拥有的是加利福尼亚的所有街道、城市和社区。每个社区也有它相关的 shapefile(定义它的边界的纬度)。这是美国人口普查局(所有公共(public)领域的东西)网站给我们的。

问题

我不确定如何最好地设计数据库表。我们还没有被告知我们需要使用哪种类型的数据库......所以如果有帮助，我们愿意接受建议。我们有使用 MS SQL 2005 和 2008(以及 08 年的空间 Material )的经验。

我们可以有以下合法数据。

街道，城市，州

城市，州

邻里，州

状态

State 之所以是合法地点，是因为我们被告知这可能会出售给其他州，所以我们现在需要为此做好计划。

所以，一开始，我是这么想的……

LocationId INTEGER PK 身份

街道 NVARCHAR(100)

邻域 NVARCHAR(100)

城市 NVARCHAR(100)

状态 NVARCHAR(100)

纬度 VARCHAR(15)

经度 VARCHAR(15)

形状文件

顺便说一句，这些都不是可以为空的。但不久之后，我认为在字段中放置这么多“加利福尼亚”文本或“圣地亚哥”文本是一种浪费。因此，我通过使 Neighborhood、City 和 State 字段成为他们自己的新表的外键(例如查找)来更改表以更加规范化......并且这两个字段现在是 NULLABLE。

所以..一切正常。除非我尝试对它们执行一些 Sql 语句。由于 NULLABLE FK，进行所有这些外部连接查询是一场噩梦 :(

让主表、子查找表(例如社区、城市和州)通过 ID 链接然后将所有这些放在 View 中怎么样？请记住，NeighborhoodID 和 CitiyID 将是 NULLABLE.. ???

我只是想看看人们对此的想法以及他们提出建议的原因。我真的很担心和困惑，但渴望学习。

请帮忙!

编辑 1:我需要坚持使用 RDBMS 数据库。

编辑 2:我正在考虑使用一个带有约束的单表(去规范化)以保持字段的总和 unqiue 或在主表上具有可为空 FK 的多表(例如位置(主表)、社区、城市, 状态...规范化的数据库模式)。

编辑 3:将城市添加到示例中，第二个列表。

编辑 4:添加了 View 问题。

Answer 1

举个例子:

街道，城市，州

城市，州

邻里，州

状态

首先回到基本原则，以上所有都是不同的地理空间实体，因此您的地址由一个名称和一个或多个地理空间说明符组成。这告诉我们，我们真的应该将它们存储在一个表中。这里的关键是更抽象地考虑数据，

因此，您的地址表需要与另一个名为 address_entities 的表建立一对多关系，如下所示:

整数 ID

varchar() 名称

varchar() 类型

int parentID

地理位置。

int parentID

这意味着您显然需要一个表来将地址链接到上面的地址实体表。现在，每个地理空间实体本质上都是分层的，虽然它使 SQL 更难，但我个人尽量避免自引用表，有时它是一个很好的解决方案，这就是其中之一。

好处是巨大的，即使它使代码变得更难，但从长远来看还是值得的。

此外，即使不是即时要求，从全局考虑，并非世界上所有地址都有街道或州，例如，在法国，有效地址可能是

- la Maison des Fou
- 24500 Eymet

因此，在设计模式时请记住这一点。