>有人指出我的数据结构架构很糟糕。
任务
我有一个locations表，用于存储位置的name。然后我有一个tags表，用于存储有关这些locations的信息。locations有一个层次结构，我想用它来获取所有tags。
例
地点：
USA <- California <- San Francisco <- Mission St
标签：
USA: English
California: Sunny
California: West coast
San Francisco: Sea side
Mission St: Cable car station
如果有人要求提供有关Mission St的信息，我想提供它的所有tags及其祖先(["English", "Sunny", "West coast", "Sea side", "Cable car station"]。如果我要求California的所有tags答案将是["English", "Sunny", "West coast"]。
我正在寻找最佳的读取性能！我不在乎写入性能。此数据不会经常更改。而且我也不在乎桌子的大小。如果我需要更多或更大的表来更快地解决这个问题，那就这样吧。
目录
所以目前我正在考虑设置这些表：
地点
id | name
---|--------------
1 | USA
2 | California
3 | San Francisco
4 | Mission St
标签
id | location_id | name
---|-------------|------------------
1 | 1 | English
2 | 2 | Sunny
3 | 2 | West coast
4 | 3 | Sea side
5 | 4 | Cable car station
祖先
我添加了一个position字段来存储层次结构。
| id | location_id | ancestor_id | position |
|----|-------------|-------------|----------|
| 1 | 2 | 1 | 1 |
| 2 | 3 | 2 | 1 |
| 3 | 3 | 1 | 2 |
| 4 | 4 | 3 | 1 |
| 5 | 4 | 2 | 2 |
| 6 | 4 | 1 | 3 |
问题
这是解决问题的好解决方案还是有更好的解决方案？我想尽快选择任何给定位置的所有标签，包括其祖先的所有标签。我正在使用PostgreSQL数据库，但我认为这是一个纯粹的SQL架构问题。
您的问题似乎包括两个挑战。最有趣的是"如何在关系数据库中存储层次结构"。对此有很多答案 - 您提出的答案是最常见的。
还有一种称为"嵌套集"的替代方法，它的读取速度更快(在您的示例中，查找特定层次结构中的所有位置将是"在 x 和 y 之间"。
Postgres对hierachies有专门的支持;我认为这也将提供出色的性能。
问题的第二部分是"在我的层次结构中给定一个路径，检索所有匹配的标签"。最简单的选择是按照您的建议加入标签表。
最后一个方面是"你应该去规范化/预先计算吗"。我通常建议构建和优化"规范化"解决方案，并且仅在需要时进行非规范化。
如果您想为特定位置提供所有标签，那么我建议您复制数据并将标签存储在每个位置的一行上的标签数组中。
你说地点变化不大。 因此，当任何基础数据发生更改时，我只需批量创建整个表。
就地修改数据是相当成问题的。 单个更新最终可能会影响无数不同的行 - 考虑在美国的标签更改。 重新计算整个表将更有效率。
如果您需要搜索标签并返回它们，那么我会选择具有两个重要列的更传统的表结构，location和tag. 然后，您可以在(location)和(tag)上都有索引，以便于在任一方向上进行搜索。
如果写入性能不是很关键，我会选择数据库的非规范化。这意味着您将上述结构用于写入操作，如果您害怕触发器，则通过触发器或某些异步作业填充读取操作的表。然后读取性能是最佳的，但您必须在写入逻辑上投入更多资金。
使用上述结构进行读取操作确实不是一个明智的解决方案，因为您不知道树可以有多深。