Something related to hierarchical data.
I am implementing a message labeling system for a website that is similar to the gmail label system.
This is the summary ER.
A message can have one or more labels and each label can belong to more than one message.
My question is relative to the tab_category table.
With this id -> id_father approach I can have categories and subcategories practically infinite. Example:
id name father
1 catA 0
2 catAB 1
3 catAC 1
4 catABA 2
5 catACA 3
6 catB 0
7 catC 0
8 ... ...
This example may return:
- catA / catAB
- catA / catAC
- catA / catAB / catABA
- catA / catAC / catACA
- catB
- catC
- ...
My question is whether this approach is appropriate for this kind of scenario, thinking about performance, when we will have many categories and many sub-sub categories? If it is not appropriate what is the modern approach to this scenario?