对于存储相关关键字的数据库模式有什么建议吗?
我必须在数据库中存储一组相关的关键字。截至目前,我正在考虑使用以下内容:
存储关键字本身:
CREATE TABLE keywords(
id int(11) AUTO_INCREMENT PRIMARY KEY,
word VARCHAR(255)
);
存储关系(存储相关关键字的 id):
CREATE TABLE relatedkeywords(
id int(11) AUTO_INCREMENT PRIMARY KEY,
keyword1 int(11),
keyword2 int(11),
FOREIGN KEY (keyword1) REFERENCES keywords(id),
FOREIGN KEY (keyword2) REFERENCES keywords(id)
);
这是惯例还是有更好的方法?我看到的唯一问题是我需要检查两个列,以便有时能够获取相关的关键字......我可能在这里遗漏了一些东西。
I have to store a set of related keywords inside a database. As of now, I am thinking of using the following:
To store the keywords themselves:
CREATE TABLE keywords(
id int(11) AUTO_INCREMENT PRIMARY KEY,
word VARCHAR(255)
);
To store the relations (stores the ids of the related keywords):
CREATE TABLE relatedkeywords(
id int(11) AUTO_INCREMENT PRIMARY KEY,
keyword1 int(11),
keyword2 int(11),
FOREIGN KEY (keyword1) REFERENCES keywords(id),
FOREIGN KEY (keyword2) REFERENCES keywords(id)
);
Is this the convention or is there a better way of doing this? The only problem I am seeing is that I need to check both the column in order to be able to get the related keywords sometimes... I might be missing something here.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
如果“相关性”是一对关键字的属性,则此模式是可以的(不要忘记添加 UNIQUE(keyword1, keywords2))
如果“相关性”可以传播一组关键字,并且一组相关关键字可能有附加的在属性中,您可能需要添加一个新表“Related_Set”以及关键字和集合之间的 M:N 关系“Keyword_Set”。
如果集合没有任何附加属性,您可能只使用“Keyword_Set”表
If "relatedness" is a property of a pair of keywords, this schema is OK (don't forget to add UNIQUE(keyword1, keyword2))
If "relatedness" can spread a set of keywords and a set of related keywords may have additional propertirs, you may want to add a new table "Related_Set" and a M:N relationship "Keyword_Set" between keywords and sets.
If a set doesn't have any additional properties, you may just live with "Keyword_Set" table
将第二个表简化为:
因为这是“人造主键”没有什么意义并且没有实际用处的情况之一。
Simplify the second table to:
as this is one of the cases where an "artificial primary key" just makes little sense and offers no practical usefulness.
有没有只有一张桌子的解决方案 -
Is there a solution with just one table -