От многих до многих взаимосвязей - Дизайн таблицы пересечений

Мне интересно, какой лучший дизайн для таблицы пересечений для отношения "многие ко многим".

Два подхода, которые я рассматриваю, следующие:

CREATE TABLE SomeIntersection 
(
     IntersectionId UNIQUEIDENTIFIER PRIMARY KEY,
     TableAId UNIQUEIDENTIFIER REFERENCES TableA NOT NULL,
     TableBId UNIQUEIDENTIFIER REFERENCES TableB NOT NULL,
     CONSTRAINT IX_Intersection UNIQUE(TableAId, TableBId )
)

или

CREATE TABLE SomeIntersection 
(
     TableAId UNIQUEIDENTIFIER REFERENCES TableA NOT NULL,
     TableBId UNIQUEIDENTIFIER REFERENCES TableB NOT NULL,
     PRIMARY KEY(TableAId, TableBId )
)

Есть ли преимущества для одного над другим?
EDIT 2: **** Обратите внимание: Я планирую использовать Entity Framework для предоставления API для базы данных. Имея это в виду, помогает ли одно решение с EF лучше, чем другое?

РЕДАКТИРОВАТЬ: В соответствующей заметке для таблицы пересечений, что два столбца ссылаются на одну и ту же таблицу (пример ниже), существует ли способ, чтобы два поля отличались от записи?

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

Я хочу предотвратить следующие

ParentRecord          ChildRecord
=================================
      1                    1         --Cyclical reference!

Ответы

Ответ 1

Вторая версия - лучшая для меня, она избегает создания дополнительного индекса.

Ответ 2

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

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

Ответ 3

Используйте version-1, если ваше "пересечение" фактически является сущностью по отдельности, что означает:

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

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

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

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

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

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

Ответ 4

То, что вы строите, называется "пересечением".

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

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

Ответ 5

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

Ответ 6

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

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

Ответ 7

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

Правило, конечно, но там вы идете.

Ответ 8

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

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

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

Ответ 9

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

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

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

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

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

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

Ответ 10

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

Ответ 11

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