Суррогат против естественных / бизнес-ключей

Здесь мы снова и снова, старый аргумент все еще возникает …

Был ли у нас лучший бизнес-ключ в качестве первичного ключа, или у нас скорее был бы суррогатный идентификатор (то есть идентификатор SQL Server) с уникальным ограничением в поле бизнес-ключа?

Просьба привести примеры или доказательства для поддержки вашей теории.

И то и другое. Иметь торт и есть его.

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

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

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

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

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

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

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

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

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 и Zip-коды. Я бы определенно использовал бессмысленный цифровой ключ для таблиц, таких как Person, Address, но не для всего , что по некоторым причинам большинство людей здесь, кажется, защищают.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Мифы о естественных ключах

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

Вывод

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 Table: JOB with 50 inserts 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 inserts joined with Table: PEOPLE with 100000 inserts foreign key PEOPLEID in table ASSIGNMENT looks at primary key ID in table PEOPLE (autoincrement) 

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

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

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

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

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

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

  • Лучший способ автоматической привязки данных между базой данных и пользовательским интерфейсом в приложении java swing?
  • Как реализовать отношения «многие ко многим» в PostgreSQL?
  • Какая встроенная firebase database используется в приложении Delphi?
  • Как связать RadioButtons с перечислением?
  • Разница между масштабированием по горизонтали и вертикали для баз данных
  • Что такое EOF на языке программирования C?
  • Как разработать таблицу продуктов для многих видов продуктов, где каждый продукт имеет множество параметров
  • Плюсы / минусы баз данных на основе документов и реляционных баз данных
  • Какова наилучшая схема базы данных для поддержки значений, которые подходят только для определенных строк?
  • Как отображать элементы в Canvas через Binding
  • Поиск k-го наименьшего числа из n отсортированных массивов
  • Давайте будем гением компьютера.