Не могу определиться с вариантом организации связи таблиц-сущностей с тегами:
1 ) Есть несколько таблиц-сущностей предметной области, каждая из которых (сущность) имеет набор тегов
2 ) Теги хранятся в отдельной таблице и имеют свой ID

Возможны 2 варианта организации связи таблиц-сущностей (М:М) с тегами:
1 ) отдельные таблицы связей для каждого вида сущностей: (entity1_id, tag_id) , (entity2_id, tag_id) , ...
2 ) общая таблица связей: (entity_type, entity_id, tag_id)

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

Возникает вопрос, нужен ли такой общий поиск, поскольку в этом случае для сортировки и фильтрации общего списка (как минимум, фильтрация будет выполняться всегда ) всё равно придётся JOIN'ить таблицы сущностей и задавать условия по полям этих сущностей - данный фактор сводит на нет озвученное преимущество общей таблицы и почти уравнивает его с вариантом отдельных таблиц связей, когда поиск тега сразу для нескольких сущностей будет реализовываться несколькими поисками для отдельных сущностей с теми же фильтрациями и сортировками по полям сущностей.

авторВыглядит так будто анализ предметной области проведён криво и выделение сущностей не
доведено до логического конца.Факт наличия более одной сущности, имеющей теги, абсолютно ни о чём не говорит. Имхо, обычное явление.

авторСкорее всего у тебя должна быть только одна сущность, обложенная тегами и как результат -
связь всего двух таблиц M:N.
При определении того, нужно ли оформлять сущности отдельными таблицами или общей таблицей, фактор наличия тегов (равно как и фактор наличия текстового контента) не всегда играет решающую роль. В данном случае:

1 ) просто семантически сущности разные (обычно на форумах обсуждают такие сущности, как новости и статьи )
2 ) большинство (ни или половина) полей/характеристик у этих сущностей - свои индивидуальные (в общей таблице будет много "дырок" с NULL, что не есть хорошо)
3 ) чем меньше данных в таблице, тем быстрее работает выборка по индексам
4 ) чем меньше индексов в таблице - тем быстрее выполняются операции вставки/удаления/обновления

Здесь может стоять вопрос вынесения общих семантически равнозначных полей в отдельную таблицу (в данном случае - текстовый контент), и уже с этой таблицей связывать теги. И снова несколько но:

1 ) дублирование поля контента в каждой сабжевой таблице-сущности позволит избежать лишнего JOIN при выборке без создания избыточности (дублирования) данных
2 ) теги могут иметь и сущности без текстового контента (если и не сейчас, то в будущем)
3 ) не все сущности с текстовым контентом имеют теги

P.S . Таким образом, сабжевый вопрос остаётся в силе...

Вообще, сущностей в нашей предметной области много. Несколько из них имеют текстовый контент и теги. Другие несколько - тоже имеют какие-то семантически идентичные или похожие характеристики, и т.д. И все эти группы сущностей могут пересекаться. Выделение общих таблиц для таких групп сущностей возможно, но это усложняет и процесс проектирования БД, и процесс программирования логики. Но это не главное. Главные причины - это те, что я написал в предыдущем сообщении.

Dimitry Sibiryakov Хоть Вы так и говорите, но, похоже, для себя уже всё решили.
По поводу организации сабжевых сущностей в отдельных таблицах - да, решил (и привёл аргументы)
Но по вопросу организации их связей с тегами - нет.

Dimitry Sibiryakov Кривая архитектура БД вылезет позже в виде геморроя с запросами тормозов. Тогда и переделаете.
Я предпочитаю оперировать конструктивными доводами, а не ярлыками. На текущий момент вы не озвучили ни вашего варианта организации сущностей с контентом и тегами (общая таблица с "дырками", отдельные таблицы, как у меня сейчас, или иной вариант), ни каких-либо доводов против варианта с отдельными таблицами-сущностями .

Почему вы считаете, что вариант с отдельными таблицами-сущностями приведёт к "геморрою с запросами" и тормозам ? Отсутствие дополнительного JOIN - это разве приведёт к тормозам и сделает запрос сложнее ? Отсутствие "дырок" и малое количество индексов в одной таблице - это разве усложнит запросы и приведёт к тормозам ?

Со своей стороны я привёл доводы в 3-м посте. Если вы приведёте везкие контраргументы , я могу и переделать архитектуру . Но на текущий момент таких доводов нет, а значит, нет объективных причин считать текущую архитектуру "кривой".

"Опыт - сын ошибок трудных." (с)
Значит, аргументов не будет ?

Отсутствие дополнительного JOIN - это разве приведёт к тормозам и сделает запрос сложнее ?
Отсутствие "дырок" и малое количество индексов в одной таблице - это разве усложнит запросы и приведёт к тормозам ?

stenfordзвучит как классическая задача с наследованием, там 3 подхода - все в одну таблицу, по таблице на каждый подтип и общие характеристики вынесены в таблицу супертипа, по таблице на каждый подтип со всеми характеристиками. Что использовать зависит от конкретного случая, сколько общих аттрибутов, сколько разных, какие операции над ними преобладают итп. Именно. И каждый из вариантов имеет место в проектах:
автор таблица на тип (TPT)
каждый класс имеет свою собственную таблицу. Базовый класс имеет все элементы базового класса в нем, и каждый класс, производный от него, имеет свою собственную таблицу с первичным ключом, который также является внешним ключом к таблице базового класса; класс производной таблицы содержит только различные элементы.
таблица на иерархию (TPH)
существует одна таблица, которая представляет всю иерархию наследования, что означает, что несколько столбцов, вероятно, будут разреженными. Добавляется столбец дискриминатора, который сообщает системе, какой тип строки это.
таблица-на-класс (TPC)
каждый класс имеет свою собственную полностью сформированную таблицу без ссылок на любые другие таблицы.Вариант, предлагаемый Dimitry Sibiryakov , - это либо TPT , либо TPH (какого именно варианта он придерживается, пока неизвестно, но мы терпеливые).
По скорости выборки лидируют варианты TPH и TPC , по скорости добавления/удаления/обновления - TPC (меньше индексов). По минимальному объёму занимаемого дискового пространства лидирует TPC , за ним следует TPT. Как мы видим, по всем 3 озвученным критериям лидирует вариант TPC , на котором мы и остановились.

Правда, нужно сделать оговорку: нет связей с другими классами-таблицами, участвующих (связей) в организации иерархии. При этом, естественно, есть: связи с таблицами связей М:М, связи с таблицами-неклассами, связи с таблицами-классами, не участвующие (связи) в организации иерархии.

vmagили как уйти от EAV... EAV - это существенное упрощение архитектуры БД за счёт существенного усложнения контроля ссылочной целостности и существенного снижения производительности запросов. Если состав полей постоянен или относительно постоянен, то EAV не нужен. В сабжевом случае EAV и не пахнет.

graycodeДля начала нужно ответить на вопрос зачем нужны теги, как и для чего они используются, какого вида запросы по ним будут.Запросы будут классические:
1 ) " найти все сущности указанного типа, имеющие 1 или несколько указанных тегов "
2 ) " получить список всех тегов указанной сущности "

Запросы вида " найти все сущности нескольких типов, имеющие 1 или несколько указанных тегов" не в приоритете, поскольку в таком списке (по крайней мере, на первых страницах пагинации) может оказаться только одна сущность, тогда как пользователь, пожелавший выполнить поиск по тегам сразу для нескольких сущностей, скорее все захочет уже на 1-й странице пагинации увидеть все виды сущностей. А для реализации такого результата по-любому придётся выполнять отдельные теговые поиски по каждому виду сущностей.

graycodeИ далеко не факт, что использование классических таблиц и индексов лучший подход для запросов по тегам.Что в имеете ввиду под " неклассическим " подходом ?

zeon11 Не знаю, поможет, или нет. Сделал для мелочевки ОДНУ таблицу с иерархией.Насколько я понимаю, это у вас справочники. Только не понял, для чего нужна иерархия - ведь справочники-то независимые, судя по скрину. Да, их можно хранить в отдельной таблице. Но можно и в разных. Если их не огромное количество и они не формируются пользователем, то смысла объединять в одну таблицу нет, т.к. общая таблица справочников усложняет поддержание ссылочной целостности и в целом обслуживание...

авторЯ за одну таблицу. Гораздо проще будет управлять. И кода писать в разы меньше. За одну общую таблицу сущностей или за одну таблицу связей (entity_type, entity_id, tag_id) ?
В нашем случае приоритет имеет скорость операций, нежели чем простота проектирования. Что касается объёма кода, то всегда можно воспользоваться готовой или реализовать собственную ORM или Query Builder , которые скроют логику работы с таблицами.

Ennor TiegaelЯ обычно выбираю второй вариант. Когда этих тэговых связок будет в районе хотя бы 50-ти, и вы решите добавить в них новое поле, вы пожалеете о сделанном выборе.Ручного добавления/удаления/изменения поля легко можно избежать:
1 ) хранимые процедуры или функции, которые делают это автоматически, используя information_schema
2 ) механизм наследования (например, в PostgreSQL): добавляем/изменяем/удаляем поле в базовой таблице - и оно автоматически добавляется/изменяется/удаляется во всех дочерних таблицах

Ennor TiegaelВпрочем, если простота администрирования у вас на предпоследнем месте, сразу перед проектированием, то вперед, конечно. Т.е. вы считаете, что вариант с отдельными таблицами будет работать эффективнее ?

graycodeНе не не, не в приоритете из за сложности имплементации, это очень слабая аргументацияЧто сложность реализации не должна иметь решающего значения - согласен на 100%. Но под словами " не в приоритете " я имел ввиду не сложность реализации, а невостребованность данной функциональной возможности (отображение общего списка сущностей, связанных с указанным тегом). Вместо этого должны будут реализовываться отдельные списки для каждого раздела/типа сущностей (как в тех же социальных сетях).

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

Вопрос №1 . В социальных сетях поиск осуществляется одним запросом по всем разделам или несколькими запросами (по запросу на каждый раздел) ?
Вопрос №2 . В социальных сетях поиск осуществляется только по контенту или в том числе по индивидуальным полям сущностей (название, комментарий, имя вложения/альбома и т.д.) ?

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

Плюс, в обоих случаях мы столкнёмся с "избыточностью" данных, поскольку многие-многие экземпляры сущностей будут иметь одинаковые строковые теги - а это сильно увеличивает потребление дискового пространства. Гораздо экономнее ставить числовые идентификаторы на таблицу тегов.

graycode3. Создать такую же таблицу как во втором пункте, но добавить к ней таблицу с типами сущностей для тегов. 1 ) справочник со списком всех допустимых тегов (tag_id, tag)
2 ) справочник допустимых тегов для каждого типа сущностей (entity_type, tag_id)
Такую схему вы имеете ввиду ?

graycodeУчитывая суть тегов, я бы остановился на первом варианте, т.е. (entity_type, entity_id, tag), поисковые запросы по тегам или тег плюс тип сущности. Т.е. ваш вариант: общая таблица связей для всех типов сущностей, но вместо идентификатора тега указывается строка тега .
Преимущество этого варианта - отсутствие JOIN для связи с таблицей тегов. Недостаток - большое потребление дискового пространства.

Cyrax_02Но если такие отдельные списки будут формироваться одним запросом (что более эффективно) - такой вариант реализуем только с общей таблицей связей, включающей все типы сущностей (разделы).
Вопрос №3 . Как из общей таблицы (entity_type, entity_id, tag) одним запросом (без UNION) получить N сущностей типа 1 , N сущностей типа 2 и N сущностей типа 3 , связанных с указанным тегом ? В социальных сетях получают ))