如何使用树形结构存储和查询数据库 [英] How to store and query a database with tree structure

问题描述

我是MLM网络的成员，也是一名开发人员.我的问题是关于构建具有无限级别的MLM软件的数据库结构.示例:

I'm a member of a MLM network and I'm also a developer. My question is regarding the database structure to build a MLM software with infinite levels. Example:

第1个人(6000人是他的人脉网络-但只有4个人与他直接链接)

Person 1 (6000 people is his network - but only 4 direct linked to him)

如何存储该数据并查询其网络产生了多少点?

How to store that data and query how many points does his network produce?

我可能会使用多对多关系，但是一旦我们拥有大量用户和庞大的网络，查询和遍历这些记录就会花费很多.

I could possibly do it use many-to-many relationship, but once we have a lot of users and a huge network, it costs a lot to query and loop through these records.

推荐答案

在任何数据库中，如果树"的每个成员都具有相同的属性，则最好使用自引用表，尤其是如果每个树都具有1和只有1个直接父母.

In any database, if each member of the "tree" has the same properties, it's best to use a self referencing table, especially if each tree has 1 and only 1 direct parent.

IE.

HR
------
ID
first_name
last_name
department_id
sal
boss_hr_id (referneces HR.ID)

通常，大老板会拥有NULL boss_hr_id

Usually the big boss would have a NULL boss_hr_id

要查询这样的结构，可以在postgres中使用CTE(带有递归"语句)

To query such a structure, in postgres, you can use CTEs ("with recursive" statement)

对于上表，这样的查询将起作用:

For table above, a query like this will work:

with recursive ret(id, first_name, last_name, dept_id,boss_hr_id) as
    (
      select * from hr
      where hr.id=**ID_OF_PERSON_YOU_ARE_QUERYING_STRUCTURE**
      union
      select hr.id, hr.first_name, hr.last_name,hr.dept_id,hr.boss_hr_id, lev+1 from hr
      inner join ret on ret.boss_hr_id=hr.hr_id
    ) 
    select * from ret
) 

这篇关于如何使用树形结构存储和查询数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！