The method of storing materialized paths in the database.

The main advantage of this method is access to the child nodes of any level in a single query to the database (albeit with an INNER JOIN).

Example:
necessary in the section / company / get to the list of news (item1, item2)

• / company / About company
  
  • / company / news / News
    
    • / company / news / item2 / Second news
    • / company / news / item1 / First news news
    
    
  • / company / history / History
  • / company / contacts / Contacts
  
  

Data is stored in tables with the following structure:
paths - tree with paths

id
parent_id # id (paths.id)
name # ,
path # (/company/, /company/news/item1/)
title #


path_have_childs - a table of links that stores a list of child nodes with levels relative to the parent node

parent_id # id (paths.id)
child_id # id (paths.id)
level #


')
Data - table with paths:

id	parent_id	name	path	title
one	0		/	the main
2	one	company	/ company /	About company
four	2	news	/ company / news /	news
five	four	item1	/ company / news / item1 /	News 1
6	four	item2	/ company / news / item2 /	News 2
3	one	catalog	/ catalog /	Catalog
7	3	category1	/ catalog / category1 /	Category 1
eight	7	category2	/ catalog / category1 / category2 /	Category 2

The additional table is the storage of the levels of the child node relative to the parent:

parent_id	child_id	level
one	2	one
one	eight	3
one	7	2
one	6	3
one	five	3
one	four	2
one	3	one
2	five	2
2	6	2
2	four	one
3	7	one
3	eight	2
four	6	one
four	five	one
7	eight	one

A query that generates an additional table for levels — for each node, all the child nodes are found, even those that are not directly children, and the nesting level is calculated relative to the current node:

INSERT INTO path_have_childs
SELECT
P.id AS parent_id,
C.id AS child_id,
(LENGTH(C. path ) - LENGTH(REPLACE(C. path , '/' , '' )))
- (LENGTH(P. path ) - LENGTH(REPLACE(P. path , '/' , '' )))
FROM paths AS P
INNER JOIN paths AS C ON (C. path LIKE CONCAT(P. path , '_%' ))


Select all child nodes of node X (parent_have_childs.parent_id) with level Y (parent_have_childs.level):

SELECT C. path
FROM path_have_childs AS PHC
INNER JOIN paths AS C ON (PHC.child_id = C.id)
WHERE
PHC. level = Y
AND PHC.parent_id = X


Constructive comments and criticism are welcome ...