Рекомендуемый дизайн базы данных SQL для тегов или тегов
Я слышал о нескольких способах реализации тегов; использование таблицы сопоставления между TagID и ItemID (имеет смысл для меня, но масштабируется ли она?), добавление фиксированного количества возможных столбцов TagID в ItemID (кажется плохой идеей), сохранение тегов в текстовом столбце, разделенном запятыми (звучит сумасшедший но мог работать). Я даже слышал, как кто-то рекомендовал разреженную матрицу, но как же тогда имена тегов изящно растут?
Мне не хватает лучших практик для тегов?
Еще один интересный вопрос: «Как SO реализует теги?»
Еще один интересный вопрос: «Будете ли вы интернационализировать их, и если да, то как?»
Интересное сравнение (специфично для Postgres): databaseoup.com/2015/01/tag-all-things.html
см. также stackoverflow.com/questions/48475/database-design-for-taggin g
Ответы 6
Три таблицы (одна для хранения всех элементов, одна для всех тегов и одна для связи между ними), правильно проиндексированные, с установленными внешними ключами, работающими в соответствующей базе данных, должны хорошо работать и правильно масштабироваться.
Table: Item
Columns: ItemID, Title, Content
Table: Tag
Columns: TagID, Title
Table: ItemTag
Columns: ItemID, TagID
Это решение известно как «Toxi», дополнительную информацию о нем можно найти здесь: howto.philippkeller.com/2005/04/24/Tags-Database-schemas
Одна вещь, не показанная здесь, - это иерархические «теги» или категории в таблице тегов. Это обычно необходимо на сайтах, которые имеют категории и подкатегории, но нуждаются в гибкости тегов. Например, сайты рецептов, сайты автозапчастей, бизнес-каталоги и т. д. Эти типы данных обычно не попадают только в одну категорию, поэтому тегирование - это ответ, но вам нужно использовать что-то вроде модели вложенного набора или модели списка смежности. в вашей таблице тегов.
Я согласен с HK1, возможно ли это с приведенной выше структурой + Таблица: Столбцы группы тегов: Tag GroupId, Таблица заголовков: Столбцы тегов: TagID, Title, TagGroupId
когда я хочу добавить столбец css в таблицу, я добавлю столбец css в таблицу тегов?
Я думал, что для таблицы ItemTag нужен идентификатор столбца ItemTagID. Я ошибаюсь? я что-то пропускаю?
Я думаю, что тег таблицы должен иметь только заголовок как PK, наличие отдельного идентификатора в качестве первичного ключа, особенно с автоматическим увеличением, приведет к тому, что тег таблицы будет заполнен повторяющимися значениями при вставке / обновлении (в отношении ManyToMany).
@ftvs: ссылка снова не работает, новая ссылка howto.philippkeller.com/2005/04/24/Tags-Database-schemas
Я всегда держал теги в отдельной таблице, а затем имел таблицу сопоставления. Конечно, я тоже никогда ничего не делал в больших масштабах.
Наличие таблицы «тегов» и таблицы карты делает довольно тривиальным создание облаков тегов и тому подобного, поскольку вы можете легко собрать SQL, чтобы получить список тегов с подсчетом того, как часто каждый тег используется.
Это еще проще, если вы не используете таблицу сопоставления :)
Используйте один столбец форматированного текста [1] для хранения тегов и используйте способную систему полнотекстового поиска для его индексации. В противном случае вы столкнетесь с проблемами масштабирования при попытке реализовать логические запросы.
Если вам нужна подробная информация об имеющихся у вас тегах, вы можете либо отслеживать их в таблице, поддерживаемой постепенно, либо запускать пакетное задание для извлечения информации.
[1] Некоторые СУБД даже предоставляют собственный тип массива, который может даже лучше подходить для хранения, поскольку не требует этапа синтаксического анализа, но может вызвать проблемы с полнотекстовым поиском.
Знаете ли вы о какой-либо системе полнотекстового поиска, которая не находит вариантов слова? Например, поиск книги возвращает книги? Кроме того, что вы делаете с такими тегами, как «C++»? SQL Server, например, удалит знаки плюса в индексе. Спасибо.
Попробуйте Sphinx - sphinxsearch.com
Этот учебник из трех частей может быть полезен тем, кто идет по этому маршруту (полнотекстовый поиск). Он использует собственные средства PostgreSQL: shisaa.jp/postset/postgresql-full-text-search-part-1.html
это лучше, чем выбранный ответ с точки зрения производительности?
как насчет сохранения с использованием varchar 255, тегов, разделенных запятыми, и добавления к нему полнотекстового индекса?
Если вы используете базу данных, которая поддерживает map-reduce, например couchdb, хранение тегов в текстовом поле или поле списка действительно лучший способ. Пример:
tagcloud: {
map: function(doc){
for(tag in doc.tags){
emit(doc.tags[tag],1)
}
}
reduce: function(keys,values){
return values.length
}
}
Выполнение этого с помощью group = true сгруппирует результаты по имени тега и даже вернет количество встреч с этим тегом. Это очень похоже на подсчет вхождений слова в текст.
+1 Приятно также видеть некоторые реализации NoSQL.
@NickRetallack Ссылка не работает. Если можно, обновите этот ответ.
Хорошо, я заменил ссылку на archive.org
Обычно я соглашусь с Яаковом Эллисом, но в этом частном случае есть другое жизнеспособное решение:
Используйте две таблицы:
Table: Item
Columns: ItemID, Title, Content
Indexes: ItemID
Table: Tag
Columns: ItemID, Title
Indexes: ItemId, Title
У этого есть несколько основных преимуществ:
Во-первых, это значительно упрощает разработку: в решении с тремя таблицами для вставки и обновления item вам нужно искать в таблице Tag, есть ли уже записи. Тогда вы должны присоединить к ним новые. Это нетривиальная задача.
Затем он делает запросы проще (и, возможно, быстрее). Вам нужно выполнить три основных запроса к базе данных: вывести все Tags для одного Item, нарисовать облако тегов и выбрать все элементы для одного заголовка тега.
Все теги для одного предмета:
3 стола:
SELECT Tag.Title
FROM Tag
JOIN ItemTag ON Tag.TagID = ItemTag.TagID
WHERE ItemTag.ItemID = :id
2-стол:
SELECT Tag.Title
FROM Tag
WHERE Tag.ItemID = :id
Облако тегов:
3 стола:
SELECT Tag.Title, count(*)
FROM Tag
JOIN ItemTag ON Tag.TagID = ItemTag.TagID
GROUP BY Tag.Title
2-стол:
SELECT Tag.Title, count(*)
FROM Tag
GROUP BY Tag.Title
Предметы для одного тега:
3 стола:
SELECT Item.*
FROM Item
JOIN ItemTag ON Item.ItemID = ItemTag.ItemID
JOIN Tag ON ItemTag.TagID = Tag.TagID
WHERE Tag.Title = :title
2-стол:
SELECT Item.*
FROM Item
JOIN Tag ON Item.ItemID = Tag.ItemID
WHERE Tag.Title = :title
Но есть и некоторые недостатки: это может занять больше места в базе данных (что может привести к большему количеству дисковых операций, которые будут медленнее), и он не нормализован, что может привести к несоответствиям.
Аргумент размера не так уж силен, потому что сама природа тегов такова, что они обычно довольно малы, поэтому увеличение размера не является большим. Можно утверждать, что запрос заголовка тега выполняется намного быстрее в небольшой таблице, которая содержит каждый тег только один раз, и это, безусловно, правда. Но с учетом экономии за счет отсутствия необходимости присоединяться и того факта, что вы можете построить на них хороший индекс, можно легко это компенсировать. Это, конечно, сильно зависит от размера используемой базы данных.
Аргумент непоследовательности тоже немного спорен. Теги - это поля с произвольным текстом, и здесь нет ожидаемой операции типа «переименовать все теги с« foo »в« bar »».
Итак, tldr: я бы выбрал решение с двумя столами. (На самом деле я собираюсь это сделать. Я нашел эту статью, чтобы узнать, есть ли веские аргументы против нее.)
Означает ли "Index: ItemId, Title" индекс для каждого или один индекс, содержащий и то, и другое?
Обычно два индекса. Однако это может зависеть от используемой вами базы данных.
В таблице тегов есть ItemId, а тег - составной ключ? или ПК у тебя тоже есть?
Я думаю, это зависит от того, какой фреймворк вы используете / как вы привыкли писать код. Иногда проще иметь первичный ключ, а иногда нет. В моем случае у меня есть ПК, потому что мне больше нравится использовать что-то вроде DELETE? Id = 5, чем DELETE? Idemid = 3 & tag = Foo в веб-клиенте. С другой стороны, с точки зрения согласованности данных я бы не стал использовать дополнительный ПК. И в учебных целях я бы не рекомендовал это :)
Привет, вы сказали - «с учетом экономии за счет отсутствия присоединения и того факта, что вы можете построить хороший индекс» - «маленькая» таблица также будет проиндексирована, размер индекса будет значительно меньше. присоединение к FK, которое имеет тип int, должно быть намного быстрее, чем запрос огромного индекса nvarchar (n). С учетом сказанного, мне нравится эта идея, и я, вероятно, воспользуюсь ее производным, чтобы объединить множество различных типов элементов (таблиц) с общими тегами.
Привет h.alex. Дело в том, что вам нужно делать меньше соединений, которые могут оказаться быстрее, чем их соединение с помощью int FK. Но учитывая, что обычно таблицы тегов имеют тенденцию быть небольшими, разница в скорости (в каком направлении) может быть вообще не заметна. Вот еще одна статья, в которой примерно сравниваются оба метода: dba.stackexchange.com/questions/15897/…
исправьте меня, если я ошибаюсь, но когда я удаляю элемент, в строке не будет нулевого значения, и если я удалю все элементы, не будет ли много повторяющегося набора строк, все с нулевыми значениями для itemid?
@deez: Я не совсем понимаю, о чем вы говорите (но рано утром). Если вы удаляете элемент, вы должны удалить вместе с ним теги. В зависимости от фактической реализации базы данных (ограничения внешнего ключа) база данных не позволит вам удалить ее, пока вы не удалите теги.
в моем приложении у меня есть некоторые теги, которые поставляются предварительно загруженными, я не осмеливаюсь удалить эти теги, но я могу выполнить каскадное обнуление, которое оставляет много повторяющихся бесполезных строк, хотя мне нравится идея устранения таблицы сопоставления
Что касается решения Scheintod с двумя таблицами, оно должно работать, но есть несколько ограничений. 1. Если с вашими тегами связаны какие-либо данные (например, если ваш тег имеет описание или некоторые другие данные), для объектов с несколькими тегами нельзя будет последовательно редактировать их метаданные без выполнения большой работы по их синхронизации. 2. При многократном использовании длинных заголовков и тегов требуется дополнительное пространство для хранения. В решении с двумя таблицами заголовок будет вводиться повторно много раз. Возможно, некоторые БД будут оптимизировать данные как часть их хранения / индексации, но я не уверен. В моем случае,
где хранится исходный список тегов? страница в Интернете ?
таким образом вы не можете создавать «неиспользуемые» теги, поэтому для элемента должна выполняться функция «добавить тег». В другом методе функцию «добавить тег» можно выполнить независимо.
Привет, мне интересно, по-прежнему ли вы верите, что решение с двумя таблицами лучше решения с тремя таблицами после всех этих лет?
@Quilang. Я до сих пор верю, что это зависит от того, чем вы занимаетесь :) Я реализовал это обоими способами в разных проектах. В моем последнем случае я получил решение с тремя таблицами, потому что мне нужен «тип тега» (или какая-то другая метаинформация о теге) и я мог повторно использовать некоторый код из близкого родственника тегов: параметры. Но в том же проекте я использовал именно этот метод для еще более близкого родственника: flags (например, «продано», «новое», «горячо»).
Я бы предложил следующий дизайн:
Таблица позиций:
Itemid, taglist1, taglist2
это будет быстро и упростит сохранение и получение данных на уровне элемента.
Параллельно строим еще одну таблицу: Теги тег не создавайте уникальный идентификатор тега, и если у вас закончится место во 2-м столбце, который содержит, скажем, 100 элементов, создайте еще одну строку.
Теперь поиск предметов по тегу будет очень быстрым.
Хорошо, это вопрос №20856, (почти) тот же вопрос №48475 задают по крайней мере через две недели после того, как этот вопрос был задан.