Ответ 1

Вот некоторые компромиссы:

Одиночный суррогат (искусственно создан):

• Для внешних ключей дочерних таблиц требуется только один столбец для ссылки на первичный ключ.

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

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

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

Натуральные составные таблицы с ключами:

• меньше индексов в базе данных

• меньше столбцов в базе данных

• проще/быстрее вставить тонну записей, так как вам не потребуется захватить генератор последовательности

• Обновление одного из ключей в соединении требует обновления каждой дочерней таблицы.

Тогда есть другая категория: первичные ключи с искусственным композитом

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

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

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

Первое, что нужно сделать, это настроить первичные и внешние ключи. Обычно таблица с полем id в качестве первичного ключа. Добавив client_id, ключ теперь является составным. И нужно переносить client_id на все дочерние таблицы.

Составной ключ основан на 2 суррогатных ключах и является пуленепробивным способом обеспечения целостности данных среди клиентов и внутри базы данных в целом.

После этого вы создадите представления (или, если используете Oracle EE setup Virtual Private Database) и другие различные структуры, чтобы база данных обеспечивала безопасность уровня строки (которая является темой, которой она владеет).

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

Резюме

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

Самое большое преимущество, на мой взгляд, следующее:

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

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

• Производительность запроса улучшается, так как db может искать атрибуты для поиска s.key, а затем присоединяться ко всей дочерней таблице с помощью одного числового ключа.

Натуральные ключи, особенно сложные NKey, делают код письма болью. Когда вам нужно присоединиться к 4 таблицам, предложение "where" будет намного длиннее (и проще испортить), чем когда были использованы одиночные SKey.

Суррогатные ключи - это "безопасный" маршрут. Естественные ключи полезны в нескольких местах, я бы сказал около 1% таблиц в db.

Ответ 2

Прежде всего, ваш второй уровень может быть выражен как минимум четырьмя различными способами, и все они имеют отношение к вашему вопросу. Ниже я использую псевдо-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)
);

В. Внешние ключи к натуральным клавишам

create table people (
  FirstName varchar(n) not null
    references FirstNames (FirstName),
  LastName varchar(n) not null
    references Surnames (Surname),
  primary key (FirstName, Surname)
);

С. Внешние ключи к суррогатным клавишам, дополнительный ключ суррогата

create table people (
  ID serial primary key,
  FirstName integer not null
    references FirstNames (ID),
  LastName integer not null
    references Surnames (ID),
  unique (FirstName, LastName)
);

Д. Внешние ключи к натуральным клавишам, дополнительный ключ суррогата

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, выше

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, выше

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, выше

create table ParentsOf (
  Person integer not null
    references People (ID),
  PersonParent integer not null
    references People (ID),
  primary key (Person, PersonParent)
);

Чтобы получить имена, вы должны присоединиться к таблице "people". Вам понадобится в общей сложности шесть объединений.

Д. Внешние ключи к натуральным клавишам, дополнительный суррогатный ключ в D, выше

Эта конструкция имеет ту же структуру, что и в C, как указано выше. Поскольку таблица "people" в D, дальше выше, имеет естественные ключи, ссылающиеся на таблицы "FirstNames" и "Surnames", вам понадобятся только два соединения с таблицей "люди", чтобы получить имена.

Об ORM

ORM не создают SQL так, как SQL-разработчик пишет SQL. Если разработчик SQL записывает инструкцию SELECT, которая требует шести соединений для получения имен, ORM может выполнить семь простых запросов для получения одних и тех же данных. Это может быть проблемой; это может быть не так.

О каскадах

Суррогатные идентификационные номера делают каждую ссылку на внешний ключ неявным, необъявленным "ON UPDATE CASCADE". Например, если вы запустите это выражение обновления против своей таблицы фамилий.,.

update surnames
set surname = 'Smythe'
where surname = 'Smith';

тогда все Смиты станут Смитами. Единственный способ предотвратить это - отменить разрешения на обновление "фамилий". Неявный, необъявленный "ON UPDATE CASCADE" - это не всегда хорошая вещь. Отмена разрешений исключительно для предотвращения нежелательных неявных "каскадов" не всегда хорошая вещь.

Ответ 3

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

Вещи, которые могут повлиять на ваш выбор:

• как обращаться с отдельными записями, которые могут иметь одинаковые естественные ключи. Например, идентичное имя и фамилия.
• как веб-клиент или мобильный клиент сохраняют сложный графический граф, если сервер назначил суррогатные ключи (требуется какой-то слой отображения). Альтернатива заключается в том, чтобы избежать проблемы с отображением и использовать назначенные клиентом UUID v4.
• следуя вышеизложенному, как вы справляетесь с разрешением конфликтов в временно отключенных средах, таких как мобильные приложения или где клиенты могут находить/делиться друг с другом без предварительной синхронизации с сервером. Идентификация объектов - важная концепция поддержки и решения этих проблем.
• Масштабируемость через очертание вашей базы данных может быть легкой или сложной на основе выбора ключа. Автоматическое увеличение суррогатных ключей трудно очертить и требует выбора фиксированного количества осколков a-priori, поэтому ключи не сталкиваются, в то время как v4 UUID-суррогатные ключи легки и могут быть назначены клиентом. Композитные и естественные ключи сложны, потому что ключ, в то время как относительно стабильный может все же измениться, и для этого требуется возможность переноса записей с одного осколка на другой.
• Как ваши клиенты управляют идентификацией объекта? Часто пользовательские интерфейсы требуют создания локального графика моделей для дальнейшей стойкости к "серверу в облаке". За это время до настойчивости эти объекты нуждаются в идентификации, и после настойчивости должно быть соглашение или сопоставление между идентификатором объекта сервера и идентификатором объекта клиента.
• Вы вынуждаете все, что находится над базой данных (включая сервер приложений) для решения проблемы сопоставления идентичности, или создаете ее в дизайне ключа базы данных, а также помогаете решить задачу масштабирования /sharding для db, пока вы это используете?

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

Ответ 4

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

Ответ 5

Я видел этот список критериев для первичного ключа. Я считаю это довольно хорошей отправной точкой для такого обсуждения

• уникальный
• стабильный (необязательно непреложный)
• неприводимым
• просто
• знак

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

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

CHECK ( jobtitle <> 'BOSS' OR salary > 100 )

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

Ответ 6

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

Пример с таблицей пользователя:

ID   Name        Value       DeletedFlag
1    Smith       78          0
2    Martin      98          0
3    John        78          1
4    Edouard     54          0
5    John        64          0

Джон заполнил информацию, затем решил удалить ее и заполнить новую.

Если вы не используете уникальный pk, вы не сможете справиться с этой ситуацией.

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

Он также быстрее восстанавливает индексы для целых чисел и занимает меньше места на диске.

Ответ 7

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

Пример.

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

STUDENT     COURSE
1           CS101
1           CS101

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

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

Ответ 8

Я думаю, вы неправильно поняли что-то фундаментальное в отношении данных:

1) Вы принимаете один идентификатор (имя человека - при условии, что оно однозначно идентифицирует человека), разделяя его на субатомные части, тогда из-за 6NF, помещая их в отдельные переменные отношения. Часто такой раскол делается по практическим соображениям, и первое имя/фамилия является распространенным примером; решение обычно делается по причине сложности, частоты и т.д. расщепления по сравнению с тем, что снова возвращает атрибут. Здесь раскол не практичен.

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

3) Для идентификаторов людей соединение с именами и фамилиями редко бывает адекватным. Идентификаторы обычно выбираются исходя из требуемого уровня доверия. Работодатель проверяет ссылки, квалификацию и т.д., Затем выдает ссылку на заработную плату. Полицейское предложение может потребовать осмотра водительских прав у дороги, но отпечатки пальцев будут приняты, если вы будете признаны виновными в совершении преступления. СУБД не может проверить человека, и поэтому числовое число с автоматическим добавлением также редко бывает адекватным.