可以是多个类别的子类别?

I have a different situation that a category can be a sub category of more than one category. How should I design this ?

我有一个不同的情况，一个类别可以是多个类别的子类别。我该如何设计?

The tree structure below doesnt solve my problem I guess. Should I do ParentCategoryId as a string and can take multiple category id in it. Then do calculation to resolve parent categories or do you advise me another solution ? What is your optimum solution to solve this problem ?

下面的树结构并不能解决我的问题。我是否应该将ParentCategoryId作为一个字符串，并在其中包含多个类别id。然后做计算来解决父类或者你建议我另一个解决方案吗?你解决这个问题的最佳方案是什么?

--Category--

Id
ParentCategoryId
CategoryName

Thanks in advance,

提前谢谢,

2 个解决方案

#1

The standard SQL solution for this is a cross table. You would have two tables, categories and categories_x as follows:

标准的SQL解决方案是一个交叉表。您将有两个表，类别和categories_x，如下所示:

categories
+------------+------------+
|categoryId  |name        |
+------------+------------+
|1           |foo         |
+------------+------------+
|2           |bar         |
+------------+------------+
|3           |huh         |
+------------+------------+

categories_x
+------------+------------+
|categoryId  |parentId    |
+------------+------------+
|3           |1           |
+------------+------------+
|3           |2           |
+------------+------------+

To get the parents of category 3 your sql query would look like:

要获取第3类的父类，您的sql查询应该如下:

SELECT categoryId, name 
  FROM categories 
  WHERE categoryId IN (SELECT parentId FROM categories_x WHERE categoryId = 3)

#2

In addition to what John Clark said, it's also a good idea to have a precalculated table of all the category of your things like:

除了约翰·克拉克所说的，还有一个好主意，那就是有一个预先计算好的表格，列出你所有的东西，比如:

Item Direct Categories
+------------+------------+
|itemId      |categoryId  |
+------------+------------+
|1           |1           |
+------------+------------+
|3           |2           |
+------------+------------+

and then

然后

Item All Categories
+------------+------------+
|itemId      |categoryId  |
+------------+------------+
|1           |1           |
+------------+------------+
|3           |2           |
+------------+------------+

In the first one you'll only save the categories of your items, but in the second one you'll also associate which each item the super categories of their categories, so you can check which just a single query if an item has a category or not.

在第一个选项中，您将只保存条目的类别，但是在第二个选项中，您还将把每个条目与它们的类别的超类别关联起来，这样您就可以在一个条目是否有类别时检查哪个查询。

#1