Составные первичные ключи в сравнении с уникальным полем идентификатора объекта

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

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

Может ли кто-нибудь предложить свои мнения или добавить большую глубину в эту тему?

Ответ 1

Большинство часто используемых двигателей (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) Они позволяют упростить реализацию concurrency, чем естественные ключи.. В случае с естественным ключом, если два пользователя одновременно работают с одним и тем же информационным набором, таким как строка клиента и одна пользователей изменяет значение натурального ключа, тогда обновление второго пользователя будет терпеть неудачу, потому что код клиента, который они обновляют, больше не существует в базе данных. В суррогатном ключевом случае обновление будет успешно обработано, потому что неизменяемые значения идентификатора используются для идентификации строк в базе данных, а не изменяемых кодов клиентов. Тем не менее, не всегда желательно разрешить второе обновление - если код клиента изменился, возможно, что второму пользователю не разрешается продолжать их изменение, потому что фактический "идентификатор" строки изменился - второй пользователь может обновлять неправильную строку. Ни суррогатные ключи, ни естественные ключи сами по себе не решают эту проблему. Комплексные решения concurrency необходимо решать за пределами реализации ключа.

3) Они работают лучше, чем естественные. Производительность наиболее сильно зависит от механизма SQL. Та же схема базы данных, реализованная на одном и том же аппаратном обеспечении с использованием разных механизмов SQL, часто имеет резко отличающиеся характеристики производительности, благодаря механизмам хранения и извлечения данных SQL. Некоторые SQL-модули тесно приближаются к плоским файловым системам, где данные фактически хранятся избыточно, когда один и тот же атрибут, например код клиента, отображается в нескольких местах в схеме базы данных. Это избыточное хранилище с помощью механизма SQL может вызвать проблемы с производительностью, когда необходимо внести изменения в данные или схему. Другие механизмы SQL обеспечивают лучшее разделение между моделью данных и системой хранения/поиска, что позволяет быстрее изменять данные и схему.

4) Суррогатные ключи лучше работают с определенными библиотеками доступа к данным и графическими интерфейсами.Из-за однородности большинства суррогатных ключевых конструкций (например, все реляционные ключи являются целыми числами), библиотеки доступа к данным, ORM и графические интерфейсы могут работать с информацией, не требуя специального знания данных. Естественные ключи из-за их гетерогенности (разные типы данных, размер и т.д.) Не работают также с автоматизированными или полуавтоматическими инструментами и библиотеками. Для специализированных сценариев, таких как встроенные базы данных SQL, может быть приемлемым создание базы данных с определенным набором инструментальных средств. В других сценариях базами данных являются информационные ресурсы предприятия, которые одновременно доступны несколькими платформами, приложениями, системами отчетов и устройствами и поэтому не работают, когда они сконцентрированы на какой-либо конкретной библиотеке или структуре. Кроме того, базы данных, предназначенные для работы с конкретными инструментариями, становятся ответственностью, когда вводится следующий отличный инструментарий.

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

Есть несколько превосходных ресурсов, обсуждающих достоинства каждого подхода:

http://www.google.com/search?q=natural+key+surrogate+key

http://www.agiledata.org/essays/keys.html

http://www.informationweek.com/news/software/bi/201806814

Ответ 2

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

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

Ответ 3

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

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

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

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

Ответ 4

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

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

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

Ответ 5

Использование уникальных (object) ID-полей упрощает объединение, но вы должны стремиться к тому, чтобы другой (возможно, составной) ключ все еще был уникальным - НЕ расслабляйте ненулевые ограничения и НЕ поддерживайте уникальное ограничение.

Если СУБД не может эффективно обрабатывать уникальные целые числа, это имеет большие проблемы. Однако использование "уникального" (object) ID "и другого ключа использует больше пространства (для индексов), чем просто другой ключ, и имеет два индекса для обновления в каждой операции вставки. Так что это не халява - но до тех пор, пока вы сохраняете оригинальный ключ, вы тоже будете в порядке. Если вы устраните другой ключ, вы нарушите дизайн своей системы; весь ад в конце концов вырвется (и вы можете или не заметить, что ад сорвался).

Ответ 6

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

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

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

с указанием его первичного ключа и его физические характеристики (тип, размер)
запомнить эти характеристики каждый раз, когда я хочу обратиться к нему в мой код?
объяснение выбора PK другим разработчиков в команде?

Мой ответ не соответствует всем этим вопросам:

У меня нет времени терять попытку определить "лучший первичный ключ", когда со списком лиц.
Я не хочу вспоминать, что Первичный ключ моей таблицы "computer" строка длиной 64 символа (делает Windows принимает много символов для имени компьютера?).
Я не хочу объяснять свой выбор других разработчиков, где один из них наконец, скажет "Да, человек, но считайте, что вам нужно управлять компьютеры в разных доменах? Позволяет ли строка из 64 символов вы должны сохранить доменное имя + имя компьютера? ".

Итак, я работаю последние пять лет с очень простым правилом: каждая таблица (пусть называется "myTable" ) имеет свое первое поле под названием "id_MyTable", которое имеет уникальный тип идентификатора. Даже если эта таблица поддерживает отношение "многие ко многим", например таблицу "ComputerUser", где комбинация "id_Computer" и "id_User" образует очень приемлемый первичный ключ, я предпочитаю создайте это поле "id_ComputerUser" как уникальный идентификатор, просто придерживайтесь правила.

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

Я не уверен, что мое правило - лучшее. Но он очень эффективен!

Ответ 7

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

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

Естественные pk имеют свой сценарий мотивации и использования и не являются плохими (tm), они просто не могут хорошо ладить с ORM.

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

Ответ 8

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

Ответ 9

... как база данных обрабатывает идентификационные поля неэффективным образом и когда она строит индексы, древовидные породы ошибочны...

Это было почти наверняка бессмыслицей, но, возможно, связано с проблемой конкуренции индексного блока при назначении инкрементных чисел PK с высокой скоростью из разных сеансов. Если это так, то индекс REVERSE KEY должен помочь, хотя и за счет большего размера индекса из-за изменения алгоритма разделения блоков. http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref998

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

Ответ 10

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

Ответ 11

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

Формат внешних данных может меняться со временем. Например, вы можете подумать, что ISBN книги сделает хороший первичный ключ в таблице книг. В конце концов, ISBN уникальны. Но поскольку эта конкретная книга написана, издательская индустрия в Соединенных Штатах готовится к серьезным изменениям, поскольку дополнительные цифры добавляются ко всем ISBN. Если wed использовал ISBN в качестве первичного ключа в таблице книг, wed должен обновить каждую строку, чтобы отразить это изменение. Но потом у меня возникла другая проблема. В базе данных могут быть другие таблицы, которые связывают строки в таблице книг с помощью первичного ключа. Мы не можем изменить ключ в таблице книг, если мы сначала не рассмотрим и не обновим все эти ссылки. И это будет связано с устранением ограничений внешнего ключа, обновлением таблиц, обновлением таблицы книг и, наконец, восстановлением ограничений. В общем, это что-то боль. Проблемы уходят, если мы используем собственное внутреннее значение в качестве первичного ключа. Никакая третья сторона не может прийти и произвольно сказать нам изменить нашу схему - мы контролируем наше собственное пространство ключей. И если что-то вроде ISBN действительно нужно изменить, оно может измениться, не затрагивая какие-либо существующие отношения в базе данных. По сути, weve отделили вязание строк от внешнего представления данных в этих строках.

Хотя объяснение довольно книжное, но я думаю, что это объясняет вещи более простым способом.

Ответ 12

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

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

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

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

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

Ответ 13

@JeremyDWill

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

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

Ответ 14

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

То, что я обычно делаю в эти дни, - добавить свойство "RowID" в каждую таблицу - это поле является GUID и поэтому уникально для каждой строки. Это НЕ первичный ключ - это естественный ключ (если возможно). Однако любые слои ORM, работающие над этой базой данных, могут использовать RowID для идентификации своих производных объектов.

Таким образом, вы можете:

CREATE TABLE dbo.Invoice (
  CustomerId varchar(10),
  CustomerOrderNo varchar(10),
  InvoiceAmount money not null,
  Comments nvarchar(4000),
  RowId uniqueidentifier not null default(newid()),

  primary key(CustomerId, CustomerOrderNo)
)

Итак, ваш администратор базы данных счастлив, ваш архитектор ORM счастлив, и целостность вашей базы данных сохраняется!

Ответ 15

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

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

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

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

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