Эта (нормализованная) структура базы данных разрешит мне искать тегами, как я предназначаю?

Ваша схема выглядит неплохо. Столбец ID в вашей таблице соединений не нужен - просто создайте первичный ключ из столбцов ID других таблиц (хотя см. Комментарий Марьяна Венемы и Следует использовать составные первичные ключи или нет? для альтернативных представлений на этом). В следующих примерах показано, как создавать таблицы, добавлять данные и выполнять запрошенные запросы.

Создайте таблицы с ограничениями внешнего ключа . Короче говоря, ограничения внешнего ключа помогают обеспечить целостность базы данных. В этом примере они предотвращают вставку элементов в объединяемую таблицу ( item_tag ), если в таблицах item и tag нет совпадающих элементов:

CREATE  TABLE IF NOT EXISTS `item` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `item` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `tag` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `tag` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `item_tag` (
  `item_id` INT UNSIGNED NOT NULL ,
  `tag_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`item_id`, `tag_id`) ,
  INDEX `fk_item_tag_item` (`item_id` ASC) ,
  INDEX `fk_item_tag_tag` (`tag_id` ASC) ,
  CONSTRAINT `fk_item_tag_item`
    FOREIGN KEY (`item_id` )
    REFERENCES `item` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_item_tag_tag`
    FOREIGN KEY (`tag_id` )
    REFERENCES `tag` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Вставьте некоторые тестовые данные:

INSERT INTO item (item) VALUES
('spaniel'),
('tabby'),
('chicken'),
('goldfish');

INSERT INTO tag (tag) VALUES
('bird'),
('pet'),
('dog'),
('cat'),
('reptile'),
('fish'),
('delicious'),
('cheap'),
('expensive');

INSERT INTO item_tag (item_id, tag_id) VALUES
(1,2),
(1,3),
(1,8),
(2,2),
(2,4),
(3,1),
(3,7),
(4,2),
(4,6),
(4,8);

Выберите все элементы и все теги:

SELECT item.id, item.item, tag.tag
FROM item
JOIN item_tag ON item_tag.item_id = item.id
JOIN tag ON item_tag.tag_id = tag.id;

+----+----------+-----------+
| id | item     | tag       |
+----+----------+-----------+
|  1 | spaniel  | pet       |
|  1 | spaniel  | dog       |
|  1 | spaniel  | cheap     |
|  2 | tabby    | pet       |
|  2 | tabby    | cat       |
|  3 | chicken  | bird      |
|  3 | chicken  | delicious |
|  4 | goldfish | pet       |
|  4 | goldfish | fish      |
|  4 | goldfish | cheap     |
+----+----------+-----------+

Выберите элементы с определенным тегом:

SELECT item.id, item.item, tag.tag
FROM item
JOIN item_tag ON item_tag.item_id = item.id
JOIN tag ON item_tag.tag_id = tag.id
WHERE tag = 'pet';

+----+----------+-----+
| id | item     | tag |
+----+----------+-----+
|  1 | spaniel  | pet |
|  2 | tabby    | pet |
|  4 | goldfish | pet |
+----+----------+-----+

Выберите элементы с одним или несколькими тегами. Обратите внимание, что это вернет элементы с тегами cheap OR pet :

SELECT item.id, item.item, tag.tag
FROM item
JOIN item_tag ON item_tag.item_id = item.id
JOIN tag ON item_tag.tag_id = tag.id
WHERE tag IN ('cheap', 'pet');

+----+----------+-------+
| id | item     | tag   |
+----+----------+-------+
|  1 | spaniel  | pet   |
|  1 | spaniel  | cheap |
|  2 | tabby    | pet   |
|  4 | goldfish | pet   |
|  4 | goldfish | cheap |
+----+----------+-------+

Приведенный выше запрос дает ответ, который может вам не понадобиться, как показано в следующем запросе. В этом случае нет элементов с тегом house , но этот запрос по-прежнему возвращает некоторые строки:

SELECT item.id, item.item, tag.tag
FROM item
JOIN item_tag ON item_tag.item_id = item.id
JOIN tag ON item_tag.tag_id = tag.id
WHERE tag IN ('cheap', 'house');

+----+----------+-------+
| id | item     | tag   |
+----+----------+-------+
|  1 | spaniel  | cheap |
|  4 | goldfish | cheap |
+----+----------+-------+

Вы можете исправить это, добавив GROUP BY и HAVING :

SELECT item.id, item.item, tag.tag
FROM item
JOIN item_tag ON item_tag.item_id = item.id
JOIN tag ON item_tag.tag_id = tag.id
WHERE tag IN ('cheap', 'house')
GROUP BY item.id HAVING COUNT(*) = 2;

Empty set (0.00 sec)

GROUP BY заставляет все элементы с одинаковым идентификатором (или любым указанным вами столбцом) группироваться вместе в одну строку, эффективно удаляя дубликаты. HAVING COUNT ограничивает результаты теми, у которых количество совпадающих сгруппированных строк равно двум.Это гарантирует, что будут возвращены только элементы с двумя тегами - обратите внимание, что это значение должно совпадать с количеством тегов, указанным в предложении IN . Вот пример, который дает что-то:

SELECT item.id, item.item, tag.tag
FROM item
JOIN item_tag ON item_tag.item_id = item.id
JOIN tag ON item_tag.tag_id = tag.id
WHERE tag IN ('cheap', 'pet')
GROUP BY item.id HAVING COUNT(*) = 2;

+----+----------+-----+
| id | item     | tag |
+----+----------+-----+
|  1 | spaniel  | pet |
|  4 | goldfish | pet |
+----+----------+-----+

Обратите внимание, что в предыдущем примере элементы были сгруппированы вместе, поэтому вы не получите дубликатов. В этом случае столбец tag не нужен, так как это просто сбивает результаты - вы уже знаете, какие есть теги, поскольку запрашивали элементы с этими тегами. Поэтому вы можете немного упростить задачу, удалив столбец tag из запроса:

SELECT item.id, item.item
FROM item
JOIN item_tag ON item_tag.item_id = item.id
JOIN tag ON item_tag.tag_id = tag.id
WHERE tag IN ('cheap', 'pet')
GROUP BY item.id HAVING COUNT(*) = 2;

+----+----------+
| id | item     |
+----+----------+
|  1 | spaniel  |
|  4 | goldfish |
+----+----------+

Вы можете пойти дальше и использовать GROUP_CONCAT для предоставления списка совпадающих тегов. Это может быть удобно, если вам нужен список элементов, которые имеют один или несколько из указанных тегов, но не обязательно все из них:

SELECT item.id, item.item, GROUP_CONCAT(tag.tag) AS tags
FROM item
JOIN item_tag ON item_tag.item_id = item.id
JOIN tag ON item_tag.tag_id = tag.id
WHERE tag IN ('cheap', 'pet', 'bird', 'cat')
GROUP BY id;

+----+----------+-----------+
| id | item     | tags      |
+----+----------+-----------+
|  1 | spaniel  | pet,cheap |
|  2 | tabby    | pet,cat   |
|  3 | chicken  | bird      |
|  4 | goldfish | pet,cheap |
+----+----------+-----------+

Одна проблема с приведенной выше схемой состоит в том, что можно вводить повторяющиеся элементы и теги. То есть вы можете вставлять птицу в таблицу tag сколько угодно раз, и это нехорошо. Один из способов исправить это - добавить УНИКАЛЬНЫЙ ИНДЕКС в столбцы элемента и тега . Это дает дополнительное преимущество, помогая ускорить запросы, основанные на этих столбцах. Обновленные команды CREATE TABLE теперь выглядят следующим образом:

CREATE  TABLE IF NOT EXISTS `item` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `item` VARCHAR(255) NOT NULL ,
  UNIQUE INDEX `item` (`item`) ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `tag` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `tag` VARCHAR(255) NOT NULL ,
  UNIQUE INDEX `tag` (`tag`) ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

Теперь, если вы попытаетесь вставить повторяющееся значение, MySQL не позволит вам это сделать:

INSERT INTO tag (tag) VALUES ('bird');
ERROR 1062 (23000): Duplicate entry 'bird' for key 'tag'

Да. Это называется реляционным делением. Здесь обсуждаются различные методы http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

One Подход заключался бы в использовании двойного отрицания. т.е. для выбора всех записей из таблицы 1, для которых нет тега в списке 'cheap', 'pet' не имеет связанной записи в table2

SELECT t1.id, t1.item
FROM Table1 t1
WHERE NOT EXISTS
(
    SELECT * FROM  
    table3 t3 WHERE tag IN ('cheap','pet')
    AND NOT EXISTS (
        SELECT * FROM table2 t2
        WHERE t2.tag_id = t3.id
        AND t1.id=t2.item_id
    )
)

1. Концепция картографической таблицы довольно стандартна и выглядит здесь хорошо реализованной. Единственное, что я бы изменил, это избавление от ID в таблице 2; для чего бы вы его использовали? Просто сделайте общий ключ для таблицы 2 по ID элемента и ID метки.

2. На самом деле, выбрать, где элемент соответствует ВСЕМ тегам, сложно. Попробуйте так:

   SELECT item_id,COUNT(tag_id) FROM Table2 WHERE tag_id IN (здесь ваш набор) GROUP BY item_id

Где счетчик равен количеству ID тегов в вашем наборе, вы нашли совпадение.

Вы можете попробовать что-то вроде этого:

select item, count(*) 'NrMatches'
from #table1 i
inner join #table2 l ON i.id = l.item_id
inner join #table3 t on l.tag_id = t.id
where t.tag IN ('cheap', 'pet', 'dog')
group by item
having count(*) = (select count(*) from #table3 
                   where tag IN ('cheap', 'pet', 'dog'))

Это означает, что ваши условия поиска будут повторяться дважды, но в основном это делает то, что вам нужно.

Не уверен, что другие могли уже упомянуть об этом, но столбец id во второй таблице является избыточным. Вы можете просто создать первичный ключ соединения:

PRIMARY KEY (item_id, tag_id)

В противном случае это краткая стандартная схема базы данных m: n, и она должна работать нормально.

Спасибо всем за очень подробные и полезные ответы. Бит об использовании «WHERE tag IN ('tag_1' ... 'tag_x')» в сочетании с COUNT для выбора элементов, соответствующих всем тегам, был именно тем, чего мне раньше не хватало.

Введение в использование составных первичных ключей также было действительно полезным - я чувствовал, что нет смысла использовать уникальный ID-ключ в средней таблице, но никогда не понимал, что могу использовать составные ключи.

Еще раз спасибо! Вы молодцы!