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

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

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

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

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

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

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

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

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

Не вкладывайте смысла в свои поля PK, если ...

• На 100% совершенно невозможно, чтобы значение никогда не изменилось и что

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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