Hierarchical data. What would be the most appropriate ER Diagram to implement a Labels system like Gmail?

Asked

Viewed 25 times

0

Something related to hierarchical data.

I am implementing a Labels system for messaging a website that is similar to the gmail Labels system. This is the short ER. inserir a descrição da imagem aqui

A message may have one or more Abels and each label may belong to more than one message.

My question is about the table tab_category.

With that approach id -> id_father i can have categories and subcategories virtually infinitely. 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 can return:

  • cata/catAB
  • cata/catAC
  • cata/catAB/catABA
  • cata/catAC/Cataca
  • catB
  • catC
  • ...

My question is whether this approach is appropriate for this type of scenario, thinking about performance, when we will have many categories and many sub-sub-categories? If not appropriate what is the modern approach to this scenario?

mysql hierarchical-data

  • In case you are right to worry you would use a recursive query p/ find all decentines of a tag but recursiveness is limited in SQL and non-performance. One idea (I don’t know good or bad) is to use a relationship table that denotes the degree of kinship, e.g.: A => +2 => ABA, ABA => -2 =>A, A=>+1=>AB

  • Hi @jean vc could draw your idea? I was also thinking of an intermediate table to mitigate this problem.

No answers

Browser other questions tagged mysql hierarchical-data

You are not signed in. Login or sign up in order to post.