CODEWITHSUNDEEP
sqlpostgresqldatabase-designtree
J3Y
J3Y

Reputation: 1853

Modeling a fixed-depth tree in SQL

I'm creating a SQL schema to represent a tree-like hierarchy, but I know for a fact that this tree will never be more than N-levels deep (where N is known at design of the application, most likely to be about 4 or 5).

If the depth of this tree is fixed, is it better to design the application by modeling each level of the tree as a table? Or would it be better to approach it with arbitrary depth in mind (e.g. adjacency lists or nested sets)?

I'm asking purely from a queryability perspective, e.g. reporting on various levels of the tree.

Upvotes: 2

Views: 320

Answers (1)

klin
klin

Reputation: 121604

Use a single table with a well known hierarchical structure (node_id, parent_id) and an additional column depth. The column is theoretically redundant but can be very helpful in querying a specific tree level. Of course, such a structure makes it easy to walk the tree from top to bottom (or vice versa) using recursive queries.

create table a_tree(
    node_id int primary key, 
    parent_id int, 
    depth int
--  other columns
);

As a bonus, you can find many ready-to-use solutions for this type of tables on SO.

Upvotes: 1

Related Questions