Соотношение «многие ко многим»: использовать ассоциативную таблицу или значения с разделителями в столбце?
Обновление 2009.04.24
Суть моего вопроса не в замешательстве разработчиков, а в том, что с этим делать.
Дело в том, чтобы понять, когда значения с разделителями являются правильным решением.
Я видел данные с разделителями, используемые в коммерческих базах продуктов (Ektron lol).
SQL Server даже имеет тип данных XML, поэтому его можно использовать для тех же целей, что и поля с разделителями.
/ конец обновления
Приложение, которое я разрабатываю, имеет отношения многие ко многим. В прошлом я часто использовал ассоциативные таблицы для представления их в базе данных. Это вызвало некоторую путаницу у разработчиков.
Вот пример структуры БД:
Document
---------------
ID (PK)
Title
CategoryIDs (varchar(4000))
Category
------------
ID (PK)
Title
Между документом и категорией существует отношение многие ко многим.
В этой реализации Document.CategoryIDs представляет собой большой разделенный по конвейеру список CategoryID.
Для меня это плохо, потому что это требует использования подстроки соответствия в запросах - которые не могут использовать индексы. Я думаю, что это будет медленно и не будет масштабироваться.
С этой моделью, чтобы получить все документы для категории, вам понадобится что-то вроде следующего:
select * from documents where categoryids like '%|' + @targetCategoryId + '|%'
Мое решение состоит в том, чтобы создать ассоциативную таблицу следующим образом:
Document_Category
-------------------------------
DocumentID (PK)
CategoryID (PK)
Это сбивает с толку разработчиков. Есть ли какое-то элегантное альтернативное решение, которое мне не хватает?
Я предполагаю, что в документе будут тысячи строк. Категория может быть как 40 строк или около того. Основной проблемой является производительность запросов. Я переусердствовал в этом?
Есть ли случай, когда предпочтительнее хранить списки идентификаторов в столбцах базы данных, а не выгружать данные в ассоциативную таблицу?
Учтите также, что нам может понадобиться создать отношения «многие ко многим» между документами. Это предполагает ассоциативную таблицу Document_Document. Это предпочтительный дизайн или лучше хранить связанные идентификаторы документов в одном столбце?
Благодарю.