Дизайнерские идеи “ключа/ИДЕНТИФИКАТОРА” базы данных, Суррогатный ключ, Первичный ключ, и т.д.

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

Например, предположим, что у меня была таблица адресов электронной почты для списка рассылки. Я бы хотел, чтобы они были уникальными, верно? Нет смысла иметь 2, 3 или n записей одного и того же адреса электронной почты. Если я использую email_address в качестве основного ключа ( который является естественным ключом -- он существует в виде данных независимо от структуры базы данных, которую вы создали), это гарантирует, что у меня никогда не будет дубликата адреса электронной почты в списке рассылки.

Однако, если в качестве суррогатного ключа у меня есть поле под названием id, то у меня может быть любое количество дублирующих друг друга адресов электронной почты. Это будет плохо, если в списке будет 10 строк одного и того же адреса электронной почты, и все они будут содержать противоречивую информацию о подписке в других столбцах. Какой из них правильный, если он есть? Невозможно сказать! После этого, ваша целостность данных будет нарушена. Нет другого способа исправить данные, кроме как просматривать записи по одному, спрашивать у людей, какая информация о подписке действительно верна и т.д.

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

Так что очевидно, что этот пример слишком упрощен, но он иллюстрирует суть дела.

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

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

1. Они прежде всего призваны обеспечить целостность и согласованность ваших данных! Каждая строка в таблице представляет собой экземпляр любой сущности, для которой определены данные. Нет Замена Ключ, с помощью определения, может когда-либо выполнять эту функцию. Только правильно спроектированный естественный ключ может сделать это. (Если все, что у вас есть - это суррогатный ключ, вы всегда можете добавить еще одну строку с любыми другими атрибутами, точно идентичными существующей строке, если вы дадите ей другое значение суррогатного ключа)
2. Во-вторых, они существуют для того, чтобы служить ссылками (указателями) на посторонние Ключи в других таблицах, которые являются дочерними сущностями сущности в таблице с Первичным Ключом. Природный ключ (особенно если он является составной частью множества атрибутов) не является хорошим выбором для этой функции, так как это означает, что A) посторонние ключи во всех дочерних таблицах также должны быть составными, что делает их очень широкими, и тем самым снижает производительность всех операций ограничения и SQL Joins. и B) Если бы значение ключа изменилось в основной таблице, то пришлось бы делать каскадные обновления на каждой таблице, где значение было представлено в виде FK.

Так что ответ прост.... Always (везде, где Вас волнует целостность/согласованность данных) используйте естественный ключ и, при необходимости, оба! Когда естественный ключ является составным, или длинным, или недостаточно стабильным, добавьте альтернативный Суррогатный ключ (например, как автоинкрементирующее целое число) для использования в качестве целей FK в дочерних таблицах. Но рискуя потерять целостность данных в вашей таблице, НЕ удаляйте естественный ключ из основной таблицы.

Чтобы сделать это кристально ясно, давайте приведем пример. Скажем, у вас есть таблица с банковскими счетами в ней... Естественным ключом может быть банковский номер маршрута и номер счета в банке. Чтобы избежать использования этого двойного составного ключа в каждой записи транзакции в таблице транзакций, вы можете решить поместить искусственно сгенерированный суррогатный ключ в таблицу BankAccount, которая является всего лишь целым числом. Но лучше сохранить естественный ключ! Если бы вы этого не сделали, если бы у вас не было и составного естественного ключа, вы вполне могли бы закончить с двумя строками в таблице следующим образом

id  BankRoutingNumber BankAccountNumber   BankBalance
 1     12345678932154   9876543210123       $123.12
 2     12345678932154   9876543210123    ($3,291.62)

Теперь, какая из них правильная?

Чтобы выделить из комментариев ниже, Какая польза от того, что вы можете "идентифицировать строку "? Совсем не хорошо, как мне кажется, потому что нам нужно уметь идентифицировать, какой банковский счет строка представляет! Идентификация строки важна только для технических функций внутренней базы данных, таких как объединение в запросах, или для операций ограничения FK, которые, если/когда они необходимы, все равно должны использовать суррогатный ключ, а не естественный ключ.

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

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

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

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

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

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

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

Randy

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

В некоторых случаях естественный ключ может быть громоздким, поэтому может быть создан суррогатный ключ для использования в качестве чужеродного ключа и т.д. Например, в журнале или дневнике PK может быть дата, время и полный текст записи (если возможно добавить две записи одновременно). Очевидно, что было бы плохо использовать все это каждый раз, когда вы хотите идентифицировать строку, так что вы можете сделать "log id". Это может быть последовательный номер (наиболее распространенный), или дата плюс последовательный номер (как 20091222001), или что-то другое. Некоторые натуральные ключи могут работать так же хорошо, как и первичный ключ, например, VIN-номера автомобилей, студенческие ID-номера (если они не используются повторно), или в случае объединения таблиц, PK двух соединяемых таблиц.

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

В вашем случае я думаю, что LogEntryID для ваших элементов журнала кажется разумным. Является ли идентификатор FK к таблице Пользователей? Если нет, то я могу усомниться в том, что оба ID и LogEntryID находятся в одной таблице, так как они избыточны. Если да, то я бы изменил имя на user_id или что-то подобное.

Таблица пользователей

Использование Guid в качестве первичного ключа для вашей таблицы Users : идеально .

Таблица LogEntry

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