Составные против суррогатных ключей для ссылочной целостности в 6NF
Возьмите три слоя информации:
Уровень 1: Информация
Этот слой содержит данные с UNIQUE природные индексы и суррогатный ключ, который легко переносится.
Table Surnames:
+-----------------------------+--------------+
| ID (Auto Increment, PK) | Surname |
+-----------------------------+--------------+
| 1 | Smith |
| 2 | Edwards |
| 3 | Brown |
+-----------------------------+--------------+
Table FirstNames
+-----------------------------+--------------+
| ID (Auto Increment, PK) | FirstName |
+-----------------------------+--------------+
| 1 | John |
| 2 | Bob |
| 3 | Mary |
| 4 | Kate |
+-----------------------------+--------------+
Натуральные ключи
Кроме того, две таблицы выше могут быть без ID и использовать фамилию и имя в качестве естественных первичных ключей, как объяснил Майк Шеррилл. В этом случае предположим, что слой ниже ссылки varchar скорее, чем int,
Уровень 2: Люди
В этом слое используется составной индекс. Это значение может быть UNIQUE или же PRIMARYв зависимости от того, используется ли суррогатный ключ в качестве первичного ключа.
+-----------------+--------------+
| FirstName | LastName |
+-----------------+--------------+
| 1 | 2 |
| 1 | 3 |
| 2 | 3 |
| 3 | 1 |
| 4 | 2 |
| ... | ... |
+-----------------+--------------+
Уровень 3: Родители
На этом уровне отношения между людьми изучаются через ParentsOf Таблица.
ParentsOf
+-----------------+-----------------+
| Person | PersonParent |
+-----------------+-----------------+
OR
+-----------------+-----------------+-----------------+-----------------+
| PersonFirstName | PersonSurname | ParentFirstName | ParentSurname |
+-----------------+-----------------+-----------------+-----------------+
Вопрос
Предполагая, что ссылочная целостность ОЧЕНЬ важна для меня по своей сути, и я буду иметь FOREIGN KEYS на этих индексах, так что я держу базу данных ответственной за мониторинг ее собственной целостности на этом фронте, и что, если бы я использовал ORM, она была бы такой же, как Doctrine, которая имеет встроенную поддержку составных первичных ключей...
Пожалуйста, помогите мне понять:
Список компромиссов, которые имеют место с использованием суррогатных ключей и естественных ключей на 1-м уровне.
Список компромиссов, которые имеют место при использовании составных ключей и суррогатных ключей на 2-м уровне, которые можно перенести на 3-й уровень.
Мне не интересно слышать, что лучше, потому что я понимаю, что между профессионалами по этой теме есть существенные разногласия, и это вызовет религиозную войну. Вместо этого я спрашиваю, очень просто и максимально объективно, насколько это возможно с человеческой точки зрения, какие компромиссы вы предпримете, передав суррогатные ключи каждому Слою против поддержки Первичных ключей (натуральных / составных или суррогатных / составных). Любой сможет найти человека, который НИКОГДА или ВСЕГДА не использует суррогатные ключи на SO и других веб-сайтах. Вместо этого, аргументированный анализ компромиссов - это то, что я больше всего оценю в ваших ответах.
РЕДАКТИРОВАТЬ: было отмечено, что пример фамилии является плохим примером для использования 6NF. Ради того, чтобы вопрос остался нетронутым, я собираюсь оставить его в покое. Если у вас возникли проблемы с представлением варианта использования для этого, лучшим вариантом может быть список "Продуктовых товаров". AKA:
+-----------------------------+--------------+
| ID (Auto Increment, PK) | Grocery |
+-----------------------------+--------------+
| 1 | Sponges |
| 2 | Tomato Soup |
| 3 | Ice Cream |
| 4 | Lemons |
| 5 | Strawberries |
| 6 | Whipped Cream|
+-----------------------------+--------------+
+-----------------------------+--------------+
| ID (Auto Increment, PK) | Brand |
+-----------------------------+--------------+
| 1 | Bright |
| 2 | Ben & Jerry's|
| 3 | Store Brand |
| 4 | Campbell's |
| 5 | Cool Whip |
+-----------------------------+--------------+
Пример натурального составного ключа:
+-----------------------------+--------------+
| Grocery | Brand |
+-----------------------------+--------------+
| Sponges | Bright |
| Ice Cream | Ben & Jerry's|
| Ice Cream | Store Brand |
| Tomato Soup | Campbell's |
| Tomato Soup | Store Brand |
| Lemons | Store Brand |
| Whipped Cream | Cool Whip |
+-----------------------------+--------------+
Рекомендуемые пары
+-----------------+-----------------+-----------------+-----------------+
| Grocery1 | Brand1 | Grocery2 | Brand2 |
+-----------------+-----------------+-----------------+-----------------+
Повторюсь, это тоже всего лишь пример. Это не то, как я бы рекомендовал продолжить, но это должно помочь проиллюстрировать мой вопрос.
Есть недостатки этого метода. Я еще раз повторю, что этот вопрос заключался в том, чтобы просить пройтись по преимуществам и недостаткам каждого метода ниже, а не выделять один из них лучше другого. Я полагаю, что большинство людей смогли посмотреть за пределы сомнительного характера этого конкретного примера, чтобы ответить на основной вопрос. Это редактирование для тех, кто не может.
Ниже приведены несколько очень хороших ответов, и если вам интересно, в каком направлении двигаться дальше, прочтите их.
КОНЕЦ РЕДАКТИРОВАНИЯ
Спасибо!
8 ответов
Вот некоторые компромиссы:
Единственный суррогат (искусственно созданный):
Всем внешним ключам дочерних таблиц необходим только один столбец для ссылки на первичный ключ.
Очень просто обновить естественные ключи в таблице без необходимости обновлять каждую дочернюю таблицу внешними ключами
Меньшие индексы первичного / внешнего ключа (т. Е. Не широкие). Это может ускорить работу базы данных, например, когда запись удалена в родительской таблице, необходимо выполнить поиск дочерних таблиц, чтобы убедиться, что это не приведет к созданию сирот. Узкие индексы быстрее сканируются (просто зря).
у вас будет больше индексов, потому что вы, скорее всего, захотите также индексировать любые естественные ключи, существующие в данных.
Натуральные композитные таблицы с ключами:
меньше индексов в базе данных
меньше столбцов в базе данных
проще / быстрее вставить тонну записей, так как вам не нужно будет захватывать генератор последовательности
обновление одного из ключей в соединении требует, чтобы каждая дочерняя таблица также была обновлена.
Тогда есть еще одна категория: искусственные составные первичные ключи
Я нашел только один случай, когда это имеет смысл. Когда вам нужно пометить каждую запись в каждой таблице для обеспечения безопасности на уровне строк.
Например, предположим, что у вас есть база данных, в которой хранятся данные для 50000 клиентов, и каждый клиент не должен был видеть данные других клиентов - что очень часто встречается при разработке веб-приложений.
Если каждая запись была помечена client_id поле, вы создаете среду безопасности на уровне строк. Большинство баз данных имеют инструменты для обеспечения безопасности на уровне строк при правильной настройке.
Первое, что нужно сделать, это настроить первичные и внешние ключи. Обычно стол с id поле в качестве первичного ключа. Добавляя client_id ключ теперь составной ключ. И надо нести client_id на весь детский стол.
Составной ключ основан на 2 суррогатных ключах и является пуленепробиваемым способом обеспечения целостности данных между клиентами и внутри базы данных в целом.
После этого вы создадите представления (или, если используете Oracle EE, настроите виртуальную частную базу данных) и другие различные структуры, чтобы позволить базе данных обеспечивать безопасность на уровне строк (это отдельная тема).
Предполагается, что эта структура данных больше не нормализуется до n-й степени. client_id Поле в каждом pk / fk денормализует иначе нормальную модель. Преимущество модели заключается в простоте обеспечения безопасности на уровне строк на уровне базы данных (что должны делать базы данных). Каждый выбор, вставка, обновление, удаление ограничено client_id ваша сессия в настоящее время установлена. База данных имеет информацию о сеансе.
Резюме
Суррогатные ключи всегда безопасная ставка. Они требуют немного больше работы для настройки и требуют больше памяти.
Самым большим преимуществом на мой взгляд является:
Возможность обновления PK в одной таблице и всех других дочерних таблицах мгновенно изменяется без какого-либо касания.
Когда данные перепутаны - и это произойдет в какой-то момент из-за ошибки программирования, суррогатные ключи значительно упрощают очистку, а в некоторых случаях это возможно сделать только потому, что существуют суррогатные ключи.
Производительность запросов улучшается, поскольку БД может искать атрибуты, чтобы найти s.key, а затем соединить все дочерние таблицы одним цифровым ключом.
Natural Keys, особенно составные NKeys, затрудняют написание кода. Когда вам нужно объединить 4 таблицы, "предложение where " будет намного длиннее (и его легче будет испортить), чем когда использовались одиночные SKeys.
Суррогатные ключи - это "безопасный" маршрут. Естественные ключи полезны в нескольких местах, я бы сказал, около 1% таблиц в дБ.
Прежде всего, ваш второй слой может быть выражен как минимум четырьмя различными способами, и все они имеют отношение к вашему вопросу. Ниже я использую псевдо-SQL, в основном с синтаксисом PostgreSQL. Определенные виды запросов потребуют рекурсии и более одного дополнительного индекса независимо от структуры, поэтому я не буду больше говорить об этом. Использование dbms, поддерживающего кластерные индексы, может повлиять на некоторые решения, но не думайте, что шесть объединений в кластерных индексах будут быстрее, чем простое чтение значений из одного охватывающего индекса; тест, тест, тест.
Во-вторых, на первом уровне действительно не так много компромиссов. Внешние ключи могут ссылаться на объявленный столбец not null unique точно так же, как они могут ссылаться на объявленный столбец primary key, Суррогатный ключ увеличивает ширину таблицы на 4 байта; это тривиально для большинства, но не для всех приложений баз данных.
В-третьих, правильные внешние ключи и уникальные ограничения будут поддерживать ссылочную целостность во всех четырех из этих конструкций. (Но см. Ниже "О каскадах".)
А. Внешние ключи для суррогатных ключей
create table people (
FirstName integer not null
references FirstNames (ID),
LastName integer not null
references Surnames (ID),
primary key (FirstName, LastName)
);
B. Внешние ключи к естественным ключам
create table people (
FirstName varchar(n) not null
references FirstNames (FirstName),
LastName varchar(n) not null
references Surnames (Surname),
primary key (FirstName, Surname)
);
C. Внешние ключи для суррогатных ключей, дополнительный суррогатный ключ
create table people (
ID serial primary key,
FirstName integer not null
references FirstNames (ID),
LastName integer not null
references Surnames (ID),
unique (FirstName, LastName)
);
D. Внешние ключи к естественным ключам, дополнительный суррогатный ключ
create table people (
ID serial primary key,
FirstName varchar(n) not null
references FirstNames (FirstName),
LastName varchar(n) not null
references Surnames (Surname),
unique (FirstName, Surname)
);
Теперь давайте посмотрим на таблицу ParentsOf.
A. Внешние ключи для суррогатных ключей в A, выше
create table ParentsOf (
PersonFirstName integer not null,
PersonSurname integer not null,
foreign key (PersonFirstName, PersonSurname)
references people (FirstName, LastName),
ParentFirstName integer not null,
ParentSurname integer not null,
foreign key (ParentFirstName, ParentSurname)
references people (FirstName, LastName),
primary key (PersonFirstName, PersonSurname, ParentFirstName, ParentSurname)
);
Чтобы получить имена для данной строки, вам понадобится четыре объединения. Вы можете присоединиться непосредственно к таблицам "FirstNames" и "Surnames"; Вам не нужно присоединяться через таблицу "Люди", чтобы получить имена.
B. Внешние ключи к естественным ключам в B, выше
create table ParentsOf (
PersonFirstName varchar(n) not null,
PersonSurname varchar(n) not null,
foreign key (PersonFirstName, PersonSurname)
references people (FirstName, LastName),
ParentFirstName varchar(n) not null,
ParentSurname varchar(n) not null,
foreign key (ParentFirstName, ParentSurname)
references people (FirstName, LastName),
primary key (PersonFirstName, PersonSurname, ParentFirstName, ParentSurname)
);
Этому дизайну требуется ноль соединений для извлечения имен для данной строки. Многим платформам SQL вообще не нужно читать таблицу, потому что они могут получить все данные из индекса по первичному ключу.
C. Внешние ключи для суррогатных ключей, дополнительный суррогатный ключ в C, выше
create table ParentsOf (
Person integer not null
references People (ID),
PersonParent integer not null
references People (ID),
primary key (Person, PersonParent)
);
Чтобы получить имена, вы должны присоединиться через таблицу "люди". Вам понадобится всего шесть соединений.
D. Внешние ключи к естественным ключам, дополнительный суррогатный ключ в D, выше
Эта конструкция имеет ту же структуру, что и в С, сразу выше. Поскольку таблица "people" в D, приведенная выше, имеет естественные ключи, ссылающиеся на таблицы "FirstNames" и "Surnames", вам потребуется всего два соединения с таблицей "people", чтобы получить имена.
О ОРМ
ORM не создают SQL так, как SQL-разработчик пишет SQL. Если разработчик SQL пишет оператор SELECT, которому для получения имен требуется шесть объединений, ORM обязан выполнить семь более простых запросов, чтобы получить те же данные. Это может быть проблемой; это не может быть.
О каскадах
Суррогатные идентификационные номера делают каждую ссылку на внешний ключ неявным необъявленным "ON UPDATE CASCADE". Например, если вы запустите этот оператор обновления для своей таблицы фамилий.,,
update surnames
set surname = 'Smythe'
where surname = 'Smith';
тогда все кузнецы станут смитами. Единственный способ предотвратить это - отозвать разрешение на обновление для "фамилий". Неявное, необъявленное "ОБНОВЛЕНИЕ КАСКАДА" не всегда хорошо. Отмена разрешений исключительно для предотвращения нежелательных неявных "каскадов" - это не всегда хорошо.
Использование естественных ключей позволяет выполнять более простые и быстрые запросы, поскольку нет необходимости объединять всю цепочку внешних ключей, чтобы найти "естественное" значение, например, для отображения на экране.
Я буду избегать чисто академического обсуждения здесь и рассмотрю несколько прагматических соображений, учитывая, что современный дизайн базы данных обычно должен учитывать масштабируемость, мобильность (отключенную операцию) и разрешение конфликтов, когда выбор ключа может оказать большое влияние.
Вещи, которые могут повлиять на ваш выбор:
- как работать с разными записями, которые могут иметь одинаковые естественные ключи. Например, идентичные имя и фамилия.
- как веб-клиент или мобильный клиент сохраняют сложный граф модели, если используются назначенные серверу суррогатные ключи (требуется какой-то уровень отображения). Альтернатива состоит в том, чтобы избежать проблемы сопоставления и использовать клиентские UUID v4.
- Исходя из вышеизложенного, как вы справляетесь с разрешением конфликтов во временно отключенных средах, таких как мобильные приложения, или где клиенты могут взаимодействовать / обмениваться данными друг с другом без предварительной синхронизации с сервером. Идентичность объекта является важной концепцией для поддержки и решения этих проблем.
- Масштабируемость с помощью шардинга вашей базы данных может быть простой или сложной в зависимости от выбора ключа. Автоинкрементные суррогатные ключи сложно осквернить и требуют априорного выбора фиксированного числа шардов, чтобы ключи не конфликтовали, тогда как суррогатные ключи на основе v4 UUID просты и могут быть назначены клиентом. Составные и натуральные ключи сложны, потому что ключ, хотя и относительно стабилен, все еще может измениться, и для этого требуется возможность переносить записи из одного сегмента в другой.
- как ваши клиенты управляют идентификацией объекта? Часто пользовательские интерфейсы требуют построения локального графа моделей для последующего сохранения на "сервере в облаке". В течение этого времени перед сохранением эти объекты нуждаются в идентификации, а после сохранения должно быть соглашение или сопоставление между идентификацией объекта сервера и идентификацией объекта клиента.
- Вы заставляете все, что находится над базой данных (включая сервер приложений), решать проблему сопоставления идентификаторов или встраиваете ее в конструкцию ключа базы данных, а также помогаете решить проблему масштабируемости / разбиения для базы данных, пока вы используете ее?
Мой совет - взгляните на характеристики системы в целом и не ограничивайтесь теоретическим дизайном БД, а тем, что будет хорошо работать для нетривиального полного стека, расположенного над базой данных. Выбор, который вы делаете для ключевого дизайна, может сделать или нарушить удобство использования системы и помочь или нанести ущерб сложности разработки, увеличивая или уменьшая ваше итоговое время выхода на рынок и общий компромисс между качеством и надежностью.
Частым случаем использования, который вы можете найти в базах данных, является создание версий истории
Пример с пользовательской таблицей:
ID Name Value DeletedFlag
1 Smith 78 0
2 Martin 98 0
3 John 78 1
4 Edouard 54 0
5 John 64 0
Джон заполнил информацию, затем решил удалить ее и заполнить новую.
Если вы не используете уникальный ПК, вы не сможете справиться с этой ситуацией.
В процессе разработки и производства очень просто пометить некоторые данные как удаленные и снять их, чтобы выполнять некоторые тесты или исправления данных, вместо резервного копирования, восстановления или путаницы.
Он также быстрее перестраивает индексы на целые числа и занимает меньше места на диске.
Однажды я увидел этот список критериев для первичного ключа. Я нахожу это довольно хорошей отправной точкой для такого рода обсуждения
- уникальный
- стабильный (не обязательно неизменный)
- неприводимым
- просто
- знакомый
Иногда возникает конфликт между двумя или более критериями, и мы должны идти на компромисс между ними. К сожалению, многие люди даже не задумываются о том, как спроектировать ключ, они используют какой-то автоматически сгенерированный ключ, может ли это быть столбец идентификации, guid или что-то еще.
Один недостаток суррогатных ключей состоит в том, что становится все труднее применять декларативные правила (большинство СУБД не поддерживают подзапросы в проверочных ограничениях). Я думаю, что правила, такие как:
CHECK ( jobtitle <> 'BOSS' OR salary > 100 )
Тем не менее, я считаю, что самая большая проблема с суррогатными ключами заключается в том, что вы можете сойти с рук с действительно странными конструкциями и даже не заметить.
Если наш составной ключ состоит из столбцов STUDENT и COURSE, база данных гарантирует, что мы никогда не введем повторяющиеся значения.
Пример.
С составным ключом это не разрешено, база данных предотвратит это.
STUDENT COURSE
1 CS101
1 CS101
Но если мы выберем суррогатный ключ в качестве ключа, нам нужно будет найти другой способ предотвратить такое дублирование.
Подумав, какие комбинации полей возможны, вы сможете лучше понять и понять проблему.
Я думаю, что вы неправильно поняли что-то фундаментальное в отношении данных:
1) Вы берете один идентификатор (имя человека - при условии, что он однозначно идентифицирует человека), затем разделяете его на субатомные части из-за 6NF, помещая их в отдельные переменные отношения. Часто такое разделение делается по практическим соображениям, и имя / фамилия является распространенным примером; решение обычно принимается на основе сложности, частоты и т. д. разделения по сравнению с тем, как снова собрать атрибут. Здесь разделение не практично.
2) 6NF всегда достижимо, но не всегда желательно. В этом случае усложняется определение ограничения, которое сможет проверять детали как действительные в комбинации (представьте, что вы разделили гранулы даты на день, месяц и год и сохранили каждую деталь в отдельных relvars!).
3) Для идентификаторов личности соединение имен и фамилий редко подходит. Идентификаторы обычно выбираются на основе требуемого уровня доверия. Работодатель проверяет справки, квалификации и т. Д., Затем выдает справку о заработной плате. Предложение полиции может потребовать осмотра вашего водительского удостоверения на обочине дороги, но отпечатки пальцев будут сняты, если вы будете осуждены за преступление. СУБД не может проверить человека, и поэтому целое число с автоматическим приращением также редко бывает адекватным.