Is it really worth it to normalize the “Toxi” way?

问题:

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>.

So to recap:

• 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?

Schema

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;

Sample Data

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);

Current query

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

Result

IF there are tags:

post_id     post_name        tags
1             test           mma, ufc

回答1:

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, for instance, renamed SQL Server related tags at least thrice (mssql -> sqlserver -> sql-server).

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.

(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.

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

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

回答2:

If you use the VARCHAR hack, it will be nearly impossible for you to query the data. It will be hell to write a query which accurately and efficiently shows all posts with a given tag (and let's face it, that's a pretty big aspect of a tagging system): The accuracy part is hard because you need to consider all possibilities for the comma; the effeciency part is hard because searching in a string is much, much slower than looking at the full value of a field (moreso if you could use an integer).

So yes, it is most certainly worth it.

As far as making your query faster is concerned - make sure you have the relevant indexes on your tables. Run an EXPLAIN on the query to see where any bottleneck is placed. I don't think it would be better to fetch the tags for each post as you process it, but it might be - I'm not sure how efficient MySQL really is at string manipulation, which is what it's doing when you do the GROUP_CONCAT.

回答3:

Your query of a tag would be very slow if you had a varchar with a list of tags in. You would be doing something along the lines of where post.tag like '%mytag%' which would not perform anywhere near as well as searching on an indexed key.

[edit] This study shows performance of various ways of doing tagging systems (including FULLTEXT indexed) and suggests where and when you would like to use each one.

回答4:

Joining (when you have correct indexes) is generally much faster than trying to pull data out of the middle of a comma delimited string in a field even using full text search. Or you could go with a bunch of separate tag fields (Tag1, tag2, tag3) and querying will still be harder (let me search 5 fields to find if I have used that tag) and you would need to add a new column every time you need to add a new tag and you've used up the existing columns. The normalized database design is the best possible, most performant way to go. Databases are designed to use joins. Why you wouldn't want to use them is beyond me.