T-SQL标签数据库架构设计？ [英] T-SQL Tag Database Architecture Design?

问题描述

我正在构建一个包含一系列不同表的数据库。这些包括一个COMMENTS表，一个BLOGS表和一个ARTICLES表。我想要能够在每个表格中添加新的项目，并使用0到5个标签来标记它们，以帮助用户更轻松地搜索相关信息。

I am building a database that contains a series of different tables. These consist of a COMMENTS table, a BLOGS table & an ARTICLES table. I want to be able to add new items to each table, and tag them with between 0 and 5 tags to help the user search for particular information that is relevant more easily.

我的第一个想法是拥有一个TAGS的集中表。该表将列出所有可用的标签，使用TagID字段&一个TagName字段。由于每个项目可以有多个标签，每个标签可以有很多项目，所以我需要在每个项目表和TAGS表之间有一个多对多的关系。

My first thoughts were to have a centralised table of TAGS. This table would list all of the available tags using a TagID field & a TagName field. Since each item can have many tags and each tag can have many items, I would need a MANY-TO-MANY relationship between each item table and the TAGS table.

许多评论可以有很多标签。
许多TAGS可以有很多评论。

Many COMMENTS can have many TAGS. Many TAGS can have many COMMENTS.

许多文章可以有很多标签。
许多TAGS可以有很多文章。

Many ARTICLES can have many TAGS. Many TAGS can have many ARTICLES.

等.....

从以往的经验我了解到，在T-SQL中实现此结构的一种方法是在COMMENTS表和TAG表之间建立一个连接表。这个附加表将包含CommentID& TagID，以及它自己唯一的CommentTagID。这个结构也适用于所有其他项目。

From previous experience I understand that a way of implementing this structure in T-SQL is to have an ajoining table between the COMMENTS table and the TAG table. This ajoining table would contain the CommentID & the TagID, as well as its own unique CommentTagID. This structure would also apply to all other items.

首先是正确的方法实现这样的数据库架构？如果没有，还有什么其他方法可行？由于数据库最终将包含大量信息，因此我需要确保它是可扩展的。这是可扩展的实现吗？
如果我有很多这些表，这个架构会使CRUD操作非常慢吗？
我应该使用GUID或增加INT的ID字段吗？

Firstly is this the right way to go about implementing such a database architecture? If not, what other methods would be feasible? Since the database will eventually contain a lot of information, I need to ensure that it is scalable. Is this a scalable implementation? If I had lots of these tables would this architecture make CRUD operations very slow? Should I use GUIDs or Incrementing INTs for the ID fields?

帮助&

谢谢。

推荐答案

你可以还要查看 WordPress模式和数据库描述，了解其他人如何解决类似问题。

You may also want to look at WordPress schema and database description to see how others are solving a similar problem.

这篇关于T-SQL标签数据库架构设计？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！