I am looking for a way to store and handle unlimited level of hierarchy for various organisations/entities stored in my DB. For example, instead of having just one parent and one child organisation (e.g. 2 levels of hierarchy) and just one-to-many relationship as allowed by self-join (e.g. having another column called parent referring to the IDs of the same table), I want to be able to have as many levels of hierarchy as possible and as many connections as possible.

Supposing I have an organisation table such as the following:

I am considering the following solution; for each table that I need this I can add another table named originalTable_hierarchy which refers to the organisation table in both columns and make it look like this:

From this table I can tell that 1 is parent to 2, 2 is parent to 4, 3 is parent to 1, 3 is also parent to 2, 2 is also parent to 3.

The restrictions I can think of are not to have the same ParentID and ChildID (e.g. a tuple like (3,3)) and not to have a record that puts them into the opposite order (e.g. if I have the (2,3) tuple, I can't also have (3,2))

Is this the correct solution for multiple organisations and suborganisations I might have later on? Users will have to navigate through them easily back and forth. If users decide to split one organisation into many, does this solution suffice? What else should I consider (extra or missing perks) when doing this instead of a traditional self-join or a certain number of tables for certain levels of hierarchy (e.g. organisaion table and suborganisation table)? Also, can you impose restrictions on certain records, so that no more childs of a certain parent can be created? Or to report on all the childs of an original parent?

Please feel free to also instruct on where to read more about this. Any relevant resources are welcome.

CodePudding user response：

You only need a single table as having just one parent and one child allows an unlimited (theoretical anyway) levels in the hierarchy. You do this by reversing the relationship so that the Child references the Parent. (Your table has the Parent referencing the Child). This results in allowing a child, at any level, also being a parent. This can be chained as far as needed.

create table organization ( id         integer primary key
                          , name       text
                          , parent_id  integer references organization(id) 
                          , constraint parent_not_self check (parent_id <> id) 
                          ) ;
                         
create unique index organization_not__mirrored  
       on organization( least(id,parent_id), greatest(id,parent_id) );

The check constraint enforces you first restriction and the unique index the second.
The following query shows the full hierarchy, along with the full path and the level.

with recursive hier(parent_id, child_id, path, level) as 
     ( select id, parent_id, id::text, 1 
         from organization 
        where parent_id is null 
       union all 
       select o.id, o.parent_id,h.path || '->' ||o.id::text,h.level 1 
         from organization o 
         join hier        h
           on (o.parent_id = h.parent_id)
     ) 
select * from hier;

See demo here.