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

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

  • Виджет: WidgetID (PK), название, цена
  • Пользователь: UserID (PK), FirstName, LastName

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

  1. UserWidgets1: UserWidgetID (PK), WidgetID (FK), UserID (FK)
  2. UserWidgets2: WidgetID (PK, FK), UserID (PK, FK)

Вариант 1 имеет один столбец для первичного ключа. Однако в этом нет необходимости, поскольку единственные данные, хранящиеся в таблице, - это отношения между двумя первичными таблицами, и это отношение само по себе может формировать уникальный ключ. Это приводит к варианту 2, который имеет первичный ключ из двух столбцов, но теряет уникальный идентификатор из одного столбца, который имеет вариант 1. Я также мог бы при желании добавить уникальный индекс с двумя столбцами (WidgetID, UserID) в первую таблицу.

Есть ли какая-то реальная разница между этими двумя методами с точки зрения производительности или какая-либо причина предпочесть один подход другому для структурирования таблицы UserWidgets "многие ко многим"?

Необходимые вам индексы продиктованы требованиями вашего запроса, а не дизайном схемы.

dkretz 02.11.2011 11:32
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
14
1
12 772
9
Перейти к ответу Данный вопрос помечен как решенный

Ответы 9

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

Ответ принят как подходящий

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

Выберите вариант 2.

Первичный ключ может быть составным - условия не являются исключительными.

paulmurray 22.03.2009 05:34

@paulmurray: Я считаю, что ответ выше говорит о том, что у вас есть первичный ключ в любом случае, включая случай, когда у вас есть составной ключ. Вам было что добавить к этому?

Apocalisp 22.03.2009 08:32

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 09.05.2016 22:09

@crush - да, это было бы уникально, но создание ограничения для составного ключа неустойчиво / непоследовательно на разных платформах. Я предпочитаю быть ясным и последовательным. В каждой таблице есть столбец идентификаторов.

Guy 12.05.2016 12:28

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

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

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

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

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

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

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

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

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

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

Другие вопросы по теме