Составные первичные ключи против уникального поля идентификатора объекта
Я унаследовал базу данных, основанную на идее, что составные ключи гораздо более идеальны, чем использование поля уникального идентификатора объекта, и что при создании базы данных в качестве первичного ключа никогда не следует использовать один уникальный идентификатор. Поскольку я создавал интерфейс Rails для этой базы данных, я столкнулся с трудностями при его согласовании с соглашениями Rails (хотя это было возможно при использовании пользовательских представлений и нескольких дополнительных гемов для обработки составных ключей).
Причина, по которой этот конкретный проект схемы был написан человеком, написавшим ее, была связана с тем, как база данных обрабатывает поля идентификаторов неэффективным образом, и когда она строит индексы, сортировки деревьев имеют недостатки. Этому объяснению не хватало глубины, и я все еще пытаюсь обдумать концепцию (я знаком с использованием составных клавиш, но не в 100% случаев).
Кто-нибудь может высказать свое мнение или добавить большую глубину к этой теме?
15 ответов
Большинство обычно используемых механизмов (MS SQL Server, Oracle, DB2, MySQL и т. Д.) Не испытывают заметных проблем при использовании системы суррогатных ключей. Некоторые могут даже испытать повышение производительности от использования суррогата, но проблемы с производительностью сильно зависят от платформы.
В общих чертах, естественный ключ (и, как следствие, составной ключ) стихи суррогатные ключевые дебаты имеют долгую историю без вероятного "правильного ответа" в поле зрения.
Аргументы для естественных ключей (единственного или составного) обычно включают в себя следующее:
1) Они уже доступны в модели данных. Большинство моделируемых объектов уже содержат один или несколько атрибутов или комбинаций атрибутов, которые соответствуют потребностям ключа в целях создания отношений. Добавление дополнительного атрибута к каждой таблице включает ненужную избыточность.
2) Они исключают необходимость определенных объединений. Например, если у вас есть клиенты с кодами клиентов и счета с номерами счетов-фактур (оба из которых являются "естественными" ключами), и вы хотите получить все номера счетов-фактур для определенного кода клиента, вы можете просто использовать "SELECT InvoiceNumber FROM Invoice WHERE CustomerCode = 'XYZ123'", В классическом подходе с суррогатным ключом SQL будет выглядеть примерно так: "SELECT Invoice.InvoiceNumber FROM Invoice INNER JOIN Customer ON Invoice.CustomerID = Customer.CustomerID WHERE Customer.CustomerCode = 'XYZ123'",
3) Они способствуют более универсальному подходу к моделированию данных. При использовании естественных ключей один и тот же дизайн может использоваться практически без изменений между различными механизмами SQL. Многие подходы с суррогатными ключами используют специальные методы механизма SQL для генерации ключей, что требует большей специализации модели данных для реализации на разных платформах.
Аргументы для суррогатных ключей имеют тенденцию вращаться вокруг проблем, специфичных для движка SQL:
1) Они позволяют легче изменять атрибуты при изменении бизнес-требований / правил. Это потому, что они позволяют изолировать атрибуты данных в одной таблице. Это в первую очередь проблема для механизмов SQL, которые неэффективно реализуют стандартные конструкции SQL, такие как DOMAIN. Когда атрибут определен оператором DOMAIN, изменения атрибута могут быть выполнены в рамках всей схемы с помощью оператора ALTER DOMAIN. Различные механизмы SQL имеют разные характеристики производительности для изменения домена, а некоторые механизмы SQL вообще не реализуют DOMAINS, поэтому разработчики моделей данных компенсируют эти ситуации, добавляя суррогатные ключи, чтобы улучшить возможность вносить изменения в атрибуты.
2) Они обеспечивают более легкую реализацию параллелизма, чем естественные ключи. В случае с естественным ключом, если два пользователя одновременно работают с одним и тем же набором информации, таким как строка клиента, и один из пользователей изменяет значение естественного ключа, тогда обновление вторым пользователем завершится неудачно, поскольку код клиента обновление больше не существует в базе данных. В случае суррогатного ключа обновление будет успешно выполнено, поскольку неизменяемые значения идентификаторов используются для идентификации строк в базе данных, а не изменяемых кодов клиентов. Однако не всегда желательно разрешать второе обновление - если код клиента изменился, возможно, что второму пользователю не будет разрешено продолжить их изменение, потому что фактическая "идентичность" строки изменилась - второй пользователь может обновлять не тот ряд. Ни суррогатные ключи, ни естественные ключи сами по себе не решают эту проблему. Всеобъемлющие параллельные решения должны решаться за пределами реализации ключа.
3) Они работают лучше, чем естественные ключи. На производительность больше всего влияет движок SQL. Одна и та же схема базы данных, реализованная на одном и том же оборудовании с использованием разных механизмов SQL, часто будет иметь резко отличающиеся характеристики производительности из-за механизмов хранения и поиска данных механизмов SQL. Некоторые механизмы SQL близко приближаются к системам с плоскими файлами, где данные фактически сохраняются избыточно, когда один и тот же атрибут, такой как код клиента, появляется в нескольких местах в схеме базы данных. Это избыточное хранилище с помощью механизма SQL может вызвать проблемы с производительностью, когда необходимо внести изменения в данные или схему. Другие механизмы SQL обеспечивают лучшее разделение между моделью данных и системой хранения / извлечения, что позволяет быстрее изменять данные и схему.
4) Суррогатные ключи работают лучше с определенными библиотеками доступа к данным и средами GUI. Из-за однородного характера большинства конструкций суррогатных ключей (пример: все реляционные ключи являются целыми числами), библиотеки доступа к данным, ORM и структуры GUI могут работать с информацией, не требуя специальных знаний о данных. Естественные ключи из-за их разнородной природы (разные типы данных, размер и т. Д.) Не работают так же хорошо с автоматизированными или полуавтоматическими наборами инструментов и библиотеками. Для специализированных сценариев, таких как встроенные базы данных SQL, проектирование базы данных с учетом конкретного инструментария может быть приемлемым. В других сценариях базы данных являются корпоративными информационными ресурсами, доступ к которым одновременно осуществляется несколькими платформами, приложениями, системами отчетов и устройствами, и, следовательно, они не функционируют так же хорошо, если они ориентированы на какую-либо конкретную библиотеку или среду. Кроме того, базы данных, предназначенные для работы с определенными наборами инструментов, становятся проблемой при представлении следующего замечательного набора средств.
Я склонен падать на сторону естественных ключей (очевидно), но я не фанатичен в этом. Из-за среды, в которой я работаю, где любая конкретная база данных, которую я помогаю проектировать, может использоваться различными приложениями, я использую естественные ключи для большей части моделирования данных и редко представляю суррогаты. Тем не менее, я не изо всех сил пытаюсь повторно реализовать существующие базы данных, которые используют суррогаты. Системы с суррогатным ключом работают просто отлично - не нужно менять что-то, что уже хорошо работает.
Есть несколько отличных ресурсов для обсуждения достоинств каждого подхода:
http://www.google.com/search?q=natural+key+surrogate+key
Я занимаюсь разработкой приложений баз данных 15 лет, и мне еще не приходилось сталкиваться с ситуацией, когда несуррогатный ключ был лучшим выбором, чем суррогатный ключ.
Я не говорю, что такого случая не существует, я просто говорю, что когда вы учитываете практические вопросы разработки приложения, которое обращается к базе данных, обычно преимущества суррогатного ключа начинают подавлять теоретическую чистоту Суррогатные ключи.
Первичный ключ должен быть постоянным и бессмысленным; несуррогатные ключи обычно не соответствуют одному или обоим требованиям, в конце концов
если ключ не постоянный, у вас есть проблема с обновлением в будущем, которая может быть довольно сложной
если ключ не бессмысленный, то он с большей вероятностью изменится, т.е. не будет постоянным; см выше
Возьмите простой, общий пример: таблица инвентарных предметов. Может быть заманчиво сделать первичным ключом номер элемента (номер артикула, штрих-код, код детали и т. Д.), Но через год все номера элементов изменятся, и у вас останется очень грязное обновление целого проблема с базой данных...
РЕДАКТИРОВАТЬ: есть дополнительная проблема, которая более практична, чем философская. Во многих случаях вы собираетесь каким-то образом найти конкретную строку, а затем обновить ее или найти ее снова (или обе). С составными ключами больше данных для отслеживания и больше ограничений в предложении WHERE для повторного поиска или обновления (или удаления). Также возможно, что один из ключевых сегментов мог измениться за это время! С суррогатным ключом всегда остается только одно значение (суррогатный идентификатор), и по определению оно не может измениться, что значительно упрощает ситуацию.
Похоже, что человек, который создал базу данных, находится на стороне естественных ключей от великих естественных ключей против дебатов о суррогатных ключах.
Я никогда не слышал о каких-либо проблемах с btrees в полях идентификаторов, но я также не изучал их достаточно глубоко...
Я падаю на сторону суррогатного ключа: у вас меньше повторений при использовании суррогатного ключа, потому что вы повторяете только одно значение в других таблицах. Поскольку люди редко присоединяются к столам вручную, нам все равно, число это или нет. Кроме того, поскольку в индексе можно искать только один столбец фиксированного размера, можно предположить, что суррогаты имеют более быстрый поиск по первичному ключу.
Использование полей "уникальный (объектный) идентификатор" упрощает объединения, но вы должны стремиться к тому, чтобы другой (возможно, составной) ключ оставался уникальным - НЕ ослабляйте ограничения, не равные нулю, и НЕ поддерживайте ограничение уникальности.
Если СУБД не может эффективно обрабатывать уникальные целые числа, у нее большие проблемы. Однако использование "уникального (объектного) идентификатора" и другого ключа требует больше места (для индексов), чем просто другой ключ, и имеет два индекса для обновления при каждой операции вставки. Так что это не халява, но если вы сохраните оригинальный ключ, то все будет в порядке. Если вы исключите другой ключ, вы нарушите дизайн вашей системы; весь ад в конце концов вырвется на свободу (и вы можете или не можете заметить, что ад вырвался на свободу).
Я в основном являюсь членом суррогатной ключевой команды, и даже если я ценю и понимаю аргументы, подобные приведенным здесь JeremyDWill, я все еще ищу случай, когда "натуральный" ключ лучше суррогатного...
Другие публикации, посвященные этой проблеме, обычно относятся к теории реляционных баз данных и производительности баз данных. Еще один интересный аргумент, всегда забываемый в этом случае, связан с нормализацией таблиц и производительностью кода:
каждый раз, когда я создаю таблицу, я буду терять время
- идентифицируя его первичный ключ и его физические характеристики (тип, размер)
- помните эти характеристики каждый раз, когда я хочу сослаться на это в моем коде?
- объяснять мой выбор ПК другим разработчикам в команде?
Мой ответ "нет" на все эти вопросы:
- У меня нет времени терять попытки определить "лучший первичный ключ" при работе со списком людей.
- Я не хочу помнить, что "Первичный ключ мой"
computermsgstr "таблица представляет собой строку длиной 64 символа (принимает ли Windows столько символов для имени компьютера?). - Я не хочу объяснять свой выбор другим разработчикам, когда один из них наконец скажет: "Да, чувак, но учтите, что вам нужно управлять компьютерами через разные домены? Позволяет ли эта строка из 64 символов хранить имя домена + Имя компьютера?".
Так что я работал в течение последних пяти лет с очень простым правилом: каждая таблица (давайте назовем это 'myTable') имеет свое первое поле под названием'id_MyTable', который имеет тип uniqueIdentifier. Даже если эта таблица поддерживает отношение "многие ко многим", такое какComputerUser"Таблица, где комбинация"id_Computer' а также 'id_User"формирует очень приемлемый первичный ключ, я предпочитаю создавать это"id_ComputerUser'field является uniqueIdentifier, просто чтобы придерживаться правила.
Основным преимуществом является то, что вам не нужно беспокоиться об использовании первичного ключа и / или внешнего ключа в вашем коде. Когда у вас есть имя таблицы, вы знаете имя и тип ПК. Как только вы узнаете, какие ссылки реализованы в вашей модели данных, вы узнаете имя доступных внешних ключей в таблице.
Я не уверен, что мое правило лучшее. Но это очень эффективный!
Практический подход к разработке новой архитектуры - это использование суррогатных ключей для таблиц, которые будут содержать тысячи многоколоночных уникальных записей и составных ключей для кратких описательных таблиц. Я обычно нахожу, что колледжи диктуют использование суррогатных ключей, в то время как программисты в реальном мире предпочитают составные ключи. Вам действительно нужно применить правильный тип первичного ключа к таблице, а не просто так или иначе.
Я собираюсь быть коротким и приятным здесь: составные первичные ключи не хороши в наши дни. Добавьте суррогатные произвольные ключи, если можете, и сохраняйте текущие схемы ключей с помощью уникальных ограничений. ORM счастлив, вы счастливы, оригинальный программист не так счастлив, но если он не ваш начальник, он может с этим справиться.
Использование естественных ключей превращает в кошмар использование любого автоматического ORM в качестве слоя сохранения. Кроме того, внешние ключи в нескольких столбцах имеют тенденцию перекрывать друг друга, и это создаст дополнительные проблемы при перемещении и обновлении отношений ОО-способом.
Тем не менее, вы можете преобразовать естественный ключ в уникальное ограничение и добавить автоматически сгенерированный идентификатор; это не снимает проблему с внешними ключами, однако их придется менять вручную; Надеемся, что несколько столбцов и перекрывающихся ограничений будут меньшим количеством всех отношений, поэтому вы можете сосредоточиться на рефакторинге там, где это наиболее важно.
У натурального ПК есть сценарий мотивации и использования, и это не плохо (тм), они просто не ладят с ORM.
Мне кажется, что, как и любые другие концепции, естественные ключи и нормализация таблиц должны использоваться, когда это целесообразно, а не в качестве слепых проектных ограничений.
Я не опытный, но все же я за использование первичного ключа, так как id здесь - это объяснение на примере...
Формат внешних данных может меняться со временем. Например, вы можете подумать, что ISBN книги станет хорошим первичным ключом в таблице книг. В конце концов, ISBN уникальны. Но поскольку эта конкретная книга написана, издательская индустрия в Соединенных Штатах готовится к серьезным изменениям, поскольку дополнительные цифры добавляются ко всем ISBN. Если бы мы использовали ISBN в качестве первичного ключа в таблице книг, нам пришлось бы обновлять каждую строку, чтобы отразить это изменение. Но тогда у нас будет другая проблема. В базе данных будут другие таблицы, которые ссылаются на строки в таблице книг через первичный ключ. Мы не можем изменить ключ в таблице книг, если сначала не пройдем и не обновим все эти ссылки. И это будет включать в себя удаление ограничений внешнего ключа, обновление таблиц, обновление таблицы книг и, наконец, восстановление ограничений. В общем, это что-то вроде боли. Проблемы исчезнут, если мы будем использовать нашу собственную внутреннюю ценность в качестве первичного ключа. Никакая третья сторона не может прийти и произвольно сказать нам изменить нашу схему - мы контролируем наше собственное пространство ключей. И если что-то, например, ISBN, нужно изменить, оно может измениться, не затрагивая какие-либо существующие отношения в базе данных. По сути, мы отделили связывание строк от внешнего представления данных в этих строках.
Хотя объяснение довольно книжное, но я думаю, что оно объясняет вещи проще.
... как база данных обрабатывает поля идентификаторов неэффективным образом, и когда она строит индексы, сортировки по деревьям ошибочны...
Это почти наверняка бессмыслица, но, возможно, она связана с проблемой конкуренции за индексные блоки при назначении увеличивающих номеров для PK с высокой скоростью из разных сеансов. Если это так, то поможет индекс REVERSE KEY, хотя и за счет увеличения размера индекса из-за изменения алгоритма разделения на блоки. http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm
Идите синтетически, особенно если это помогает более быстрой разработке с вашим набором инструментов.
Составные ключи могут быть хорошими - они могут повлиять на производительность - но они не являются единственным ответом, во многом так же, как уникальный (суррогатный) ключ не единственный ответ.
Меня беспокоит неопределенность в выборе составных ключей. Чаще всего расплывчатость в технических вопросах указывает на отсутствие понимания - возможно, следование чьим-либо указаниям в книге или статье...
Нет ничего плохого в одном уникальном идентификаторе - на самом деле, если у вас есть приложение, подключенное к серверу баз данных, и вы можете выбрать, какая база данных вы используете, все будет хорошо, и вы можете в значительной степени делать что-либо со своими ключами и не очень сильно страдает.
Об этом было и будет много написано, потому что нет единого ответа. Существуют методы и подходы, которые необходимо применять осторожно и квалифицированно.
У меня было много проблем с автоматическим предоставлением идентификатора базой данных - и я избегаю их везде, где это возможно, но все же время от времени использую их.
@JeremyDWill
Спасибо за то, что вы предоставили столь необходимый баланс для обсуждения. В частности, спасибо за информацию о DOMAINs.
Я на самом деле использую суррогатные ключи в масштабе всей системы для согласованности, но есть и компромиссы. Самая распространенная причина проклятия с помощью суррогатных ключей - это когда у меня есть справочная таблица с коротким списком канонических значений - я бы использовал меньше места, и все мои запросы были бы короче / проще / быстрее, если бы я только что сделал значения ПК вместо того, чтобы присоединиться к столу.
Вы можете сделать и то и другое - поскольку любая большая база данных компании, вероятно, будет использоваться несколькими приложениями, включая администраторов баз данных, выполняющих разовые запросы и импорт данных, проектирование ее исключительно в интересах систем ORM не всегда практично или желательно.
В эти дни я обычно добавляю свойство "RowID" к каждой таблице - это поле является GUID и уникально для каждой строки. Это НЕ первичный ключ - это естественный ключ (если это возможно). Однако любые слои ORM, работающие поверх этой базы данных, могут использовать RowID для идентификации своих производных объектов.
Таким образом, вы можете иметь:
CREATE TABLE dbo.Invoice (CustomerId varchar (10), CustomerOrderNo varchar (10), InvoiceAmount деньги не нулевые, Комментарии nvarchar(4000), RowId uniqueidentifier не нулевой по умолчанию (newid()), первичный ключ (CustomerId, CustomerOrderNo))
Таким образом, ваш администратор баз данных счастлив, ваш архитектор ORM счастлив, и ваша целостность базы данных сохраняется!
Я просто хотел добавить сюда кое-что, что я никогда не рассмотрел при обсуждении автоматически сгенерированных полей целочисленных идентификаторов с реляционными базами данных (потому что я их часто вижу), то есть его базовый тип может переполниться в какой-то момент.
Теперь я не пытаюсь сказать, что это автоматически делает составные идентификаторы подходящими, но это просто факт, что, хотя в таблицу может быть логически добавлено больше данных (которые все еще уникальны), единственное автоматически генерируемое целое число личность может предотвратить это.
Да, я понимаю, что в большинстве случаев это маловероятно, и использование 64-битного целого дает вам большой запас, и реально база данных, вероятно, должна была бы быть разработана по-другому, если бы произошло такое переполнение.
Но это не мешает кому-то это делать... таблица, использующая одно автоматически сгенерированное 32-битное целое число, поскольку ее идентификатор, который, как ожидается, будет хранить все транзакции на глобальном уровне для конкретной компании быстрого питания, потерпит неудачу, так как как только он пытается вставить свою 2 147 483 648-ю транзакцию (и это вполне осуществимый сценарий).
Стоит только отметить, что люди склонны скрывать или просто игнорировать. Если какая-либо таблица будет вставляться с регулярностью, следует подумать о том, как часто и сколько данных будет накапливаться с течением времени, и следует ли вообще использовать целочисленный идентификатор.