Многие многим дизайн отношения - перекрестный дизайн таблицы

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

Мы собираемся на несколько раундов бокса голыми кулаками в баре в Нью-Джерси каждые пару месяцев.

Используйте версию 1 , если ваше «пересечение» на самом деле ЯВЛЯЕТСЯ самостоятельной сущностью, что означает:

• у него есть дополнительные свойства
• вы можете искать эти объекты (и не перемещаться по отношению )

Пользователь версия-2 , если это чисто таблица отношений NM. В этом случае также убедитесь, что:

• у вас есть PK (CLUSTERED) с первым столбцом, относящимся к таблице, в которой вы чаще всего выполняете поиск: например, если ваши таблицы - это Person-Address, тогда я предполагаю, что вы будете искать все-адреса-лица чаще, чем все-люди-по-этому-адресу. Таким образом, вы должны сначала указать свой PK, чтобы включить PersonID

• , вы все равно можете иметь еще один UNIQUE-идентификатор из одного столбца, но просто:

  1. либо не превращайте его в PK
  2. , либо делайте его PK, но укажите NON CLUSTERED , так что вы можете использовать CLUSTERED для индекса UNIQUE, охватывающего два ссылочных столбца
  3. , если вы не используете GUID в своей конструкции, вы можете затем придерживаться типа столбца INT IDENTITY

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

Отвечая на второй вопрос ...

Вам просто нужно добавить ограничение проверки, например:

CREATE TABLE SomeIntersection 
(
     ParentRecord INT REFERENCES TableA NOT NULL,
     ChildRecord INT REFERENCES TableA NOT NULL,
     PRIMARY KEY(TableAId, TableBId),
     CHECK (ParentRecord <> ChildRecord)
)

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

То, что вы строите, называется «Перекресток».

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

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

если вы выберете первый, просто используйте IDENTITY на ПК, вам не нужно тратить пространство (диск и кеш памяти) с помощью UNIQUEIDENTIFIER.

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

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

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

Практическое правило, конечно, но готово.

Преимущества первого:

Возможность поиска в таблице соединений по одному значению. Это упрощает некоторые операции поиска на стороне клиента.

Некоторым ORM (NHibernate) также требуются идентификаторы в каждой сущности, чтобы постоянство работало должным образом.

Преимущества второго:

Более простая модель данных, без необходимости создания дополнительного индекса.

Требуется меньше памяти.

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