Проектирование базы данных с таблицами без первичного ключа

#база_данных #sql_server #проектирование

Прежде всего вопрос к опытным коллегам : а допускаются ли таблицы без ПК(первичный
ключ) в профессиональной БД? Ситуация такая - есть таблица клиент с разными полями
в том числе ID(которое и есть пк для таблицы) но есть и таблица контактные данные (без
пк) там столбцы состоят из (ID, phone, email, приоритетный способ связи и тд. другие
способы) стоит ли устанавливать связь между этими таблицами и как если стоит? Ведь
номеров может быть много и они могут добавляться.
    

Ответы

Ответ 1

tl;dr;

Да, добавляейте FK. Если у вас одна запись в таблице контактов для каждого кастомера
- делаейте Id (CustomerID) в таблице контактов первичным ключем + кластерным индексом.
Если больше одной для одного заказчика - добавляйте отдельную колонку CustomerDetailsID,
и и делайте PK+CI ее + добавьте некластерный индекс по CustomerID.

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

Длинная версия

Ок, чтобы понять, допустимы ли таблицы без Primary Key, нужно сначала понять что
такое PK и какое отношение он имеет к индексам.

Primary Key и Foreign Key - это, прежде всего, логические концепции. 

PK - это такая колонка (или несколько колонок), которая однозначно идентифицирует
запись. Т.е. одному значению PK соответствует одна запись в текущей таблице. Например,
значение ID в таблице клиентов однозначно идентифицирует запись о клиенте в этой таблице.

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

PK и FK - это свойства самой структуры данных. Чисто теоретически - неважно, поставили
ли вы отметку PK на Customer.ID, и создали ли вы FK ContactDetails.CustomerID -> Customer.ID
- колонки от этого не перестанут идентифицировать записи. Например, в базе данных Team
Foundation Server вообще не проставлены FK - что не мешает ему вполне нормально работать :)



Зачем тогда ставить отметки PK и FK при создании базы в SQL Server? 


Это позволяет SQL Server жестко поддерживать уникальность, защищая вас от ошибок
в данных. Т.е. он просто не даст вам вставить еще одну запись Customer с тем же ID.
И не даст вписать в таблицу ContactDetails запись для несуществующего заказчика.
Это позволяет SQL Server строить запросы более эффективно. Например, при поиске заказчика
по ID он точно будет знать, что найдет не больше одной строки. А не, например, 100500
заказчиков с ID=1. И он выберет соответствующий план запроса, выделит соответствующее
количество памяти под запрос и т.д.




Какое отношение это все имеет к индексам? Дело в том, что для поддержания целостности
PK и FK SQL Server-у необходимы определенные физические структуры в базе данных.

В SQL Server есть два формата хранения таблицы


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




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



Ок, как эти физические структуры соответствуют PK и FK?

Для PK нужна возможность быстро проверить существование и уникальность записи. Поэтому
PK создается или на основе кластерного индекса, или на основе уникального некластерного
индекса. Просто так висеть в воздухе он не может.

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

Тот же Management Studio по одной кнопке создает одновременно и PK и Clustered Index.
Поэтому кластерный индекс и Primary Key считаются чуть ли не синонимами. Хотя на самом
деле есть техническая возможность создать кластерный индекс по одной колонке, а PK
- по другой.

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

Например, при вставке в ContactDetails SQL Server должен проверять, что для вставляемого
значения CustomerID есть соответствующая (и ровно одна!) запись в Customers. Поэтому
для FK со стороны Customer нужен или кластерный индекс по той же колонке, или хотя
бы уникальный ключ. В таблице ContactDetails при этом никаких структур данных ради
этого FK не требуется.


Ответ 2

допускаются ли таблицы без ПК(первичный ключ) в профессиональной БД?


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

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

К слову, если Вы даже не создаёте первичного ключа, не факт, что его нет. СУБД вполне
может ввести (и скорее всего введёт) в структуру скрытое и недоступное Вам поле, которое
будет выполнять роль синтетического первичного ключа, идентифицирующего запись. Например,
СУБД же надо как-то идентифицировать, какой записи таблицы соответствует запись в индексе...


  стоит ли устанавливать связь между этими таблицами


При создании такой связи средствами СУБД (созданием внешнего ключа) Вы переложите
заботу по контролю целостности и непротиворечивости информации на плечи специально
предназначенной для этого службы SQL-сервера. Это убережёт Вас от потенциального нарушения
логической целостности данных, что в общем полезно - если не создавать такую связь
и возложить эту задачу на клиентское ПО, которое гораздо хуже приспособлено для подобных
функций, то можно поиметь ненужные проблемы, особенно в случае нештатных ситуаций.
Так что если логика требует такой связи - её необходимо создать.


Ответ 3

Если правильно понял, то тут имеется ввиду таблица без кластеризованного индекса.
Как правило такие таблицы называются кучами.
Их стоит использовать, например, в следующих случаях:

1) В таблице не будет много записей и нам, при обращении к таблице, нужно делать скан.

2) Если созданы некластеризованные индексы и доступ к данным осуществляется через них.

3) Когда мы знаем что в таблицу будет производиться массовая запись данных.

Есть и другие варианты использования куч. Все зависит от архитектуры проекта.В крупных
проектах такое встречается довольно часто.

Более подробно можно почитать на msdn.