I'm working on a hobby project which is a nested todo list app.
Now I'm researching the best method to persist my todos using PostgreSQL. I've extensively read through the pros and cons of each solution here (What are the options for storing hierarchical data in a relational database?), and am about to settle on recursive WITH CTE's, as writes and updates take priority over reads in my app, but I thought I should ask.. is there a reason why storing child ids on the parent is not a popular solution for relational DB's?
It's most similar to the Adjacency List method, but with less recursion, and you get ordering (by maintaining order in your
is there a reason why storing child ids on the parent is not a popular solution for relational DB's
This idea is very popular, with two caveats, and under certain modeling scenario:
In effect, DB's model of storing child IDs on the parent side stores IDs on nobody's side, because you can retrieve IDs of parents of a child with the same ease as retrieving children of a parent. That is why this approach is used when you model many-to-many relationship.
Note: Since enforcing one parent per child policy when you store parent ID on the child happens automatically, you can easily model your tree like that, while converting in-memory representation to an adjacency list for the tree.