设计具有层次结构/子类别的SQL表 [英] Designing a SQL table with hierarchy/sub-categories

问题描述

我有一个看起来像这样的表：

I have a table that looks something like this:

ID |关键字|分类|子类别|子子类别| Sub-Sub-Sub-Category

ID | Keyword | Category | Sub-Category | Sub-Sub-Category | Sub-Sub-Sub-Category

我是否需要将其分成两个表（关键字表和具有父ID的类别表）
（如果有）关键字只能属于一个类别，子类别...等。表示没有重复。

Do i need to split it up in two tables (a keyword table and a categories table with parent id) if one keyword can only belong to one category,sub-category...etc. meaning there are no repetition. is there still a need to split it up?

推荐答案

您只需要一个表即可表示1-1映射。要表示一个或多个映射，您应该使用多个表。

You only need one table to represent a 1-1 mapping. To represent 1-many or many-many mappings, you should use multiple tables.

如果关键字只能对应一个类别/子类别/子类别，

If a keyword can only correspond to one category/sub-category/sub-sub-category, your current layout should be fine.

一个警告：如果您要基于关键字进行搜索，则分离表可能会提高性能。执行整数搜索要快得多。

One caveat: if you want to search based on the keyword, there could be performance gains for separating the tables. It's much faster to perform an integer search.

关于将关键字值存储在另一个表中的讨论大致对应于此讨论中将国家名（通常是静态的）存储在另一个表中。使用另一个表的一些主要优点可能是（说出）语言独立性，快速搜索以及以后易于更新。

The discussion of storing the keyword values in another table coarsely corresponds to this discussion of storing country names (which are mostly static) in another table. Some key advantages of using another table might be things like (spoken) language independence, fast searching, and ease of updating later on.

这篇关于设计具有层次结构/子类别的SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！