Выбор наилучшего первичного ключа + система нумерации

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

Учитывая дизайн базы данных ниже, что будет лучшим вариантом.

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

=================   ==================
Road_Number(PK)     Segment_Number(PK)
=================   ==================
 1                   1

Пример 2: Использование созданной программы PK

=================   ==================
Road_Number(PK)     Segment_Number(PK)
=================   ==================
 "RD00000001WCK"     "00000001.1"

(00000001.1 означает первый сегмент дороги, который увеличивается каждый раз, когда вы добавляете новый сегмент, например 00000001.2)

Пример 3: Использование бит обоих (добавление нового столбца)

=======================    ==========================
ID(PK) Road_Number(UK)     ID(PK)  Segment_Number(UK)
=======================    ==========================
 1     "RD00000001WCK"       1       "00000001.1"

Немного информации о предыстории, мы будем использовать Road Number и Segment Number в отчетах и других документах, поэтому они должны быть уникальными.

Мне всегда нравилось сохранять простые вещи, поэтому я предпочитаю пример 1, но я читал, что вы не должны раскрывать свои первичные ключи в отчетах/документах. Итак, теперь я больше думаю по строкам примера 3.

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

Как вы думаете, что мы должны делать?

Спасибо.

EDIT: Спасибо всем за отличные ответы, очень помог мне.

Ответ 1

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

Натуральные клавиши - это клавиши, основанные на внешне значимые данные, которые (якобы) уникальным. Общие примеры являются кодами продуктов, двухбуквенным состоянием коды (США), номера социального страхования и так далее. Суррогатный или технический Первичные ключи - это те, которые имеют абсолютно никакого значения вне система. Они придуманы исключительно для идентификации объекта и обычно автоматически увеличивающиеся поля (SQL Server, MySQL, другие) или последовательности (в первую очередь Oracle).

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

Как вам нравятся ваши первичные ключи?

Какова наилучшая практика для первичных ключей в таблицах?

Какой формат первичного ключа вы бы использовали в этой ситуации.

Суррогатный Vs. Природные/бизнес-ключи

Должен ли я иметь выделенное поле первичного ключа?

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

Ответ 2

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

Ответ 3

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

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

Ответ 4

Не помещайте значение в свои поля PK, если...

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

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

Ответ 5

Я думаю, что важно помнить, что каждая таблица в вашей базе данных/дизайне может иметь несколько ключей. Это Ключи кандидата. См. запись в Википедии для Ключей Кандидатов

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

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

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

Неизменяемый/Стабильный. Значения первичного ключа не должны меняться. Если это так, вы рискуете внедрить аномалии обновления.
Not Null. Большинство платформ СУБД требуют, чтобы атрибуты первичного ключа не были нулевыми.
Простой - простые типы данных и значения для физического хранения и производительности. Целочисленные значения хорошо работают здесь, и это тип данных для большинства ключей суррогата/автогенератора.

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

Ответ 6

Следуйте политике "Не использовать".

Некоторые проблемы, с которыми вы можете столкнуться:

Вам нужно сгенерировать ключи от нескольких хостов.

Кто-то захочет зарезервировать смежные номера для совместного использования.

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

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

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

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

Ответ 7

Как уже упоминалось ранее, сохраните свои внутренние первичные ключи как просто клавиши, какой бы оптимальный тип данных не был на вашей платформе.

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

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

    Identifier-type table             Identifier-cross-ref table
      type-id             ------------> type-id              (unique
      type-name                         identifier-string     key)
                                        internal-id

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

Кроме того, вы можете пойти на все встречи, на которых все спорят друг с другом.

Ответ 8

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

Ответ 9

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

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

Ответ 10

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

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

Ответ 11

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

Говоря об этом, вы можете подумать о том, есть ли ваша база данных:

сосредоточено на обслуживании данных и транзакционной обработке (т.е. создавать/обновлять/удалять операции)
ориентированный на анализ и отчетность (т.е. запросы)

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

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

В противном случае вы, вероятно, сосредоточены на полной CRUD DB, которая должна в какой-то степени покрыть все базы - это подавляющее большинство ситуаций. В этом случае перейдите к опции №3. Вы всегда можете оптимизировать для запросов в будущем, но вам будет трудно модифицировать для удобства обслуживания.

Ответ 12

Надеюсь, вы согласитесь со мной в том, что каждый элемент дизайна должен иметь одну цель.

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

Что касается новых столбцов в опции 3, вы должны проверить, могут ли они быть рассчитаны (лучше всего было бы выполнять вычисления на уровне модели, чтобы их можно было легко изменить, чем если бы они выполнялись в СУБД), не слишком много от других элементов. Например, вы можете сохранить номер сегмента и номер дороги в соответствующих таблицах, а затем использовать их для генерации "00000001.1". Это позволит быстро изменить нумерацию активов.

Ответ 13

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

Лично я всегда использую отдельный столбец первичного ключа; и я всегда использую GUID. Некоторые разработчики предпочитают простой INT поверх GUID из-за нехватки жесткого диска. Однако, если возникает ситуация, когда вам нужно объединить две базы данных, GUID почти никогда не сталкиваются (в то время как INTs могут столкнуться).

Первичные ключи должны выглядеть НИКОГДА пользователем. Сделать его понятным для пользователя не должно вызывать беспокойства. Первичные ключи ДОЛЖЕН использоваться для связи с внешними ключами. Это их цель. Значение должно быть машиносчитываемым и после его создания никогда не меняться.