本文介绍了将“毒物"标准化是否真的值得?办法?( 3NF )的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正处于数据库设计的早期阶段,所以还没有最终确定,我正在为我的线程使用TOXI"3 表设计,这些线程具有可选标签,但我不禁感到加入并不是真正必要的,也许我只需要依赖 posts 表中的一个简单的标签列,我可以在其中存储诸如 、 之类的 varchar;.

I'm in the early stages of my database design so nothing is final yet, and I'm using the "TOXI" 3-table design for my threads which have optional tags, but I can't help but feel that the joining is not really necessary and perhaps I need to just rely on a simple tags column in my posts table where I can just store a varchar of something like <tag>, <secondTag>.

总结一下:

  • 在我的 posts 表中添加一个额外的左连接而不是在我的 posts 表中添加一个标签列是否值得.
  • 有什么方法可以优化我的查询?
  • is it worth the trouble of the extra left joins on the 2 tag tables instead of just having a tag column in my posts table.
  • is there a way I can optimize my query?
CREATE TABLE `posts` (
    `post_id` INT UNSIGNED PRIMARY AUTO_INCREMENT,
    `post_name` VARCHAR(255)
) Engine=InnoDB;

CREATE TABLE `post_tags` (
    `tag_id` INT UNSIGNED PRIMARY AUTO_INCREMENT,
    `tag_name` VARCHAR(255)
) Engine=InnoDB;

CREATE TABLE `post_tags_map` (
    `map_id` INT PRIMARY AUTO_INCREMENT,
    `post_id` INT NOT NULL,
    `tags_id` INT NOT NULL,
    FOREIGN KEY `post_id` REFERENCES `posts` (`post_id`),
    FOREIGN KEY `post_id` REFERENCES `post_tags` (`tag_id`)
) Engine=InnoDB;

示例数据

INSERT INTO `posts` (`post_id`, `post_name`)
  VALUES
(1, 'test');

INSERT INTO `post_tags` (`tag_id`, `tag_name`)
  VALUES
(1, 'mma'),
(2, 'ufc');

INSERT INTO `posts_tags_map` (`map_id`, `post_id`, `tags_id`)
  VALUES
(1, 1, 1),
(2, 1, 2);

当前查询

SELECT
    posts.*,
    GROUP_CONCAT( post_tags.tag_name order by post_tags.tag_name ) AS tags

  FROM posts
    LEFT JOIN posts_tags_map
      ON posts_tags_map.post_id = posts.post_id
    LEFT JOIN post_tags
      ON posts_tags_map.tags_id = posts_tags.tag_id

  WHERE posts.post_id = 1
  GROUP BY post_id

结果

如果有标签:


post_id     post_name        tags
1             test           mma, ufc

推荐答案

将所有标签放在不同的记录中(标准化)意味着您将能够在需要时更轻松地重命名标签并跟踪标签名称历史记录.

Having all tags in different records (normalized) means that you'll be able to rename the tags more easily should the need arise and track the tag name history.

SO,例如,将 SQL Server 相关标签重命名至少三次 (mssql -> sqlserver -> sql-server).

SO, for instance, renamed SQL Server related tags at least thrice (mssql -> sqlserver -> sql-server).

在一条记录中包含所有标签(非规范化)意味着您可以使用 FULLTEXT 索引将此列编入索引,并一次搜索具有两个或多个标签的帖子:

Having all tags in one record (denormalized) means that you can index this column with a FULLTEXT index and search for posts having two or more tags at once:

SELECT  *
FROM    posts
WHERE   MATCH(tags) AGAINST('+mma +ufc')

这也是可能的,但使用标准化设计效率较低.

which is possible too but less efficient with normalized design.

(不要忘记将 @ft_min_word_len 调整为索引 3 个字符或更少字符的标签以使其工作)

(Don't forget to adjust @ft_min_word_len to index tags of 3 characters or less for this to work)

您可以组合两种设计:同时存储映射表和非规范化列.不过,这将需要更多的维护.

You can combine both designs: store both the map table and the denormalized column. This will require more maintenance, though.

您还可以将规范化设计存储在您的数据库中,并使用您提供的查询将标签提供给 SphinxLucene.

You can also store the normalized design in your database and use the query you provided to feed the tags to Sphinx or Lucene.

这样,您可以使用MySQL 进行历史挖掘,使用Sphinx 进行全文标签搜索,无需额外维护.

This way, you can do history digging with MySQL, fulltext tag searches using Sphinx, and no extra maintenance will be required.

这篇关于将“毒物"标准化是否真的值得?办法?( 3NF )的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-03 22:06
查看更多