Натуральные против суррогатных ключей на столах поддержки

Question

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

Но в случае вспомогательных таблиц, что я должен использовать?

Например, в гипотетической таблице "orderStates". Значения в этой таблице недоступны для редактирования (пользователь не может вставлять, изменять или удалять эти значения).

Если вы используете естественный ключ, будут иметь следующие данные:

TABLE ORDERSTATES
{ID: "NEW", NAME: "New"}
{ID: "MANAGEMENT" NAME: "Management"}
{ID: "SHIPPED" NAME: "Shipped"}

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

TABLE ORDERSTATES
{ID: 1 CODE: "NEW", NAME: "New"}
{ID: 2 CODE: "MANAGEMENT" NAME: "Management"}
{ID: 3 CODE: "SHIPPED" NAME: "Shipped"}

Теперь давайте рассмотрим пример: пользователь вводит новый заказ.

В случае, когда используются натуральные ключи, в коде я могу написать это:

newOrder.StateOrderId = "NEW";

С суррогатными ключами вместо этого каждый раз у меня появляется дополнительный шаг.

stateOrderId_NEW = .... I retrieve the id corresponding to the recod code "NEW"

newOrder.StateOrderId = stateOrderId_NEW;

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

Итак, что в этом случае является причиной выбора одного типа ключа по сравнению с другим?

Answer 1

Ответ: это зависит.

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

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

Есть разница, когда вы делаете запрос к таблице заказов и хотите напечатать состояние для каждого заказа. Наличие естественного ключа позволит избежать необходимости повторного объединения, что помогает (хотя и немного).

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

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

Answer 2

В двух словах:

• естественный ключ может привести к меньшему присоединению ¹,
• но также требуют больше места ² (и, следовательно, ухудшают производительность кэша ³).

Здесь нет жестких и быстрых правил. Сначала определите, нужен ли вам такой JOIN вообще, и если да, то стоит ли его устранять, стоит ли платить за это в увеличенном хранилище. Единственный способ сделать это - измерить реалистичные объемы данных.

Кстати, есть и другие соображения в естественных и суррогатных дебатах, такие как...

• каскадные обновления,
• кластеризация,
• ромбовидные зависимости и т. д.

... но они, по большей части, не относятся к вашему делу.

---

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

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

³ строки "толще", поэтому на одной странице базы данных поместится меньше строк. Кэширование обычно осуществляется на уровне страницы.

Answer 3

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

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

В первом примере первичный ключ и поле "имя" имеют одинаковое значение; хотя изменение имени не приведет к изменению первичного ключа (и, следовательно, не повлияет на таблицу, использующую "OrderStates" в качестве внешнего ключа), будет иметь место логическое разъединение - в качестве первичного ключа можно указать "NAME", но значение "Person" ".

Обычно пишут запросы, такие как

select orders.ordname
from orders
inner join orderstatus on orders.status = orderstatus.id
where orderstatus.name = 'NEW'

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

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