Как реализовать систему тегов

Мне было интересно, как лучше всего реализовать систему тегов, например, ту, что используется на SO. Я думал об этом, но я не могу придумать хорошее масштабируемое решение.

Я думал о базовом 3-х табличном решении: имея таблицу tags, таблицы articles и таблицу tag_to_articles.

Это лучшее решение этой проблемы или есть альтернативы? Используя этот метод, таблица будет очень большой во времени, и для поиска это не слишком эффективно, я предполагаю. С другой стороны, не так важно, чтобы запрос выполнялся быстро.

Ответ 1

Я считаю, что вы найдете интересный этот пост в блоге: Теги: схемы базы данных

Проблема: вы хотите иметь схему базы данных, где вы можете пометить закладки (или сообщение в блоге или что-то еще) с таким количеством тегов, которое вы хотите. Позже вы хотите запускать запросы, чтобы ограничить закладки объединение или пересечение тегов. Вы также хотите исключить (скажем: минус) некоторые теги из результата поиска.

"MySQLicious" решение

В этом решении схема имеет только одну таблицу, она денормализуется. Этот тип называется "MySQLicious solution", потому что MySQL.com импортирует данные del.icio.us в таблицу с этой структурой.

Пересечение (AND) Запрос для "search + webservice + semweb":

SELECT *
FROM `delicious`
WHERE tags LIKE "%search%"
AND tags LIKE "%webservice%"
AND tags LIKE "%semweb%"

Союз (OR) Запрос для "search | webservice | semweb":

SELECT *
FROM `delicious`
WHERE tags LIKE "%search%"
OR tags LIKE "%webservice%"
OR tags LIKE "%semweb%"

Минус Запрос для поиска "web-сервис-semweb"

SELECT *
FROM `delicious`
WHERE tags LIKE "%search%"
AND tags LIKE "%webservice%"
AND tags NOT LIKE "%semweb%"

Решение "Scuttle"

Scuttle организует свои данные в двух таблицах. Эта таблица "scCategories" является "tag" -table и имеет внешний ключ для "закладки" -table.

Пересечение (AND) Запрос для "bookmark + webservice + semweb":

SELECT b.*
FROM scBookmarks b, scCategories c
WHERE c.bId = b.bId
AND (c.category IN ('bookmark', 'webservice', 'semweb'))
GROUP BY b.bId
HAVING COUNT( b.bId )=3

Сначала выполняется поиск всех комбинаций меток-меток, где тегом являются "bookmark", "webservice" или "semweb" (c.category IN ( "bookmark", "webservice", "semweb" )), а затем просто учитываются закладки, у которых есть все три найденных тэга (HAVING COUNT (b.bId) = 3).

Союз (OR) Запрос для "закладки | webservice | semweb": Просто оставьте предложение HAVING, и у вас есть союз:

SELECT b.*
FROM scBookmarks b, scCategories c
WHERE c.bId = b.bId
AND (c.category IN ('bookmark', 'webservice', 'semweb'))
GROUP BY b.bId

Минус (исключение) Запрос для "bookmark + webservice-semweb", то есть: bookmark И webservice И НЕ semweb.

SELECT b. *
FROM scBookmarks b, scCategories c
WHERE b.bId = c.bId
AND (c.category IN ('bookmark', 'webservice'))
AND b.bId NOT
IN (SELECT b.bId FROM scBookmarks b, scCategories c WHERE b.bId = c.bId AND c.category = 'semweb')
GROUP BY b.bId
HAVING COUNT( b.bId ) =2

Оставив HAVING COUNT, вы получите запрос на "закладку | webservice-semweb".

Решение "Токси"

Toxi придумал структуру из трех таблиц. Через таблицу "tagmap" закладки и теги связаны с n-to-m. Каждый тег можно использовать вместе с разными закладками и наоборот. Эта DB-схема также используется wordpress. Запросы совершенно такие же, как в решении "scuttle".

Пересечение (AND) Запрос для "bookmark + webservice + semweb"

SELECT b.*
FROM tagmap bt, bookmark b, tag t
WHERE bt.tag_id = t.tag_id
AND (t.name IN ('bookmark', 'webservice', 'semweb'))
AND b.id = bt.bookmark_id
GROUP BY b.id
HAVING COUNT( b.id )=3

Союз (OR) Запрос для "закладки | webservice | semweb"

SELECT b.*
FROM tagmap bt, bookmark b, tag t
WHERE bt.tag_id = t.tag_id
AND (t.name IN ('bookmark', 'webservice', 'semweb'))
AND b.id = bt.bookmark_id
GROUP BY b.id

Минус (исключение) Запрос для "bookmark + webservice-semweb", то есть: bookmark И webservice И НЕ semweb.

SELECT b. *
FROM bookmark b, tagmap bt, tag t
WHERE b.id = bt.bookmark_id
AND bt.tag_id = t.tag_id
AND (t.name IN ('Programming', 'Algorithms'))
AND b.id NOT IN (SELECT b.id FROM bookmark b, tagmap bt, tag t WHERE b.id = bt.bookmark_id AND bt.tag_id = t.tag_id AND t.name = 'Python')
GROUP BY b.id
HAVING COUNT( b.id ) =2

Оставив HAVING COUNT, вы получите запрос на "закладку | webservice-semweb".

Ответ 2

Ничего плохого в решении с тремя таблицами.

Другой вариант - ограничить количество тегов, которые могут быть применены к статье (например, 5 в SO), и добавить их непосредственно в таблицу статей.

Нормализация БД имеет свои преимущества и недостатки, так же как вещи с жесткой проводкой в одну таблицу имеют преимущества и недостатки.

Ничего не говорится, что вы не можете обойти оба. Это противоречит парадигмам реляционных БД для повторения информации, но если целью является производительность, вам может потребоваться разорвать парадигмы.

Ответ 3

Ваша предлагаемая трехэтапная реализация будет работать для тегов.

Переполнение стека использует, однако, различную реализацию. Они сохраняют теги в столбце varchar в таблице сообщений в виде обычного текста и используют полную индексацию текста для получения сообщений, соответствующих тегам. Например posts.tags = "algorithm system tagging best-practices". Я уверен, что Джефф упоминал об этом где-то, но я забыл, где.

Ответ 4

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

Ответ 5

Если ваша база данных поддерживает индексируемые массивы (например, PostgreSQL, например), я бы рекомендовал полностью денормализованное решение - хранить теги в виде массива строк в одной таблице. Если нет, то лучшим решением является вторичная таблица, сопоставляющая объекты с тегами. Если вам нужно хранить дополнительную информацию в отношении тегов, вы можете использовать отдельную таблицу тегов, но нет смысла вводить второе соединение для каждого поиска тегов.