У меня есть следующие таблицы в моей базе данных, которые имеют отношение «многие ко многим», которое выражается соединительной таблицей, имеющей внешние ключи к первичным ключам каждой из основных таблиц:
Предположим, что каждая комбинация пользователь-виджет уникальна. Я вижу два варианта того, как структурировать соединительную таблицу, определяющую отношения данных:
Вариант 1 имеет один столбец для первичного ключа. Однако в этом нет необходимости, поскольку единственные данные, хранящиеся в таблице, - это отношения между двумя первичными таблицами, и это отношение само по себе может формировать уникальный ключ. Это приводит к варианту 2, который имеет первичный ключ из двух столбцов, но теряет уникальный идентификатор из одного столбца, который имеет вариант 1. Я также мог бы при желании добавить уникальный индекс с двумя столбцами (WidgetID, UserID) в первую таблицу.
Есть ли какая-то реальная разница между этими двумя методами с точки зрения производительности или какая-либо причина предпочесть один подход другому для структурирования таблицы UserWidgets "многие ко многим"?
Поскольку каждая комбинация пользователь-виджет уникальна, вы должны представить ее в своей таблице, сделав комбинацию уникальной. Другими словами, выберите вариант 2. В противном случае у вас могут быть две записи с одними и теми же идентификаторами виджета и пользователя, но с разными идентификаторами виджета пользователя.
В любом случае у вас есть только один первичный ключ. Второй - так называемый составной ключ. Нет веских причин для введения нового столбца. На практике вам придется сохранить уникальный индекс для всех ключей-кандидатов. Добавление нового столбца не приносит вам ничего, кроме накладных расходов на обслуживание.
Выберите вариант 2.
Первичный ключ может быть составным - условия не являются исключительными.
@paulmurray: Я считаю, что ответ выше говорит о том, что у вас есть первичный ключ в любом случае, включая случай, когда у вас есть составной ключ. Вам было что добавить к этому?
Userwidgetid в первой таблице не нужен, поскольку, как вы сказали, уникальность происходит от комбинации widgetid и идентификатора пользователя.
Я бы использовал вторую таблицу, сохранил внешние ключи и добавил уникальный индекс для widgetid и userid.
Так:
userwidgets( widgetid(fk), userid(fk), unique_index(widgetid, userid) )
Отсутствие дополнительного первичного ключа дает некоторый выигрыш в производительности, поскольку базе данных не требуется вычислять индекс для ключа. В приведенной выше модели этот индекс (через unique_index) все еще вычисляется, но я считаю, что это легче понять.
В чем преимущество первичного ключа в этом сценарии? Рассмотрим вариант без первичного ключа: UserWidgets3: WidgetID (FK), UserID (FK)
Если вам нужна уникальность, используйте либо составной ключ (UserWidgets2), либо ограничение уникальности.
Обычное преимущество использования первичного ключа в производительности заключается в том, что вы часто запрашиваете таблицу по первичному ключу, что происходит быстро. В случае таблиц типа "многие ко многим" вы обычно не запрашиваете по первичному ключу, поэтому нет преимущества в производительности. Таблицы "многие ко многим" запрашиваются по их внешним ключам, поэтому вам следует подумать о добавлении индексов для WidgetID и UserID.
Вариант 2 - правильный ответ, если у вас нет действительно веской причины для добавления суррогатного числового ключа (что вы сделали в варианте 1).
Столбцы суррогатных цифровых ключей не являются «первичными ключами». Первичные ключи технически представляют собой одну из комбинаций столбцов, которые однозначно идентифицируют запись в таблице.
Любой, кто создает базу данных, должен прочитать эту статью http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-i-7327 Джоша Беркуса, чтобы понять разницу между столбцами суррогатных числовых ключей и первичными ключами.
По моему опыту, единственная реальная причина для добавления суррогатного числового ключа в вашу таблицу заключается в том, что ваш первичный ключ является составным ключом и должен использоваться в качестве ссылки на внешний ключ в другой таблице. Только после этого стоит даже подумать о добавлении в таблицу лишнего столбца.
Всякий раз, когда я вижу структуру базы данных, в которой каждая таблица имеет столбец «id», скорее всего, она была разработана кем-то, кто не ценит реляционную модель, и она неизменно отображает одну или несколько проблем, указанных в статье Джоша.
Я согласен с предыдущими ответами, но хочу добавить одно замечание. Если вы хотите добавить больше информации в отношение и разрешить больше отношений между одними и теми же двумя объектами, вам нужен первый вариант.
Например, если вы хотите отслеживать все случаи, когда пользователь 1 использовал виджет 664 в таблице userwidget, идентификатор пользователя и widgetid больше не уникальны.
Лично у меня бы есть столбец синтетического / суррогатного ключа в таблицах "многие ко многим" по следующим причинам:
Синтетический ключ не является заменой естественного / составного ключа и не становится PRIMARY KEY
для этой таблицы только потому, что это первый столбец в таблице, поэтому я частично согласен со статьей Джоша Беркуса. Однако я не согласен с тем, что естественные ключи всегда являются хорошими кандидатами для PRIMARY KEY's
и, конечно, не должны использоваться, если они будут использоваться в качестве внешних ключей в других таблицах.
Я понимаю, что на это был дан ответ давным-давно, но разве составной ключ по-прежнему не будет уникальной ссылкой на отдельную строку для родительской таблицы (ваш пункт 2)?
@crush - да, это было бы уникально, но создание ограничения для составного ключа неустойчиво / непоследовательно на разных платформах. Я предпочитаю быть ясным и последовательным. В каждой таблице есть столбец идентификаторов.
Вариант 2 использует простой ключ compund, вариант 1 использует суррогатный ключ. Вариант 2 предпочтителен в большинстве сценариев и близок к творческой модели в том, что это хороший кандидатный ключ.
Бывают ситуации, когда вы можете захотеть использовать суррогатный ключ (вариант 1)
Как показывает практика, при моделировании данных вы обнаружите, что большинство ассоциативных сущностей (многие ко многим) являются результатом события. Человек устраивается на работу, товар добавляется в корзину и т. д. Большинство событий имеют временную зависимость от события, где важны дата или время - и в этом случае суррогатный ключ может быть лучшей альтернативой.
Итак, возьмите вариант 2, но убедитесь, что у вас есть полная модель.
Я бы пошел с обоими.
Выслушайте меня:
Составной ключ, очевидно, является хорошим и правильным способом отражения смысла ваших данных. Нет вопросов.
Однако: у меня были всевозможные проблемы с правильной работой спящего режима, если вы не использовали один сгенерированный первичный ключ - суррогатный ключ.
Поэтому я бы использовал логические и физические данные модель. У логической есть составной ключ. Физическая модель, реализующая логическую модель, имеет суррогатный ключ и внешние ключи.
Необходимые вам индексы продиктованы требованиями вашего запроса, а не дизайном схемы.