CODEWITHSUNDEEP
postgresqldatabase-designhierarchical-data
Rafael Fragoso
Rafael Fragoso

Reputation: 1309

How to store and query a database with tree structure

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:

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.

Upvotes: 2

Views: 840

Answers (1)

Joe Love
Joe Love

Reputation: 5962

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)

Usually the big boss would have a NULL boss_hr_id

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
)

Upvotes: 1

Related Questions