title: Storing a tree like structure in dynamodb
body: <p>I've a tree like structure </p>
  
  <pre><code>                (T)
            M1   M2  M3  M4
       M1L1 M1L2 M2L1 M3L1  M4L
  </code></pre>
  
  <p>where T is the top Level, M1, M2, M3, M4 are the children of T and M1L1, M1L2 are the children of M1 and so on. The max height of the tree will be 3. The max number of nodes which can be there is 50k. I would like to store it in a database. I'm hoping to store in DynamoDB as all my infrastructure right now is in DynamoDB and would like to if possible keep it in DynamoDB itself.</p>
  
  <p>I will need to perform the following type of queries:</p>
  
  <p>1 - Given a m1L1 return all same level childs (m1l1, m1l2)</p>
  
  <p>2 - Given a m1L1 id return TopLevel(T) and M1</p>
  
  <p>3 - Given a T return all Ms</p>
  
  <p>4 - given a T return all Ms and lowerLevel along with the relationship</p>
  
  <p>5 - given a M1 return all same level (m1, m2, m3)</p>
  
  <p>6 - given a M1 return all children
  7 - given a M1 return top level</p>
  
  <p>I was thinking of the following database schema:</p>
  
  <pre><code>Primary Key (id of the node)  Children             Parent         Sibling 
  T                              M1, M2,M3,M4         null          null
  M1                              M1L1,M1L2            T           M2, M3, M4
  M2                                  M2L1             T          M1, M3, M4
  M1L1                                  null           M1         M1L2
  </code></pre>
  
  <p>I was thinking of using AppendSet( <a href="https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.UpdateExpressions.html" rel="nofollow noreferrer">https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.UpdateExpressions.html</a>) for inserting into Children/Parent/Sibling relationship. </p>
  
  <p>With this approach, most of my queries will be possible, though I might need to call DDB twice for ex: if I want to get list of all children given a T, i.e for T get M1, M2, M3, M4. Then do Batch get for M1, M2, M3, M4.</p>
  
  <p>However the problem with this approach is my Puts. When I'm inserting into the Database I will have to update in multiple rows. Also I am not using any indexes which probably might not be great. Am I approaching this in a wrong way?</p>