在 SQL Server 中保存数据,正确的方法

发布于 2024-08-16 20:44:25 字数 673 浏览 8 评论 0原文

我们有用于发布文章的内部应用程序,使用 SQL Server 2005。

对于该任务,我需要在用户发布帖子时保存标签列表。

我是否应该为标签创建一个单独的表,并使用列出它的帖子的 id 更新标签的 ID 列,使用 XML 列,

TABLE_TAGS
TAG_NAME varchar, ARTICLE_IDS XML

或者

在 ARTICLE 表本身中创建一个列,例如

TABLE_ARTICLE 
COLUMN_TAGS XML //  and store tag, the post is associated with.

当用户单击“标签云”中​​的特定标签时”,我们需要显示带有该标签的帖子,就像任何博客一样。我们还需要嵌套 SQL 语句来显示具有更多点击率或讨论等的帖子,就像

SELECT ARTICLE_TITLE, ARTICLE_URL FROM
TABLE_ARTICLE 
WHERE
ARTICLE_ID = 
(SELECT ARTICLE_IDS FROM TABLE_TAGS
WHERE TAG_NAME = @TAGTOFIND)

我不确定在添加、更新或效率方面如何以及什么是最佳方法一样。

还有其他提示吗?

谢谢

We have internal application used to publish articles, using SQL Server 2005.

For the task I need to save list of tags when a user publishes a post.

Should I make a separate table For Tags and update the ID column for a TAG with the id of the post that lists it, using XML column like

TABLE_TAGS
TAG_NAME varchar, ARTICLE_IDS XML

OR

make a column in ARTICLE table itself like

TABLE_ARTICLE 
COLUMN_TAGS XML //  and store tag, the post is associated with.

When a user clicks particular tag in the "TAG cloud", we need to bring up the posts listed with that tag, just like any blog. We will need nested SQL statements as well to bring up the posts with more hits or discussions etc, like

SELECT ARTICLE_TITLE, ARTICLE_URL FROM
TABLE_ARTICLE 
WHERE
ARTICLE_ID = 
(SELECT ARTICLE_IDS FROM TABLE_TAGS
WHERE TAG_NAME = @TAGTOFIND)

I am not sure how and what is the best approach in terms of adding, update or efficiency.

Any other hint ?

Thanks

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

你的他你的她 2024-08-23 20:44:25

articletags_model_01

SELECT  ArticleTitle
       ,ArticleURL
FROM    Article AS a
        JOIN Article_Tag AS x ON x.ArticleID = a.ArticleID
        JOIN Tag AS t ON t.TagID = x.TagID
WHERE   t.TagName = @SomeTag

articletags_model_01

SELECT  ArticleTitle
       ,ArticleURL
FROM    Article AS a
        JOIN Article_Tag AS x ON x.ArticleID = a.ArticleID
        JOIN Tag AS t ON t.TagID = x.TagID
WHERE   t.TagName = @SomeTag
千里故人稀 2024-08-23 20:44:25

处理多对多关系的常用方法是使用连接表(又名连接表、桥接表、直通表、等。 ...)。因此,您将有一个标签表、一个文章表和第三个“文章上使用的标签”表,其中只有 TagId 和 ArticleId 外键。要找出哪些标签与任何给定文章关联,或者哪些文章与任何给定标签关联,您可以对目标表(标签或文章)和连接表执行连接查询。

The usual way to handle many-to-many relationships is to use a join table (aka junction table, bridge table, through table, etc....). Thus, you would have a table of Tags, a table of Articles, and a third table of "tag used on article" which just had TagId and ArticleId foreign keys. To find out which tags were associated with any given article, or which articles were associated with any given tag, you would perform a join query on the target table (Tags or Articles) and the join table.

她说她爱他 2024-08-23 20:44:25

如果是我 - 这可能不对......

除了您的文章表之外,我还会使用两个表:

  • 一张用于标签的表:ID,标签
  • 一张用于将文章链接到标签的表:ArticleID,TagID

这将涉及维护文章标签的大量工作(解析标签列表,标签是否存在,如果不创建等),但另一方(搜索 - 按标签和标签、显示、标签云计数)应该变得更容易,更重要的是,更快,因为您可以对事物进行索引。因为当搜索是关键性能目标时,读取速度应该远远超过写入速度。

If it were me - and this may not be right...

I'd go with two tables in addition to your article table:

  • One table for Tags: ID, Tag
  • One table to link Articles to tags: ArticleID, TagID

This will involve a chunk of work in maintaining an article's tags (parsing the tags list, does the tag exist, if not create, etc) but then the other side (search - both by tag and for tags, display, counts for your tag cloud) should be both easier and, more importantly, faster as you can index things. Since you should be doing read far more than write speed when searching is the key performance target.

无法言说的痛 2024-08-23 20:44:25

将 XML 存储在数据库中自然不会有利于性能。考虑通过使用实现关系的多对多表,以规范化的方式存储标签与文章的关系:

Table: ArticleTag

ArticleId int
TagId int

这允许您查询标签和文章,而无需借助数据库中的 XML 支持。

Storing XML in a database is not naturally good for performance. Consider storing the tags to article relation in a normalized manner, by having a many-to-many table that implements the relation:

Table: ArticleTag

ArticleId int
TagId int

This allows you to query tags and articles without resorting to XML support in the database.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文