Отношение "многие ко многим": используйте ассоциативную таблицу или разделительные значения в столбце?

Обновление 2009.04.24

Главное в моем вопросе - не путаница разработчиков и что с этим делать.

Цель состоит в том, чтобы понять, когда правильные решения являются правильными.

Я видел данные с разделителями, используемые в коммерческих продуктовых базах данных (Ektron lol).

SQL Server даже имеет тип данных XML, поэтому его можно использовать с той же целью, что и поля с разделителями.

/end Обновление

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

Здесь пример структуры БД:

Document
---------------
ID (PK)
Title
CategoryIDs (varchar(4000))


Category
------------
ID (PK)
Title

Между документом и категорией существует взаимосвязь "многие-ко-многим".

В этой реализации Document.CategoryID - это большой список идентификаторов категорий, разделенных по строкам.

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

С этой моделью, чтобы получить все документы для категории, вам понадобится следующее:

select * from documents where categoryids like '%|' + @targetCategoryId + '|%'

Мое решение - создать ассоциативную таблицу следующим образом:

Document_Category
-------------------------------
DocumentID (PK)
CategoryID (PK)

Это смущает разработчиков. Есть ли какое-то элегантное альтернативное решение, которое мне не хватает?

Я предполагаю, что в документе будут тысячи строк. Категория может быть примерно 40 строк. Основная проблема - производительность запросов. Могу ли я переоценить это?

Есть ли случай, когда он предпочитает хранить списки идентификаторов в столбцах базы данных вместо того, чтобы выталкивать данные в ассоциативную таблицу?

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

Спасибо.

Ответ 1

Таблица Document_Category в вашем дизайне, безусловно, является правильным способом подойти к проблеме. Если это возможно, я бы посоветовал вам обучать разработчиков вместо того, чтобы придумать субоптимальное решение (и получить удар производительности и не иметь ссылочной целостности).

Другие параметры могут зависеть от используемой вами базы данных. Например, в SQL Server вы можете иметь столбец XML, который позволит вам хранить ваш массив в заранее определенной схеме, а затем делать соединения на основе содержимого этого поля. Другие системы баз данных могут иметь что-то подобное.

Ответ 2

Это запутывает разработчиков.

Усовершенствуйте разработчиков. Это правильный подход.

Ответ 3

Ваше предложение - это элегантное, мощное, лучшее решение.

Так как я не думаю, что другие ответы сказали следующее достаточно сильно, я собираюсь это сделать.

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

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

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

Я надеюсь, что этот ответ очень поможет вам.

Обновление

Главное в моем вопросе - не путаница разработчиков и что с этим делать.

Цель состоит в том, чтобы понять, когда правильные решения являются правильными.

Ограниченные значения являются неправильным решением, за исключением крайне редких случаев. Когда индивидуальные значения когда-либо будут запрашиваться/вставлены/удалены/обновлены, это доказывает, что это было неправильное решение, потому что вам нужно разобрать и коснуться всех других значений только для работы с нужным. Поступая таким образом, вы нарушаете нормальную форму first (!!!) (эта фраза должна звучать для вас, как невероятно мерзкий эклектизм). Использование XML для выполнения того же самого тоже неверно. Сохранение ограниченных значений или многозначных XML в столбце может иметь смысл, когда оно рассматривается как неделимая и непрозрачная "сумка свойств", которая НЕ запрашивается базой данных, но всегда отправляется целиком другому потребителю (возможно, веб-сервер или EDI).

Это возвращает меня к моему первоначальному комментарию. Разработчики, которые считают, что нарушают первую нормальную форму, являются хорошей идеей, очень неопытные разработчики в моей книге.

Я расскажу о некоторых довольно сложных нереляционных реализациях хранения данных там, используя пакеты свойств текста (например, Facebook (?) и другие многомиллионные пользовательские сайты, работающие на тысячах серверов). Ну, когда ваша база данных, пользовательская база и транзакции в секунду достаточно велики для этого, у вас будут деньги для ее разработки. Тем временем придерживайтесь лучших практик.

Ответ 4

Это почти всегда большая ошибка использования идентификаторов, разделенных запятыми.
RDBMS предназначены для хранения отношений.

Ответ 5

Мое решение - создать ассоциативную таблицу следующим образом: запутанные разработчикам

Действительно? это база данных 101, если это сбивает с толку, то, возможно, им нужно отойти от кода, созданного мастером, и изучить некоторую базовую нормализацию БД.

То, что вы предлагаете, является правильным решением!

Ответ 6

Отображаемое вами многозначное сопоставление прекрасно и нормально. Он также позволяет при необходимости добавлять другие данные позже. Например, скажем, вы хотели добавить время добавления категории в документ.

Я бы предложил иметь суррогатный первичный ключ в таблице document_category. И уникальное (documentid, categoryid) ограничение, если это имеет смысл сделать это.

Почему разработчики запутались?

Ответ 7

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

Если вы действительно хотите использовать структуру списка, используйте СУБД, которая их понимает. Примерами таких баз данных будут U2 (Unidata, Universe) СУБД, которые (или были, когда-то давно) на основе СУБД выбора. Скорее всего, будут другие аналогичные поставщики СУБД.

Ответ 8

Это классическая проблема объектно-реляционного сопоставления. Разработчики, вероятно, не являются глупыми, просто неопытными или непривычными для правильного решения. Кричать "3NF!" снова и снова не убедят их в правильном пути.

Я предлагаю вам попросить ваших разработчиков объяснить вам, как они получат количество документов по категориям, используя подход, ограниченный по трубам. Это был бы кошмар, в то время как таблица ссылок делает его довольно простым.

Ответ 9

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

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

Также, как и в одном из моих проектов, разработчики имели таблицу для размещения нескольких значений, но находились под впечатлением, что дублирование этих данных в родительской таблице ускорит производительность. Они были неправы, и их вызвали на него.

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

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

Мы также должны видеть, что люди данных должны постоянно следить за "встроенной" частью наших архитектур данных.

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

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