title: database schema nested array
body: <p>I am working on a project which contains lots of nested data. Think of a comments sections of a website where people can comment on previous comments. The difference for me being a comment can be one of a number of different types. The json representation would be something like:</p>
  <pre><code>{
  &quot;children&quot;: [{
          &quot;name&quot;: &quot;comment&quot;,
          &quot;children&quot;: [{
                  &quot;type&quot;: &quot;comment&quot;,
                  &quot;children&quot;: []
              },
              {
                  &quot;type&quot;: &quot;note&quot;,
                  &quot;children&quot;: [{
                      &quot;type&quot;: &quot;comment&quot;,
                      &quot;children&quot;: [{
                          &quot;type&quot;: &quot;note&quot;,
                          &quot;children&quot;: []
                      }, {
                          &quot;type&quot;: &quot;comment&quot;,
                          &quot;children&quot;: []
                      }]
                  }]
              }
          ]
      },
      {
          &quot;type&quot;: &quot;comment&quot;,
          &quot;children&quot;: []
      }
  ]
  }
  </code></pre>
  <p>Visually it would look something like: <a href="https://i.stack.imgur.com/qQ8xT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qQ8xT.png" alt="enter image description here" /></a></p>
  <p>I'm struggling to figure out how to represent this in the database. Currently I'm thinking of using postgres but am happy to use the most suitable db for this. I know I could have a comments table and a notes table. Then each comment and note would have a parent_id and parent_type and order_number which relates it back to the parent. But it feels to me like there would be a better way. If anyone knows of one I'd love to hear it. Maybe it's the case that a relation db is not best suited for this?</p>