如何为这些需求设计SQL表格

Question

我有数据 CITY 和 AREA。

1. 每个 CITY 都有多个 AREAS
2. 每个 AREA 都有多个 AREAS（这里没有限制，用户可以动态地在子级 AREA 下添加 AREAS，例如 AREA->AREA->Area->AREA....->AREA）

那么，如何设计表结构以满足这些要求？

提前感谢您的帮助。

- Jigar Parmar

---

每个区域都有多个子区域、多个父区域还是两者都有？ - Tudor Constantin

Answer 1

城市表

• 城市ID（主键）
• 城市名称

城市区域表

• 城市ID（复合（双列）主键）
• 区域ID（如果您希望一个区域只能被一个城市拥有，请为此列添加唯一索引）

区域表

• 区域ID（主键）
• 区域名称

区域与区域映射表

• 区域ID（所有者区域）（复合（双列）主键）
• 区域ID

规则

• 为了将一个区域映射到另一个区域，必须在区域表中为每个区域创建一条记录。
• 在区域与区域映射表中，您必须确定这些关系是双向还是单向。在我看来，这将是单向的。第一个区域ID是拥有第二个区域ID的区域。

- Brian Webster

---

在你的例子中，一个地区可以属于多个城市，也可以属于多个子地区。根据他所描述的情况，似乎只有一个城市可以有多个地区，并且一个地区可以有多个子地区。这些都是一对多的关系，而不是多对多的关系。 - Tudor Constantin

好的，Cita Areas表已经使用唯一索引进行了修复。然而，我认为我们不能排除一个区域可能被多个区域所拥有这种情况。例如：公园由街区拥有，街区又由社区拥有。虽然最好是有所有权链，但这个问题并不是很清晰。 - Brian Webster

注意，从技术上讲，如果一个区域只能被一个城市拥有，那么城市区域可能只有一个基于AreaID的PKey。然而，为了其他程序员的可理解性，我可能会保留这两列PKEY和唯一约束。 - Brian Webster

那张表是冗余的，没错。然而，就像我在同一张表上的复合主键一样，我相信以这种方式让其他程序员更容易理解数据库。话虽如此，你是完全正确的。 - Brian Webster

透视表（包含复合主键的透视表）用于m到n的关系 - 当有人使用它们来建模1到n的关系时会很混乱。 - Tudor Constantin

Answer 2

表AREA：

• id (PK)
• parent_area_id - 所属的区域，如果没有上级区域则可以为NULL (AREA表上的外键)
• city_id - 所属的城市，你可以通过业务逻辑来强制要求当parent_area_id被填写时city_id必须为空，反之亦然 (city表上的外键)
• 其他有用的列

表CITY：

• id (PK)
• 其他有用信息

你可能会对阅读在MySQL中管理分层数据感兴趣 - 这也适用于其他DB引擎

- Tudor Constantin

---

在提到嵌套集时加1（参见链接）。我没有尝试过它们，但在某些情况下它们看起来可能会快得多。这里是Joe Celko关于该主题的一篇文章。 - Alexander Malakhov

Answer 3

这将是一棵树（或可能是一个层次结构）。最常见的解决方案，您将在其他答案中看到，是使用邻接列表模型。然而，另一个需要考虑的“大”想法是嵌套集模型。关于此主题的一本有用的书是Joe Celko's Trees and hierarchies in SQL。

- onedaywhen

Answer 4

反身关系。

或者，如果您更喜欢嵌套集。

- Damir Sudarevic

Answer 5

对于 SQL Server 2008，最佳选择是层次结构数据类型。

这里有一个关于性能的链接。

- RC_Cleland