Regarding the design of label database, how to solve the problems of label redirection, internationalization or synonymous labels?

  mysql, question

To design a labeling system in the database, label each entity.
Then need to be able to reflect the hierarchical relationship, such asRED BLACK TREEIt belongs toData structureThis structure of sub-tags of tags.

We should also consider the situation of label redirection with the same meaning, such asLine segment treeAndInterval treeIn fact, I am talking about one thing, and the other is something like internationalization or capitalization.Trie,trie,trieAnother thing.

The idea now is to set a labelparent_idTo point to the parent tag to indicate hierarchy, and set anotherredirect_idTo redirect to do the same kind of label, and then unified in English to set the label finally through translationSolve the problem of synonymous labels in different languagesBecause of thisLabelIt may also be used as a design like encyclopedic items’s, so how to solve the synonymous label problem is really quite tangled.

I feel like Union Find, and I don’t know whether this design is good or not, whether there is a better design method, etc. Because this label may also serve as the same function as encyclopedic items, I would like to ask how such problems are handled in general actual development.

This design is basically reliable, and there are several differences for discussion:

  1. The label itself feels flat and loose and does not seem to fit well in different levels. At most, labels have a category category, not a hierarchy, as is the case with segmentfault and many websites. Otherwise, it will become the hierarchical classification of e-commerce

  2. The internationalization of labels is a bit strange. The Chinese will also set up many English labels, just like the trie and trie mentioned by Question inside. The Chinese will call them. If I do internationalization, I will record the Chinese and English labels separately. The labels I see when logging in in Chinese and the labels I see when logging in in English a re totally different things. Assuming that someone has added trie and trie tags to an entity when logging in in Chinese, English login will show that there is no tag unless another tag is created in English

  3. I now prefer to use mongodb and elasticsearch as document type nosql to store such articles and labels. Using relational databases, especially mysql (which does not support array fields), to do similar things is painful, like dancing in chains.