您是否为包含子节点的多个父节点的分层数据建模扩展了嵌套集？你有什么经历？

I am looking to use this concept in one of my upcoming project.

我希望在我即将开展的项目中使用这个概念。

More info: Managing Hierarchical Data in MySQL.

更多信息:在MySQL中管理分层数据。

Please share your experiences good or bad with examples.

请通过示例分享您的经历好坏。

I am adding more information to make it more broad:

我正在添加更多信息以使其更广泛:

I have child items that can have more than one parent (example: a user can belong to city and also a group called UserDefinedRegion), which the typical hierarchical models do not support, whether it is adjacency list or nested sets.

我有子项可以有多个父项(例如:用户可以属于city,也有一个名为UserDefinedRegion的组),典型的层次模型不支持,无论是邻接列表还是嵌套集。

I am pasting the use case here for clarity:

为清楚起见,我在这里粘贴用例:

Background: Currently the system has a fixed hierarchy in place which is State->County->City->User

背景:目前系统具有固定的层次结构,即State-> County-> City-> User

Sales Manager logs in to the system and creates a new group which can by at the same level as City, or County.

销售经理登录系统并创建一个新组,该组可以与City或County处于同一级别。
Sales Manager logs in to the system and creates a new group which can be in between State and county or County and City.

销售经理登录系统并创建一个新组,该组可以位于州和县或县和市之间。
Once the sales manager creates the groups, he should be able to view all the necessary reports rolled up the next day in his dashboard.

销售经理创建组后,他应该能够在他的仪表板中查看第二天汇总的所有必要报告。

As you can see, second point can easily be accomplished by nested sets, but not the first point, which will introduce new parents nodes for the same child node.

如您所见,第二点可以通过嵌套集轻松完成,但不是第一点,它将为同一子节点引入新的父节点。

So far the following solutions were proposed by * users:

到目前为止,*用户提出了以下解决方案:

Network Node structure supported by Network Database.

网络数据库支持的网络节点结构。

Directed Acyclic graphs.

有向无环图。

I am definitely looking for a RDBMS solution. It looks like not many have encountered multiple parent nodes in heirarchical data models in real life.

我肯定在寻找一个RDBMS解决方案。在现实生活中,似乎没有多少人在层次数据模型中遇到过多个父节点。

2 个解决方案

#1

As you will probably be using stored procedures for some operations, make sure that they really perform well enough for your needs! This could be a problem if you use MySQL, in my experience.

由于您可能正在使用存储过程进行某些操作,请确保它们确实能够满足您的需求!根据我的经验,如果你使用MySQL,这可能是一个问题。

Regarding the new requirement (multiple parents): You are now into much more problematic stuff when using a RDBMS, depending on what kind of queries you need to run against the data. I compared the RDBMS approach to using a graph database on this wiki page. If you are only interested in the RDBMS approach, take a look at A Model to Represent Directed Acyclic Graphs (DAG) on SQL Databases.

关于新要求(多个父项):在使用RDBMS时,您现在遇到的问题更多,具体取决于您需要针对数据运行哪种查询。我将RDBMS方法与在此Wiki页面上使用图形数据库进行了比较。如果您只对RDBMS方法感兴趣,请查看SQL模型上的A Model to Represent Directed Acyclic Graphs(DAG)。

#2

Your requirement for multiple parents immediately violates the fundamental nature of nested sets, as pointed out in your referenced article, so I'd say you're headed for trouble to start with. Since you'll be using a relational database, which (using it's core capabilties) will handle everything you've described so far, I think just working in that conceptual framework and polishing your skills will provide everything you need, without adding additional abstractions that (at least in this case) don't add any value.

您对多个父母的要求会立即违反嵌套集的基本性质,正如您在参考文章中指出的那样,所以我会说您开始时遇到麻烦了。由于你将使用一个关系数据库,它(使用它的核心功能)将处理你到目前为止所描述的所有内容,我认为只是在这个概念框架中工作并抛光你的技能将提供你需要的一切,而不添加额外的抽象, (至少在这种情况下)不添加任何值。

If you still want to go there, I'd call this a networked node structure. Here's a reference.

如果你还想去那里,我称之为网络节点结构。这是一个参考。

#1