Всегда используйте ключ, не имеющий коммерческого значения. Это просто хорошая практика.

Обновлено: Я пытался найти ссылку на него в Интернете, но не смог. Однако в 'Паттерны корпоративной архитектуры' [Fowler] есть хорошее объяснение того, почему вы не должны использовать ничего, кроме ключа, не имеющего никакого значения, кроме ключа. Это сводится к тому, что у него должна быть одна работа и только одна работа.

У суррогатного ключа НИКОГДА не будет причин для изменения. Я не могу сказать того же о естественных ключах. Фамилии, адреса электронной почты, номера ISBN - все это может измениться в один прекрасный день.

Я считаю, что в сценарии хранилища данных лучше следовать пути суррогатного ключа. Две причины:

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

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

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

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

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

Думайте о компаниях: вы счастливы, что компания Merkin ведет дела с другими компаниями в Merkia. Вы достаточно умны, чтобы не использовать название компании в качестве первичного ключа, поэтому вы используете уникальный правительственный идентификатор компании Merkia, состоящий из 10 буквенно-цифровых символов. Затем Merkia меняет идентификаторы компании, потому что они думали, что это будет хорошая идея. Ничего страшного, вы используете функцию каскадных обновлений вашего движка db для изменения, которое не должно касаться вас в первую очередь. Позже ваш бизнес расширяется, и теперь вы работаете с компанией во Фридонии. Идентификатор компании Freedonian может содержать до 16 символов. Вам необходимо увеличить первичный ключ идентификатора компании (а также поля внешнего ключа в Order, Issues, MoneyTransfers и т. д.), Добавив поле Country в первичный ключ (также во внешние ключи). Ой! Гражданская война во Фридонии, она расколота на три страны. Название страны вашего сотрудника следует изменить на новое; каскадные обновления приходят на помощь. Кстати, какой у вас первичный ключ? (Country, CompanyID) или (CompanyID, Country)? Последний помогает присоединяться, первый избегает другого индекса (или, возможно, многих, если вы хотите, чтобы ваши заказы также были сгруппированы по странам).

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

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

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

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

3. Скорость: в зависимости от значения и типа PK суррогатный ключ целого числа может быть меньше, что позволяет быстрее индексировать и искать.

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

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

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

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

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

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

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

Бизнес-логика / естественные ключи могут изменяться, но физический ключ таблицы НИКОГДА не должен меняться.

Обе. Ешь свой торт и ешь.

Помните, что в первичном ключе нет ничего особенного, за исключением того, что он помечен как таковой. Это не что иное, как ограничение NOT NULL UNIQUE, а в таблице может быть больше одного.

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

Похоже, что никто еще ничего не сказал в поддержку несуррогатных (я не решаюсь сказать «естественных») ключей. Итак, начнем ...

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

select sum(t.hours)
from timesheets t
where t.dept_code = 'HR'
and t.status = 'VALID'
and t.project_code = 'MYPROJECT'
and t.task = 'BUILD';

против:

select sum(t.hours)
from timesheets t
     join departents d on d.dept_id = t.dept_id
     join timesheet_statuses s on s.status_id = t.status_id
     join projects p on p.project_id = t.project_id
     join tasks k on k.task_id = t.task_id
where d.dept_code = 'HR'
and s.status = 'VALID'
and p.project_code = 'MYPROJECT'
and k.task_code = 'BUILD';

Если кто-то всерьез не считает, что следующее - хорошая идея ?:

select sum(t.hours)
from timesheets t
where t.dept_id = 34394
and t.status_id = 89    
and t.project_id = 1253
and t.task_id = 77;

«Но, - скажет кто-то, - что произойдет, если изменится код для MYPROJECT, VALID или HR?» На что я отвечу: «зачем вам необходимость менять его?» Это не «естественные» ключи в том смысле, что какой-то посторонний орган собирается издать закон, что впредь «ДЕЙСТВИТЕЛЬНЫЙ» должен быть перекодирован как «ХОРОШИЙ». Лишь небольшой процент «естественных» ключей действительно попадает в эту категорию - обычно это SSN и почтовый индекс. Я бы определенно использовал бессмысленный числовой ключ для таких таблиц, как Person, Address - но не для все, который по некоторым причинам, кажется, защищает большинство здесь людей.

См. Также: мой ответ на другой вопрос

Суррогатные ключи могут быть полезны, когда бизнес-информация может изменяться или быть идентичной. В конце концов, названия компаний не обязательно должны быть уникальными для страны. Предположим, вы имеете дело с двумя компаниями под названием Smith Electronics: одним в Канзасе и одним в Мичигане. Вы можете отличить их по адресу, но это изменится. Даже состояние может измениться; что, если Smith Electronics из Канзас-Сити, штат Канзас, перейдет через реку в Канзас-Сити, штат Миссури? Нет очевидного способа отделить эти предприятия с помощью естественной ключевой информации, поэтому суррогатный ключ очень полезен.

Думайте о суррогатном ключе как о номере ISBN. Обычно вы определяете книгу по названию и автору. Однако у меня есть две книги под названием «Перл-Харбор» Х. П. Уиллмотта, и это определенно разные книги, а не просто разные издания. В таком случае я мог бы сослаться на внешний вид книг, или более ранний по сравнению с более поздним, но это также хорошо, что у меня есть ISBN, на который можно опираться.

В случае базы данных на определенный момент времени лучше всего иметь комбинацию суррогатных и естественных ключей. например вам необходимо отслеживать информацию о членах клуба. Некоторые атрибуты члена никогда не меняются. например, Дата рождения, но имя может измениться. Итак, создайте таблицу Member с суррогатным ключом member_id и получите столбец для DOB. Создайте еще одну таблицу с именем person name и добавьте столбцы для member_id, member_fname, member_lname, date_updated. В этой таблице естественным ключом будет member_id + date_updated.

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

Вот мои причины:

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

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

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

4. В цепочках отношений от одного до многих - логические цепочки ключей. Так, например, в организациях много учетных записей, а в учетных записях много счетов-фактур. Таким образом, логическим ключом организации является OrgName. Логический ключ учетных записей - OrgName, AccountID. Логическим ключом счета-фактуры является OrgName, AccountID, InvoiceNumber.

   Когда используются суррогатные ключи, цепочки ключей усекаются только за счет наличия внешнего ключа для непосредственного родителя. Например, в таблице «Счет-фактура» нет столбца OrgName. В нем есть только столбец для AccountID. Если вы хотите найти счета-фактуры для данной организации, вам нужно будет присоединиться к таблицам «Организация», «Учетная запись» и «Счет-фактура». Если вы используете логические ключи, вы можете напрямую запросить таблицу организации.

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

6. У меня есть три разные книги по базам данных. Ни один из них не показывает использование суррогатных ключей.

Лошадь для курсов. Чтобы заявить о моей предвзятости; Я в первую очередь разработчик, поэтому меня в основном беспокоит предоставление пользователям работающего приложения.

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

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

Большинство традиционных разработчиков PL / SQL, с которыми я работал, не любили суррогатные ключи из-за количества таблиц на соединение, но наши тестовые и производственные базы данных никогда не вызывали беспокойства; дополнительные соединения не повлияли на производительность приложения. С диалектами базы данных, которые не поддерживают такие предложения, как «внутреннее соединение X Y на Xa = Yb», или разработчиками, которые не используют этот синтаксис, дополнительные соединения для суррогатных ключей действительно затрудняют чтение запросов, их более длительный ввод и проверьте: см. сообщение @Tony Andrews. Но если вы используете ORM или любой другой фреймворк для генерации SQL, вы этого не заметите. Слепой набор также смягчает.

Напоминаем, что не рекомендуется размещать кластеризованные индексы на случайных суррогатных ключах, то есть идентификаторах GUID, которые читают XY8D7-DFD8S, поскольку SQL Server не имеет возможности физически сортировать эти данные. Вместо этого вам следует разместить уникальные индексы для этих данных, хотя может быть также полезно просто запустить профилировщик SQL для основных операций с таблицами, а затем поместить эти данные в помощник по настройке ядра СУБД.

См. Ветку @ http://social.msdn.microsoft.com/Forums/en-us/sqlgetstarted/thread/27bd9c77-ec31-44f1-ab7f-bd2cb13129be

Случай 1: Ваша таблица - это Справочная таблица с менее чем 50 записями (50 типов)

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

Например:

Table: JOB with 50 records
CODE (primary key)       NAME               DESCRIPTION
PRG                      PROGRAMMER         A programmer is writing code
MNG                      MANAGER            A manager is doing whatever
CLN                      CLEANER            A cleaner cleans
...............
joined with
Table: PEOPLE with 100000 inserts

foreign key JOBCODE in table PEOPLE
looks at
primary key CODE in table JOB

Случай 2: Ваш стол - таблица с тысячами записей

Используйте суррогатные / автоинкрементные ключи.

Например:

Table: ASSIGNMENT with 1000000 records
joined with
Table: PEOPLE with 100000 records

foreign key PEOPLEID in table ASSIGNMENT
looks at
primary key ID in table PEOPLE (autoincrement)

В первом случае:

• Вы можете выбрать всех программистов в таблице PEOPLE без использования соединения с таблицей JOB, но только с: SELECT * FROM PEOPLE WHERE JOBCODE = 'PRG'

Во втором случае:

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

Возможно, это не совсем относится к этой теме, но суррогатные ключи у меня головная боль. Предварительно поставленная аналитика Oracle создает автоматически сгенерированные SK для всех своих таблиц измерений на складе, а также сохраняет их на основе фактов. Таким образом, каждый раз, когда их (измерения) необходимо перезагружать по мере добавления новых столбцов или их заполнения для всех элементов в измерении, SK, назначенные во время обновления, не синхронизируют SK с исходными значениями, сохраненными в факте, что вынуждает полная перезагрузка всех присоединяющихся к нему таблиц фактов. Я бы предпочел, чтобы даже если бы SK был бессмысленным числом, был бы какой-то способ, которым он не мог измениться для исходных / старых записей. Как многие знают, готовые решения редко удовлетворяют потребности организации, и мы должны постоянно настраиваться. Теперь у нас есть данные за 3 года на нашем складе, и полная перезагрузка из финансовых систем Oracle очень велика. Итак, в моем случае они не создаются на основе ввода данных, а добавляются в хранилище, чтобы помочь в отчетности производительности. Я понимаю, но наши действительно меняются, и это кошмар.

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

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

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

Суррогатные ключи:

1. Источник проблем с производительностью:
   • Обычно они реализуются с использованием автоматически увеличивающихся столбцов, что означает:
     • Обращение к базе данных каждый раз, когда вы хотите получить новый идентификатор (я знаю, что это можно улучшить с помощью кэширования или подобных алгоритмов [seq] hilo, но все же у этих методов есть свои недостатки).
     • Если в один прекрасный день вам нужно переместить данные из одной схемы в другую (по крайней мере, в моей компании это происходит довольно регулярно), вы можете столкнуться с проблемами коллизии идентификаторов. И да, я знаю, что вы можете использовать UUID, но для этого требуется 32 шестнадцатеричных цифры! (Если вам важен размер базы данных, это может быть проблемой).
     • Если вы используете одну последовательность для всех своих суррогатных ключей, тогда - наверняка - вы столкнетесь с конфликтом в своей базе данных.
2. Склонность к ошибкам. У последовательности есть ограничение max_value, поэтому как разработчик вы должны обратить внимание на следующие моменты:
   • Вы должны циклически повторять последовательность (когда достигается максимальное значение, оно возвращается к 1,2, ...).
   • Если вы используете последовательность в качестве упорядочения (во времени) ваших данных, вы должны обработать случай циклического изменения (столбец с идентификатором 1 может быть новее, чем строка с максимальным значением идентификатора - 1).
   • Убедитесь, что ваш код (и даже ваши клиентские интерфейсы, которых не должно происходить, поскольку это должен быть внутренний идентификатор) поддерживает целые числа 32b / 64b, которые вы использовали для хранения значений вашей последовательности.
3. Они не гарантируют отсутствие дублирования данных. Вы всегда можете иметь 2 строки со всеми одинаковыми значениями столбцов, но с другим сгенерированным значением. Для меня это проблема суррогатных ключей THE с точки зрения дизайна базы данных.
4. Подробнее в Википедии ...

Мифы о природных ключах

1. Составные ключи менее неэффективны, чем суррогатные ключи. Нет! Это зависит от используемого движка базы данных:
   • Oracle
   • MySQL
2. Естественных ключей в реальной жизни не существует. Извините, но они существуют! В авиационной отрасли, например, следующий кортеж всегда будет уникальным в отношении данного рейса по расписанию (авиакомпания, отправлениеDate, flightNumber, operatingSuffix). В более общем смысле, когда набор бизнес-данных гарантированно уникален заданным стандарт, тогда этот набор данных является [хорошим] кандидатом на естественный ключ.
3. Естественные ключи «загрязняют схему» дочерних таблиц. Для меня это больше чувство, чем реальная проблема. Первичный ключ из 4 столбцов по 2 байта может быть более эффективным, чем один столбец из 11 байтов. Кроме того, 4 столбца можно использовать для прямого запроса дочерней таблицы (используя 4 столбца в предложении where) без присоединения к родительской таблице.

Вывод

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

Надеюсь, что это кому-то помогло!