标准化"Toxi"是否真的值得呢​​?道路? (3NF) [英] Is it really worth it to normalize the "Toxi" way? ( 3NF )

问题描述

我正处于数据库设计的早期阶段，所以尚无定论，并且我正在为具有可选标签的线程使用"TOXI" 3表设计，但我不禁感到加入并不是真正必要的，也许我只需要依靠posts表中的一个简单标签列即可在其中存储<tag>, <secondTag>之类的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>.

所以回顾一下:

• 值得在2个标记表上进行额外的左联接而不仅仅是在我的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

结果

IF 有标签:

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.

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

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.

这篇关于标准化"Toxi"是否真的值得呢​​?道路? (3NF)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！