Saving an unbalanced tree in the database

Question

Saving an unbalanced tree in the database

I am working on a project where I need to store a tree structure in a database, I have dealt with the same scenario in the past and I used a specific solution (explained below).

I know there is no BEST solution, and usually the best solution is the one with the main benefits, but undoubtedly the worst one, and I would not want to use that ...

As I said, I need:

keep an unbalanced tree structure
any node can have an "unlimited" number of children
have the ability to easily get all the children (recursively) of one node
have the ability to easily "rearrange" the tree structure.

The solution I have used in the past has been to use a primary key VARCHAR(X * Y)

, where:

X is the "hypothetical" maximum possible level
Y is the character number of the "hypothetical" maximum number of direct children of one node ...

i.e.

If I have: - for a maximum of 3 levels then X = 3
- a maximum of 20 direct children per node, Y = 2 (20 has two characters - up to 99 children can be stored)

The PRIMARY KEY column will be created as VARCHAR(6)

The identifier is a compound combination PARENT ID

+NODE_ID

NODE The identifier is an incremental numeric value padded with zeros on the left side.

node at the first level will be saved as:
[01,02,03,04,...,99]

second level nodes will be saved as:
[0101, 0102, 0103, ..., 0201, 0202, 0203, ... , 9901, 9999]

3rd level nodes will be saved as:
[010101, 010102, 010103, ..., 020101, 020102, 020301, ... , 990101, 999999]

etc.

PROs:

Easy to restore wood
It's super easy to get a list of children of one specific node (i.e. select ... where id like '0101%'

)
Only one column for ID and parent reference.

CONs:

It is imperative to define the MAXIMUM number of CHILDREN / LEVELS
Values X

and Y

are large, the key id

will be too long.
VARCHAR type as primary key
Changing the tree structure (moving one node from one parent to another) will be difficult (if not impossible) and consuming due to the need to recreate all ids for a node and all of its children.

Traversing the order tree

I have done some research and the best solution I found in my main problems (getting all children of one node, etc.) is to use the solution Preorder Tree Traversal

(for brevity, I will post a link where the solution is explained: HERE )

Even though this solution is better in almost every aspect, it has a HUGE drawback, any change in the structure (add / remove / change the parent node) is necessary to WRITE all left / right indices, and this operation is time and resource.