关键词表格设计
我有很多关键字，并且有一个与每个关键字相关的单词列表
说关键字是科学，计算机，结构
每个关键字下都有一个单词列表，例如
科学 - 植物学，动物学，物理学......
计算机 - 数据库，操作系统，......
结构 - 堆栈，队列，数组，......
我需要将其存储在我的数据库中，对此有什么好的设计？将它们存储在单个表中似乎很愚蠢，因为它们彼此不相关，但创建不同的表似乎也是一种开销！所以在这里我很困惑。
I have a many keywords and there are a list of words associated with each keyword
Say keywords are SCIENCE , COMPUTERS, STRUCTURES
There are a list of words under each keyword like
SCIENCE - botany , zoology, physics .....
COMPUTERS - databases , operating systems, .....
STRUCTURES - stacks, queues, arrays, ....
I need to store this in my database, what is a good design for this? Storing them in a single table seems stupid as they are not related to each other, but creating different table seems like a overhead too! So here I am confused.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我相信这应该可以。
subcategory中的CATID是引用category表中ID的外键。I believe this should do.
CATIDinsubcategoryis a foreignkey referencing theIDincategorytable.您以错误的方式思考“关系”。虽然植物学确实与数据库无关（除了一些非常非常罕见的极端情况），但您看到的是数据，而不是事物。
您如何建模取决于您如何查看这些关键字。这是严格的单亲关系吗，子关键字永远不能有子关键字，并且子关键字与父关键字根本不同（换句话说，父关键字仅充当分类机制，而不是，他们自己，关键词）？或者您可以任意深度地“嵌套”这些关键字并将它们用作任何级别的关键字（换句话说，“植物学”可能有子级，而“科学”与“植物学”一样都是关键字）？
如果是第一个，您需要对其进行如下建模：
如果是第二个，您将对其进行如下建模：
其中
ParentKeywordID是返回到Keyword 的可为空外键。您已经创建了一个引用自身的表，这样的结构定义了一个树结构，其中的节点可以嵌套在任何级别。旁注
许多人会告诉您，在数据库中存储
null值是一个坏主意，我总体上同意这些人的观点。如果您确实想要采用完全标准化的存储格式（无论如何标准化为 5NF），您必须这样做：那么您的顶级关键字在
KeywordParent中不会有行。从数据库设计的角度来看，这种设计通常被认为是“更好”，尽管它会使您的查询稍微复杂化（仅在它们的构造中；它们的性能不会更差，并且实际上可能性能更好没有可为空的列）。
You're thinking about "relations" in the wrong way. While it's true that botany is not related to databases (except in some very, very rare corner cases), you're looking at the data, not the thing.
How you model this depends on how you're viewing these keywords. Is this a strict single-parent relationship, where a child keyword can never have children and child keywords are fundamentally different from parent keywords (in other words, the parents serve only as a classification mechanism and are not, themselves, keywords)? Or could you "nest" these keywords arbitrarily deeply and use them as keywords at any level (in other words, "botany" might have children, and "SCIENCE" is just as much a keyword as "botany" is)?
If it's the first, you'd want to model it something like this:
If it's the second, you'd model it something like this:
Where
ParentKeywordIDis a nullable foreign key back toKeyword. You've created a table that references itself, and structures like this define a tree structure with nodes that can be nested at any level.Side note
Many will tell you that storing
nullvalues in the database is a bad idea, and I would agree with these people in general. If you truly want to go to a fully normalized storage format (normalized to 5NF, anyway), you'd have to do it like this:Then your top-level keywords simply would not have rows in
KeywordParent.This sort of design is generally regarded as "better" from a database design perspective, though it will slightly complicate your queries (only in their construction; they won't perform any worse, and may actually perform better without nullable columns).
典型的树结构。
Typical Tree structure.