'How to store tree structure in sql?

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;
pythonsqldatabasesqlitedatabase-design


Solution 1:[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

Solution 2:[2]

Yes, the conventional way to represent a tree in a relational database is just as you did in your example: let each node in the tree have an ID, and a parent_ID. Create indexes on both for rapid retrieval.

The problem with trees in RDBs is not creating them, it's retrieving them efficiently. There's no query in standard SQL that will retrieve an entire tree or sub-tree. You have to write a loop.

Solution 3:[3]

Since the data structure doesn't have a natural representation in relational databases, there are several ways to store them depending on the use case and the database.

In this presentation, from slide 48, there are two methods described.

My favourite one is materialized path which is very simple and capable.

Hope it helps.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Jay
Solution 3

Related Questions