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

<p>здесь мы идем снова, старый аргумент все еще возникает ...</p><p>должны ли мы иметь бизнес-ключ в качестве первичного ключа или лучше иметь суррогатный идентификатор (т. е. идентификатор sql server) с уникальным ограничением на поле бизнес-ключа?</p><p>пожалуйста, предоставьте примеры или доказательства в поддержку вашей теории.</p>

<p>и то и другое. возьми свой пирог и съешь его.</p><p>помните, что в первичном ключе нет ничего особенного, кроме того, что он помечен как таковой. это не более чем ограничение not null unique, и таблица может иметь более одного.</p><p>если вы используете суррогатный ключ, вы все равно хотите, чтобы бизнес-ключ гарантировал уникальность в соответствии с бизнес-правилами.</p>

<p>несколько причин использовать суррогатные ключи:</p><ol> <li> <p><strong>стабильность</strong>: изменение ключа из-за деловой или естественной потребности негативно повлияет на связанные таблицы. суррогатные ключи редко, если вообще когда-либо, нужно менять, потому что нет значения, связанного со значением.</p> </li> <li> <p><strong>соглашение</strong>: позволяет вам иметь стандартизированное соглашение об именах столбцов первичного ключа, а не думать о том, как объединять таблицы с различными именами для их pk.</p> </li> <li> <p><strong>скорость</strong>: в зависимости от значения и типа pk суррогатный ключ целого числа может быть меньше, быстрее индексировать и искать.</p> </li> </ol>

<p>похоже, что никто еще ничего не сказал в поддержку несуррогатных (я стесняюсь говорить "естественных") ключей. так что здесь идет ...</p><p>недостатком <strong> </strong> суррогатных ключей является то, что они <strong> не имеют смысла </strong> (цитируется как преимущество некоторых, но ...). это иногда вынуждает вас присоединять к вашему запросу гораздо больше таблиц, чем это действительно необходимо. для сравнения:</p><pre><code>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'; </code></pre><p>против:</p><pre><code>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'; </code></pre><p>разве кто-нибудь всерьез считает, что следующая идея - хорошая идея?</p><pre><code>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; </code></pre><p>"но, - скажет кто-то, - что произойдет, когда код для myproject, valid или hr изменится?" на что мой ответ будет таким: "зачем вам <strong> нужно </strong> его менять?" это не "естественные" ключи в том смысле, что какой-то внешний орган собирается издать закон о том, что впредь "действительно" следует перекодировать как "хорошо". только небольшой процент "естественных" ключей действительно попадает в эту категорию - обычными примерами являются ssn и zip-код. я бы определенно использовал бессмысленный цифровой ключ для таблиц, таких как person, address - но не для <strong> всего </strong>, что, по некоторым причинам, большинство людей здесь защищают.</p><p>смотрите также: <a href="https://stackoverflow.com/questions/337503/whats-the-best-practice-for-primary-keys-in-tables/337716#337716">мой ответ на другой вопрос</a> </p>

<p>суррогатные ключи (как правило, целые числа) имеют дополнительную ценность, заключающуюся в том, чтобы сделать ваши табличные отношения более быстрыми и более экономичными с точки зрения хранения и скорости обновления (даже лучше, внешние ключи не нужно обновлять при использовании суррогатных ключей, в отличие от полей бизнес-ключей, которые меняются сейчас и потом).</p><p>первичный ключ таблицы должен использоваться для уникальной идентификации строки, главным образом для целей объединения. подумайте о персоне: имена могут меняться, и они не гарантированно уникальны.</p><p>think companies: вы - счастливая компания merkin, которая ведет дела с другими компаниями в merkia. вы достаточно умны, чтобы не использовать название компании в качестве первичного ключа, поэтому вы используете уникальный идентификатор компании правительства merkia из 10 буквенно-цифровых символов. затем merkia меняет идентификационные данные компании, потому что они думали, что это будет хорошей идеей. ничего страшного, вы используете функцию каскадных обновлений вашего db-движка для изменений, которые не должны вас привлекать. позже ваш бизнес расширяется, и теперь вы работаете с компанией во фридонии. идентификатор компании freedonian - до 16 символов. вам необходимо увеличить первичный ключ идентификатора компании (также поля внешнего ключа в заказах, выпусках, moneytransfers и т.д.), добавив поле страна в первичном ключе (также во внешних ключах). ой! гражданская война во фридонии раскололась на три страны. название страны вашего сотрудника должно быть изменено на новое; каскадные обновления на помощь. кстати, каков твой первичный ключ? (страна, companyid) или (companyid, страна)? последний помогает объединениям, первый избегает другого индекса (или, возможно, многих, если вы хотите, чтобы ваши заказы также группировались по странам).</p><p>все это не является доказательством, но указывает на то, что суррогатный ключ для уникальной идентификации строки для всех применений, включая операции соединения, предпочтительнее бизнес-ключа.</p>

<p>суррогатный ключ никогда не будет иметь причины для изменения. я не могу сказать то же самое о естественных ключах. фамилии, электронные письма, номера isbn - все они могут измениться за один день.</p>

<p>я ненавижу суррогатные ключи в целом. их следует использовать только при отсутствии качественного натурального ключа. когда вы думаете об этом, абсурдно думать, что добавление бессмысленных данных в вашу таблицу может улучшить ситуацию.</p><p>вот мои причины:</p><ol> <li> <p>при использовании естественных ключей таблицы группируются так, как их чаще всего ищут, что ускоряет запросы.</p> </li> <li> <p>при использовании суррогатных ключей необходимо добавлять уникальные индексы в столбцы логических ключей. вы все еще должны предотвратить логическое дублирование данных. например, вы не можете разрешить две организации с одинаковыми именами в вашей таблице организации, даже если pk является столбцом суррогатного идентификатора.</p> </li> <li> <p>когда в качестве первичного ключа используются суррогатные ключи, гораздо менее понятно, каковы естественные первичные ключи. при разработке вы хотите знать, какой набор столбцов делает таблицу уникальной.</p> </li> <li> <p>в цепочках отношений один ко многим цепочки логических ключей. так, например, в организациях есть много счетов, а в счетах много счетов. таким образом, логический ключ организации - orgname. логический ключ учетных записей - orgname, accountid. логическим ключом invoice является orgname, accountid, invoicenumber.</p> <p>когда используются суррогатные ключи, цепочки ключей усекаются, имея только внешний ключ для непосредственного родителя. например, таблица invoice не имеет столбца orgname. у него есть только столбец для accountid. если вы хотите искать счета для определенной организации, вам нужно присоединиться к таблицам организация, учетная запись и счет. если вы используете логические ключи, то вы можете запросить таблицу организации напрямую.</p> </li> <li> <p>хранение значений суррогатного ключа таблиц поиска приводит к тому, что таблицы заполняются бессмысленными целыми числами. для просмотра данных необходимо создать сложные представления, объединяющие все таблицы поиска. таблица поиска предназначена для хранения набора допустимых значений для столбца. его не следует кодифицировать, храня вместо этого целочисленный суррогатный ключ. в правилах нормализации нет ничего, что предлагало бы хранить суррогатное целое число вместо самого значения.</p> </li> <li> <p>у меня есть три разные базы данных книг. ни один из них не показывает использование суррогатных ключей.</p> </li> </ol>

<p>я хочу поделиться с вами своим опытом этой бесконечной войны: d на естественной и суррогатной ключевой дилемме. я думаю, что <strong> как </strong> суррогатные ключи (искусственные автоматически сгенерированные), так и естественные ключи (составленные из столбцов с доменным значением) имеют <strong> плюсы </strong> и <strong> минусы </strong>. поэтому, в зависимости от вашей ситуации, может быть более уместным выбрать тот или иной метод.<br></p><p>поскольку многие люди представляют суррогатные ключи как почти идеальное решение, а естественные ключи - как чуму, я остановлюсь на аргументах другой точки зрения:</p><h1>недостатки суррогатных ключей</h1><p>суррогатными ключами являются:</p><ol> <li>источник проблем с производительностью: <ul> <li>они обычно реализуются с использованием автоматически увеличиваемых столбцов, что означает: <ul> <li>обход к базе данных каждый раз, когда вы хотите получить новый id (я знаю, что это можно улучшить с помощью алгоритмов кэширования или [seq] hilo, но все же эти методы имеют свои недостатки).</li> <li>если однажды вам понадобится переместить ваши данные из одной схемы в другую (по крайней мере, в моей компании это происходит довольно регулярно), то у вас могут возникнуть проблемы с коллизиями id. и да, я знаю, что вы можете использовать uuid, но для того, чтобы это длилось, требуется 32 шестнадцатеричных числа! (если вы заботитесь о размере базы данных, это может быть проблемой).</li> <li>если вы используете одну последовательность для всех ваших суррогатных ключей, то - наверняка - вы получите конфликт в вашей базе данных.</li> </ul> </li> </ul> </li> <li>ошибка склонна. последовательность имеет ограничение max\_value, поэтому, как разработчик, вы должны обратить внимание на следующие моменты: <ul> <li>вы должны циклически повторять вашу последовательность (когда достигается максимальное значение, оно возвращается к 1,2, ...).</li> <li>если вы используете последовательность как порядок (со временем) ваших данных, то вы должны обработать случай цикличности (столбец с id 1 может быть новее, чем строка с id max-value - 1).</li> <li>убедитесь, что ваш код (и даже ваши клиентские интерфейсы, которые не должны происходить так, как это должно было бы быть внутренним идентификатором), поддерживает целые числа 32b/64b, которые вы использовали для хранения значений последовательности.</li> </ul> </li> <li>они не гарантируют не дублированные данные. вы всегда можете иметь 2 строки с одинаковыми значениями столбца, но с другим сгенерированным значением. для меня это <strong></strong> проблема суррогатных ключей с точки зрения дизайна базы данных.</li> <li> <a href="http://en.wikipedia.org/wiki/surrogate\_key">подробнее в википедии ...</a> </li> </ol><h1>мифы о природных ключах</h1><ol> <li>составные ключи менее неэффективны, чем суррогатные ключи. нет! это зависит от используемого движка базы данных: <ul> <li> <a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11\_question\_id:6070436300346195032">oracle</a> </li> <li> <a href="https://stackoverflow.com/questions/1460465/composite-primary-key-performance-drawback-in-mysql">баз данных</a> </li> </ul> </li> <li>естественные ключи не существуют в реальной жизни. извините, но они существуют! например, в авиационной промышленности следующий кортеж всегда будет уникальным в отношении заданного <strong> запланированного </strong> рейса (авиакомпания, flightdate, flightnumber, operatingsuffix). в более общем смысле, когда набор бизнес-данных гарантированно является уникальным по заданному <strong> стандарту </strong>, тогда этот набор данных является [хорошим] кандидатом в естественный ключ.</li> <li>естественные ключи "загрязняют схему" дочерних таблиц. для меня это больше чувство, чем настоящая проблема. наличие первичного ключа из 4 столбцов по 2 байта каждый может быть более эффективным, чем один столбец из 11 байтов. кроме того, 4 столбца можно использовать для непосредственного запроса к дочерней таблице (используя 4 столбца в предложении where) без присоединения к родительской таблице.</li> </ol><h1>заключение</h1><p>используйте естественные ключи, когда это уместно, и используйте суррогатные ключи, когда их лучше использовать.</p><p>надеюсь, что это помогло кому-то!</p>

<p>всегда используйте ключ, который не имеет никакого делового значения. это просто хорошая практика.</p><p>правка: я пытался найти ссылку на него в интернете, но я не мог. однако в <a href="http://martinfowler.com/books.html">'patterns of enterprise archtecture'</a> [фаулер] есть хорошее объяснение того, почему вы не должны использовать ничего, кроме ключа, не имеющего значения, кроме как ключ. это сводится к тому, что у него должна быть одна работа и только одна работа.</p>

<p>суррогатные ключи очень удобны, если вы планируете использовать инструмент orm для обработки/генерации ваших классов данных. хотя вы можете использовать составные ключи с некоторыми из более продвинутых картографов (читай: hibernate), это добавляет некоторую сложность вашему коду.</p><p>(конечно, пуристы базы данных будут утверждать, что даже понятие суррогатного ключа является мерзостью.)</p><p>я фанат использования uids для суррогатных ключей, когда это необходимо. главная их победа в том, что вы знаете ключ заранее, например, вы можете создать экземпляр класса с идентификатором, который уже установлен и гарантированно будет уникальным, в то время как, скажем, с целочисленным ключом вам потребуется значение по умолчанию 0 или -1 и обновление до подходящего значения при сохранении/обновлении.</p><p>uid имеют штрафы с точки зрения поиска и скорости соединения, хотя это зависит от того, является ли данное приложение желательным.</p>

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

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

<p>я считаю, что в сценарии хранилища данных лучше следовать суррогатному ключевому пути. две причины:</p><ul> <li>вы независимы от исходной системы, и изменения там - такие как изменение типа данных - не затронут вас.</li> <li>вашему dw понадобится меньше физического пространства, поскольку вы будете использовать только целочисленные типы данных для своих суррогатных ключей. также ваши индексы будут работать лучше.</li> </ul>

<p>это один из тех случаев, когда суррогатный ключ в значительной степени <em>всегда</em> имеет смысл. в некоторых случаях вы выбираете, что лучше для базы данных или для вашей объектной модели, но в обоих случаях лучше использовать бессмысленный ключ или guid. это делает индексацию проще и быстрее, и это идентичность вашего объекта, которая не меняется.</p>

<p>напоминаем, что не рекомендуется размещать кластеризованные индексы на случайных суррогатных ключах, т. е. guid, которые читают xy8d7-dfd8s, поскольку sql server не имеет возможности физически сортировать эти данные. вместо этого вы должны поместить уникальные индексы в эти данные, хотя может быть также полезно просто запустить sql profiler для операций с основными таблицами и затем поместить эти данные в помощник по настройке ядра субд.</p><p>смотрите ветку @ <a href="http://social.msdn.microsoft.com/forums/en-us/sqlgetstarted/thread/27bd9c77-ec31-44f1-ab7f-bd2cb13129be">http://social.msdn.microsoft.com/forums/en-us/sqlgetstarted/thread/27bd9c77-ec31-44f1-ab7f-bd2cb13129be</a> </p>

<p><em> <strong>случай 1:</strong> </em> ваша таблица <strong>справочная таблица</strong> с менее чем 50 типами (вставками)</p><p>используйте <strong>бизнес/натуральные ключи</strong>. например:</p><pre><code>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 </code></pre><p><em> <strong>случай 2:</strong> </em> ваша таблица <strong>таблица с тысячами вставок</strong></p><p>используйте <strong>суррогатные/автоинкрементные ключи</strong>. например:</p><pre><code>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) </code></pre><p><strong>в первом случае:</strong></p><ul> <li>вы можете выбрать всех программистов в таблице people без использования объединения с таблицей job, но только с помощью: "select * from people where jobcode = 'prg'"</li> </ul><p><strong>во втором случае:</strong></p><ul> <li>ваши запросы к базе данных выполняются быстрее, потому что ваш первичный ключ является целым числом</li> <li>вам не нужно беспокоиться о поиске следующего уникального ключа, потому что сама база данных дает вам следующий автоинкремент.</li> </ul>

<p>суррогатные ключи могут быть полезны, когда деловая информация может измениться или быть идентичной. в конце концов, названия компаний не обязательно должны быть уникальными по всей стране. предположим, вы имеете дело с двумя компаниями под названием smith electronics, один в канзасе и один в мичигане. вы можете различить их по адресу, но это изменится. даже государство может измениться; что если смит электроникс из канзас-сити, штат канзас, переместится через реку в канзас-сити, штат миссури? не существует очевидного способа отличить эти предприятия от естественной ключевой информации, поэтому суррогатный ключ очень полезен.</p><p>думайте о суррогатном ключе как о номере isbn. обычно вы определяете книгу по названию и автору. однако у меня есть две книги под названием "перл-харбор" х. п. уилмотта, и это определенно разные книги, а не просто разные издания. в таком случае я мог бы сослаться на внешний вид книг или более ранних по сравнению с более поздними, но точно так же у меня есть isbn, к которому можно вернуться.</p>

<p>лошадь для курсов. чтобы заявить о моей предвзятости; сначала я разработчик, поэтому я в основном заинтересован в том, чтобы предоставить пользователям работающее приложение.</p><p>я работал над системами с естественными ключами, и мне пришлось потратить много времени, чтобы убедиться, что изменения значений будут иметь место.</p><p>я работал на системах только с суррогатными ключами, и единственным недостатком было отсутствие денормализованных данных для разделения.</p><p>большинству традиционных разработчиков pl/sql, с которыми я работал, не нравились суррогатные ключи из-за количества таблиц на соединение, но наши тестовые и производственные базы данных никогда не вызывали проблем; дополнительные объединения не влияли на производительность приложения. в случае с диалектами базы данных, которые не поддерживают такие предложения, как "x внутреннее соединение y для xa = yb", или разработчиками, которые не используют этот синтаксис, дополнительные объединения для суррогатных ключей затрудняют чтение запросов, а также их длительность при наборе и проверьте: см. сообщение тони эндрюса. но если вы используете orm или любую другую среду генерации sql, вы не заметите этого. сенсорный набор также смягчает.</p>

<p>может быть, не совсем относится к этой теме, но у меня болит голова с суррогатными ключами. предварительно предоставленная oracle аналитика создает автоматически сгенерированные sk на всех своих таблицах измерений в хранилище, а также сохраняет их на основе фактов. таким образом, каждый раз, когда они (измерения) необходимо перезагружать при добавлении новых столбцов или заполнении для всех элементов измерения, sk, назначенные во время обновления, делают sk несинхронными с исходными значениями, сохраненными в факте, заставляя полная перезагрузка всех таблиц фактов, которые присоединяются к нему. я бы предпочел, чтобы, даже если sk был бессмысленным числом, был бы какой-то способ, которым он не мог бы измениться для оригинальных/старых записей. как многие знают, нестандартные решения редко служат потребностям организации, и нам приходится постоянно настраивать их. теперь у нас на складе есть данные за 3 года, и полная перезагрузка из систем oracle financial очень велика. так что в моем случае они не генерируются при вводе данных, а добавляются в хранилище, чтобы помочь составить отчет о производительности. я понимаю, но наши меняются, и это кошмар.</p>

<p>в случае базы данных на определенный момент времени лучше всего использовать комбинацию суррогатных и натуральных ключей. например вы должны отслеживать информацию о члене клуба. некоторые атрибуты члена никогда не меняются. например, дата рождения, но имя может измениться. поэтому создайте таблицу member с суррогатным ключом member\_id и создайте столбец для dob. создайте другую таблицу с именем person name и имейте столбцы для member\_id, member\_fname, member\_lname, date\_updated. в этой таблице естественным ключом будет member\_id + date\_updated.</p>