mysql - 关于标签数据库的设计，如何解决标签重定向，国际化或同义标签等问题？

Question

要在数据库设计一个标签系统，给各个实体打上标签。
然后又需要可以体现层次关系，比如红黑树是属于数据结构标签的子标签这种结构。

还要考虑到相同意义的标签重定向的情况，比如线段树和区间树其实讲的是一个东西，另外就是像国际化或者大小写这样的，Trie，trie，字典树又是一个东西。

现在想法是，给标签设一个parent_id来指向父标签来表示层次性，另外设一个redirect_id来进行重定向来做同类标签，然后统一用英文来设标签最后通过翻译来解决不同语言的同义标签问题，因为这个标签可能也会作为百科词条这样的设计，所以如何解决同义标签问题确实比较纠结。

感觉想并查集一样了，不知道这样设计好不好，有没有更好的设计方法等，因为这个标签也可能会作为百科词条一样的功能，所以想问问一般实际开发中是怎么处理这类问题的。

Answer 1

The design of the question is basically reliable. There are a few differences for discussion:

1. The label itself feels like a flat and loose thing, and the layers don’t seem to match well. At most, the label has a category, rather than a hierarchy, as is the case with segmentfault and many websites. Otherwise, it will become a hierarchical classification like e-commerce

2. The internationalization of tags is a bit strange. Chinese people also set up many English tags. Just like the trie and dictionary tree mentioned in the question, Chinese people all call them. If I do internationalization, I will record Chinese and English labels separately. The labels you see when you log in in Chinese are completely different from what you see when you log in in English. Suppose someone logs in in Chinese and adds two labels: trie and dictionary tree to an entity. When logging in in English, it will show that there is no label, unless another label is created in English

3. I am now more inclined to use document-type NoSQL such as mongodb and elasticsearch to store such articles and tags. It is very painful to do similar things using relational databases, especially mysql (which does not support array fields), like dancing with shackles

Answer 2

I have no relevant industry experience.

I agree with @manong’s answer. If you use parent_id to define the parent-child tag relationship, it will be embarrassing if one day the child tag may belong to two different parent tags. It is more flexible to use categories for management (of course, if your current business is not complex, it is not recommended to consider such long-term things).

The tag internationalization...cannot be understood. .
For example: I put a Chrome tag, and programmers all know it; but after internationalization, it turns into 铬, which is embarrassing...
Of course, whether there is any need for internationalization depends on It depends on your business (after all, it increases maintenance costs). I'm just giving an example from a programmer's perspective.

redirect_id is a relatively fast and simple implementation. If you are more flexible, you can build an intermediate relationship table.

Line segment trees and interval trees actually talk about the same thing. In addition, things like internationalization or capitalization, Trie, trie, and dictionary trees are another thing.

In addition, regarding the point you mentioned, you need a dictionary to express these relationships (which naturally requires manual maintenance), unless you use algorithms to judge.