How to store tree structure in sql?

Question

Here is my schema using sqlite, I am not sure if this is a good way to create tree structure in sql as I have to traverse many time to obtain the entire tree, as oppose to extracting the entire tree base on top comment and construct the tree in python. Can someone give me some suggestions.

BEGIN;
CREATE TABLE "tree_comment" 
    ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
     "text" text NOT NULL, 
     "parent_id" integer NULL REFERENCES "tree_comment" ("id"));
CREATE INDEX "tree_comment_6be37982" ON "tree_comment" ("parent_id");

COMMIT;

Answer 1

Your example is the correct way to represent hierarchical data in a relational database. You should use Recursive Common Table Expressions (R CTE) to query the table.

In the past, you would have had to have used Nested Set or Materialized Path, but R CTE was purpose-built to fix their flaws, so use it.

SQLite supports Recursive CTE, and it's part of the SQL:1999 standard.

Here is an example query:

https://database-patterns.blogspot.com/2014/02/trees-paths-recursive-cte-postgresql.html