Mysql – Items and their tags

database-designMySQL

This is my table design: 

  CREATE TABLE IF NOT EXISTS `items` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `title` varchar(255) NOT NULL,
      `description` mediumtext NOT NULL,
      `user_id` bigint(100) NOT NULL,
      `to_read` tinyint(1) NOT NULL DEFAULT '0',
      `added_at` datetime NOT NULL,
      `created_at` datetime NOT NULL,
      `updated_at` datetime NOT NULL,
      PRIMARY KEY (`id`),
      KEY `user_id` (`user_id`),
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(100) NOT NULL,
  `item_id` int(11) NOT NULL,
  `tag` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `item_id` (`item_id`),
  KEY `user_id` (`user_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

My two queries: 

SELECT items.*, 
    (SELECT GROUP_CONCAT(tags.tag) 
       FROM tags 
      WHERE tags.item_id = items.id) AS tags 
FROM items WHERE items.user_id = 0;

Three-table layout with a tags_items table:

SELECT items.*, 
    (SELECT GROUP_CONCAT(tags.tag) FROM tags 
       WHERE tags.id = tags_items.tag_id) AS tags 
  FROM items 
  LEFT JOIN tags_items 
         ON tags_items.item_id = items.id 
 WHERE items.user_id = 0;

Question:

Is it good to go with 2 table design, or 3 table design?

When I tried out 3 table design, my second query, rewritten for 3-table design doesn't work properly, it simply doesn't concatenate properly like the 2nd table query.

Best Answer

As I hinted in my first comment, you have to move the join in the subquery, so you still have only one row of output per item. Example can be tested at http://sqlfiddle.com/#!9/11b37/3

SELECT items.*,
  (SELECT GROUP_CONCAT(tags.tag)
   FROM tags
   JOIN tags_items ON tags_items.tag_id = tags.id
   WHERE items.id = tags_items.item_id) AS tags
FROM items
WHERE items.user_id = 0;

Using 3 tables is the right design. With only two you would end up with multiple rows for the same tag name.

Your original tables have redundant user_id column in tags table (and that same would apply for tags_items) - as one tag/tags_items row can only be linked with one item the user_id is defined by it.

Related Question