title: Postgres - multilevel parent child relation data
body: <p>We have a parent child relation as below. </p>
  
  <p><a href="https://i.stack.imgur.com/T0lUA.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/T0lUA.png" alt="enter image description here"></a></p>
  
  <p>Script to generate tables as below. </p>
  
  <pre><code>create table dependency ( packageid int, dependant_packageid int);
  insert into dependency values (2,1);
  insert into dependency values (3,1);
  insert into dependency values (4,1);
  insert into dependency values (5,2);
  insert into dependency values (6,3);
  insert into dependency values (7,4);
  insert into dependency values (7,5);
  insert into dependency values (8,5);
  insert into dependency values (8,3);
  insert into dependency values (4,5);
  insert into dependency values (6,4);
  insert into dependency values (5,3);
  </code></pre>
  
  <p>We wanted to get data based on below mentioned quer. </p>
  
  <ol>
  <li>for the given package get the possible dependant hierarchy</li>
  </ol>
  
  <pre><code>
  Ex:
  packageid : 6
  Result should be: [(3,1),(4,1),(4,5,2,1),(4,5,3,1)]
  packageid : 7
  Result should be: [(4,1),(4,5,2,1),(4,5,3,1)]
  packageid : 8
  Result should be: [(5,2,1),(5,3,1),(3,1)]
  </code></pre>
  
  <ol start="2">
  <li>for the given package get the list of parent packages </li>
  </ol>
  
  <pre><code>
  Ex:
  1 - 2,3,4
  2 - 5
  3 - 6,8,5
  4 - 7,6
  5 - 7,8,4
  </code></pre>
  
  <ol start="3">
  <li>If we need to maintain this kind of parent child relation (many to many), what should be ideal schema structure (Keeping in mind performance) ?</li>
  </ol>
  
  <p>Appreciate any help....happy coding....:)</p>