我应该使用哪种分层模型？邻接、嵌套还是枚举？

我有一个表，其中包含世界上所有地理位置及其关系的位置。

这是一个显示层次结构的示例。您将看到数据实际上存储为所有三个

• 枚举路径
• 邻接表
• 嵌套集

数据显然也永远不会改变。以下是英国布莱顿地点的直系祖先的示例，该地点的 woeid 为 13911。

Table: geoplanet_places (Has 5.6million rows) Large Image: http://chrisacky.com/ancestors.jpg http://chrisacky.com/ancestors.jpg

I then have another table called entities. This table stores my items which I would like to map to a geographical location. I store some basic information but most important I store the woeid which is a foreign key from geoplanet_places.

最终将entities表将包含数千个实体。我想要一种能够返回包含实体的所有节点的完整树的方法。

我计划创建一些东西来促进根据实体的地理位置过滤和搜索实体，并能够发现在该特定节点上可以找到多少实体。

因此，如果我的模型中只有一个实体entities表，我可能有这样的东西

'地球 (1)

英国 (1)

英格兰 (1)

东萨塞克斯 (1)

布莱顿霍夫市 (1)

布莱顿 (1)`

然后假设我有另一个位于德文郡的实体，那么它会显示如下内容：

地球 (2)

英国 (2)

英格兰 (2)

德文郡 (1)

东萨塞克斯 (1) ... ETC

表示每个地理位置“内部”有多少实体的（计数）不需要是实时的。我可以忍受每小时生成我的对象并缓存它。

目的是能够创建一个界面，该界面可能一开始仅显示拥有实体的国家。

So like

Argentina (1021), Chile (291), ..., United States (32,103), United Kingdom (12,338)

然后，用户将单击一个位置，例如英国，然后将获得所有直接子节点，这些子节点是英国的后代并且其中有一个实体。

如果英国有 32 个县，但当您向下钻取时最终只有 23 个县存储了实体，那么我不想显示其他 9 个县。它只是位置。

This site aptly demonstrates the functionality that I wish to achieve: http://www.homeaway.com/vacation-rentals/europe/r5 http://www.homeaway.com/vacation-rentals/europe/r5

您建议我如何管理这样的数据结构？

我正在使用的东西。

• PHP
• MySQL
• Solr

我计划尽可能快地进行深入研究。我想创建一个无缝搜索的 AJAX 界面。

我也有兴趣知道您建议在哪些列上建立索引。

通常，层次结构中存在三种查询会导致问题：

1. 返回所有祖先
2. 返回所有后代
3. 返回所有孩子（直系后代）。

这是一个小表，显示了不同方法的性能MySQL:

                        Ancestors  Descendants  Children        Maintainability InnoDB
Adjacency list          Good       Decent       Excellent       Easy            Yes
Nested sets (classic)   Poor       Excellent    Poor/Excellent  Very hard       Yes
Nested sets (spatial)   Excellent  Very good    Poor/Excellent  Very hard       No
Materialized path       Excellent  Very good    Poor/Excellent  Hard            Yes

In children, poor/excellent意味着答案取决于您是否将该方法与邻接表混合，即。 e.存储的parentID在每条记录中。

对于您的任务，您需要所有三个查询：

1. 所有祖先展示地球/英国/德文郡的事情
2. 所有儿童展示“欧洲目的地”（项目）
3. 所有后代都将展示“欧洲目的地”（伯爵）

我会选择物质化的道路，因为这种等级制度很少改变（只有在战争、叛乱等情况下）。

创建一个名为的 varchar 列path，对其进行索引并用如下值填充它：

1:234:6345:45454:

其中数字是适当父母的主键，按正确的顺序（1对于欧洲来说，234对于英国等）

您还需要一个名为levels防止数字1 to 20（或任何您想要的最大嵌套级别）。

选择所有祖先：

SELECT   pa.*
FROM     places p
JOIN     levels l
ON       SUBSTRING_INDEX(p.path, ':', l.level) <> p.path
JOIN     places pa
ON       pa.path = CONCAT(SUBSTRING_INDEX(p.path, ':', l.level), ':') 
WHERE    p.id = @id_of_place_in_devon

要选择所有子项以及其中的位置计数：

SELECT  pc.*, COUNT(pp.id)
FROM    places p
JOIN    places pc
ON      pc.parentId = p.id
JOIN    places pp
ON      pp.path BETWEEN pc.path AND CONCAT(pc.path, ':')
        AND pp.id NOT IN
        (
        SELECT  parentId
        FROM    places
        )
WHERE   p.id = @id_of_europe
GROUP BY
        pc.id