Один или два первичных ключа в таблице «многие ко многим»?

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

Идите по варианту 2.

Лично я предпочел бы столбец синтетического/суррогатного ключа в таблицах "многие ко многим" по следующим причинам:

• Если вы использовали числовые синтетические ключи в своих таблицах сущностей, то их использование в таблицах отношений поддерживает согласованность в дизайне и соглашении об именах.
• В будущем может случиться так, что таблица «многие ко многим» сама станет родительской сущностью для подчиненной сущности, которой требуется уникальная ссылка на отдельную строку.
• На самом деле он не будет использовать столько дополнительного дискового пространства.

Синтетический ключ не заменяет естественный/составной ключ и не становится PRIMARY KEY для этой таблицы только потому, что это первый столбец в таблице, поэтому я частично согласен со статьей Джоша Беркуса. Однако я не согласен с тем, что естественные ключи всегда являются хорошими кандидатами для PRIMARY KEY's и, конечно же, их не следует использовать, если они должны использоваться в качестве внешних ключей в других таблицах.

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

Бывают ситуации, когда вы можете захотеть использовать суррогатный ключ (вариант 1).

1. Вы не утверждаете, что составной ключ является хорошим ключом-кандидатом с течением времени. В частности, с временными данными (данными, которые меняются со временем). Что делать, если вы хотите добавить еще одну строку в таблицу UserWidget с теми же UserId и WidgetId? Подумайте о Employment(EmployeeId,EmployeeId) — это будет работать в большинстве случаев, за исключением случаев, когда кто-то вернется на работу к тому же работодателю позже.
2. Если вы создаете сообщения/бизнес-транзакции или что-то подобное, для интеграции требуется более простой ключ. Может репликация?
3. Если вы хотите создать свои собственные механизмы аудита (или аналогичные) и не хотите, чтобы ключи были слишком длинными.

Как правило, при моделировании данных вы обнаружите, что большинство ассоциативных сущностей (многие ко многим) являются результатом события. Человек устраивается на работу, товар добавляется в корзину и т. д. Большинство событий имеют временную зависимость от события, где важна дата или время — и в этом случае суррогатный ключ может быть лучшей альтернативой.

Итак, выберите вариант 2, но убедитесь, что у вас есть полная модель.

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

Например, если вы хотите отслеживать все случаи, когда пользователь 1 использовал виджет 664 в таблице userwidget, идентификатор пользователя и виджет больше не уникальны.

В чем преимущество первичного ключа в этом сценарии? Рассмотрим вариант без первичного ключа: UserWidgets3: WidgetID (FK), UserID (FK)

Если вам нужна уникальность, используйте либо составной ключ (UserWidgets2), либо ограничение уникальности.

Обычное преимущество в производительности от наличия первичного ключа заключается в том, что вы часто запрашиваете таблицу по первичному ключу, что является быстрым. В случае таблиц «многие ко многим» вы обычно не запрашиваете первичный ключ, поэтому выигрыша в производительности нет. Таблицы «многие ко многим» запрашиваются по их внешним ключам, поэтому вам следует подумать о добавлении индексов для WidgetID и UserID.

Вариант 2 является правильным ответом, если только у вас нет действительно веских причин для добавления суррогатного цифрового ключа (что вы сделали в варианте 1).

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

Любой, кто создает базу данных, должен прочитать эту статью http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-i-7327 Джоша Беркуса, чтобы понять разницу между столбцами суррогатных числовых ключей и первичными ключами.

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

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

Я бы пошел с обоими.

Выслушай меня:

Составной ключ, очевидно, является хорошим и правильным способом отражения смысла ваших данных. Нет вопросов.

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

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

Поскольку каждая комбинация «Пользователь-виджет» уникальна, вы должны отразить это в своей таблице, сделав эту комбинацию уникальной. Другими словами, используйте вариант 2. В противном случае у вас могут быть две записи с одинаковыми идентификаторами виджета и пользователя, но с разными идентификаторами виджета пользователя.

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

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

So:

userwidgets( widgetid(fk), userid(fk),
             unique_index(widgetid, userid)
)

Отсутствие дополнительного первичного ключа дает некоторый выигрыш в производительности, поскольку базе данных не нужно будет вычислять индекс для ключа. В приведенной выше модели хотя этот индекс (через unique_index) все еще рассчитывается, но я считаю, что это проще для понимания.