Хотя я живу в Копенгагене и в основном перемещаюсь по городу на велосипеде, у меня есть и машина, чтобы добираться до других частей страны. В Дании автомобили через каждые два года проходят обязательный официальный осмотр; я проходил его за свою жизнь несколько раз. Несколько лет назад механик, выполнявший осмотр, сообщил мне о том, что у машины неправильный номер кузова.
Я немного занервничал, потому что покупал машину с рук, и внезапно задался вопросом, действительно ли всё так, как я думал. Неужели я непреднамеренно купил краденную машину?
Но механик просто подошёл к своему компьютеру, чтобы исправить ошибку. И тогда у меня возникло совершенно другое опасение. Когда программируешь больше десятка лет, то учишься предвидеть разные типичные режимы отказа. Так как номер кузова — очевидный кандидат на должность естественного ключа, я заранее предвидел, что изменение этого номера будет или невозможно, или приведёт ко всевозможным каскадным эффектам, и в конечном итоге к уничтожению официальных записей, больше не признающих, что машина принадлежит мне.
Но оказалось, что писавший программное обеспечение знал своё дело, потому что механик просто поменял номер, и на этом всё закончилось. Это было пять или шесть лет назад, я по-прежнему владею этой машиной, и у меня никогда не возникало проблем с официальными записями о владении.
Уникальность
Я рассказал эту историю потому, что сейчас наблюдаю за студенческим курсом по базам данных и информационным системам. Так как этот курс предназначен для студентов, не имеющих опыта работы, он движется педагогически поступательно. Чтобы обучить понятию ключей баз данных, он начинает с естественных ключей. С точки зрения обучения это логично, но в результате этого молодёжь, с которой я работаю, предлагает архитектуры баз данных с естественными ключами.
Я не пытаюсь никого обвинить: прежде чем научиться ходить, нужно научиться ползать.
Тем не менее, эта ситуация заставила меня задуматься над таким вопросом: бывают ли хоть когда-нибудь естественные ключи хорошим выбором?
Давайте рассмотрим пример. Для нашего небольшого проекта мы создали базу данных из 50 лучших ресторанов мира. Мои коллеги-студенты предложили следующую структуру таблицы:
CREATE TABLE Restaurants (
year TEXT NOT NULL,
rank TEXT NOT NULL,
restaurantName TEXT NOT NULL,
cityName TEXT NOT NULL
);Да, на этом этапе такое определение таблицы вообще не определяет никаких ключей. Я на это не жалуюсь. В конце концов, ещё месяц назад студенты, вероятно, и не видели за свою жизнь ни одной таблицы базы данных.
Однако, согласно курсу, будет естественным определить ключ для таблицы Restaurants как комбинацию restaurantName, cityName и year. Допущение здесь заключается в том. что название и город идентифицируют ресторан уникальным образом.
В этом конкретном примере такое допущение, вероятно, будет соблюдаться. Пока. В конце концов, датасет не очень велик, а ресторанам такого уровня важно иметь узнаваемые названия. Я бы предположил, что в мире есть только один Nobelhart & Schmutzig.
Тем не менее, хороший архитектор ПО должен подвергать сомнениям подобные фундаментальные допущения. Действительно ли название и город — это естественный ключ? Легко представить, что это не так. Что, если мы дополним ключ ещё и страной? Отлично, но что, если у нас будет ресторан China Wok в Спрингфилде, США? [Прим. пер.: в США более сорока населённых пунктов с таким названием, поэтому его носит и вымышленный город из «Симпсонов».] Не совсем уникально. Говорите, добавить ещё и штат? Вероятно, всё равно ключ окажется неуникальным.
Идентификация
Обеспечение уникальности — лишь первая из множества проблем естественных ключей. Можно достаточно быстро прийти к выводу, что для базы данных ресторанов, вероятно, лучшим выбором станет синтетический ключ.
Но что насчёт «естественных» естественных ключей, так сказать? Например, номера кузова автомобиля. Это уже непрозрачное число, которое, вероятно, берётся из какой-то базы данных. А может быть, использовать персональный идентификационный номер? У нас в Дании есть номер CPR, и я знаю, что Social Security Number США примерно ему аналогичен.
Если вы проектируете базу данных, уже содержащую такой персональный идентификационный номер, у вас может возникнуть искушение использовать его в качестве естественного ключа. В конце концов, он уже используется как ключ где-то ещё, так что гарантированно является уникальным, ведь так?
Да, номер может уникальным образом идентифицировать человека, но обратное может и не быть истинным. У человека может быть несколько идентификационных номеров. По крайней мере, они могут добавляться со временем.
Например, по исторически-техническим причинам датский номер CPR содержит такую информацию, как дата рождения и пол человека (которых не должно быть в ключах). С 2014 года новый закон позволяет транссексуальным гражданам получать новый номер CPR, отражающий их воспринимаемый гендер, поэтому у одного человека может быть несколько номеров CPR. Возможно, не больше одного в конкретный момент времени, но два в течение срока жизни.
Даже если уже существующие ключи гарантированно оказываются уникальными, мы не можем допускать, что уникальность становится причиной биекции. Если вы используете внешний уникальный ключ, то можете потерять из виду сущности, которые пытаетесь отслеживать.
Это уникально не только для людей, но и для автомобилей, для велосипедов (тоже имеющих номер рамы), для сетевых карт и так далее.
Опечатки
Кроме того, даже если вы нашли естественный ключ, который гарантированно уникален и отслеживает сущность, которую вам нужно отслеживать, есть последний аргумент против использования в системе внешне определяемого ключа: ошибки ввода данных.
Вернёмся к истории о номере кузова моей машины. Механик, обнаруживший расхождение, чётко интерпретировал его как опечатку.
Программируя уже больше десятка лет, я понял, что рано или поздно в ваших данных появятся ошибки. Это могут быть канцелярские ошибки, опечатки конечного пользователя или ошибки преобразования данных при импорте из внешней системы. Или даже ошибки преобразования данных в одной системе в процессе апгрейдов и миграций.
Систему следует проектировать так, чтобы обеспечить возможность исправления данных. В том числе и исправления внешних ключей, то есть номеров кузова, государственных идентификационных номеров и так далее. Это означает, что вы не можете использовать такие ключи в качестве ключей баз данных в вашей собственной системе.
Эвристика
На ранних этапах моей карьеры много раз бывало так, что я решал использовать «естественный ключ» в качестве ключа моей базы данных. Насколько помню, я жалел об этом каждый раз.
Сегодня я следую чёткой эвристике: всегда пользоваться синтетическими ключами для таблиц баз данных.
Заключение
Бывают ли случаи, когда правильно использовать естественные ключи при проектировании базы данных? Мой опыт говорит, что нет. В конечном итоге, вне зависимости от вашей уверенности в том, что естественный ключ стабилен и корректно отслеживает сущность, случаются ошибки данных. В том числе и ошибки в этих естественных ключах.
У вас должна быть возможность исправлять такие ошибки без потери контроля за относящимися к ним сущностям. Вы пожалеете об использовании естественных ключей. Пользуйтесь синтетическими.
Комментарии (114)
petropavel
05.06.2024 08:48+11Не хватает истории "я угнал машину, перебил номера, привёз на сервис, смотрите, говорю, тут номер кузова неправильный, исправьте там у себя"
Akina
05.06.2024 08:48+3Если кого-то (или что-то) заставить делать два дела сразу, как правило оба делаются хреновато.
Так и тут. Назначение первичного ключа (ПК) - уникальная идентификация записи. Всё. И проистекающее из этого использование - поддержание средствами СУБД целостности, в том числе ссылочной. По-хорошему, значение ПК, исходя из его функции и связанных с этим применений, не должно быть видно пользователю и нигде, и никогда - за абсолютной для этого юзера ненадобностью.
Однако на ПК навешивают ещё одну функцию - обеспечение требования уникальности. Точнее, эта функция у него уже есть, но её расширяют в область взаимодействия с пользователем. И вот именно с этого момента, приобретя фактически вторую функцию, ПК начинает работать "не очень". А паровозом к этому "не очень" идут все описанные в статье (и ещё куча не описанных) проблемы. Ну и кто тут ССЗБ? явно не сам ПК.
Бывают ли случаи, когда правильно использовать естественные ключи при проектировании базы данных?
Конечно. Композитный естественный первичный ключ в adjacency table.
Ivan22
05.06.2024 08:48+2композитный ключ - это само по себе антипаттерн
Akina
05.06.2024 08:48+12композитный ключ - это само по себе антипаттерн
Эмм... это почему же? какой смысл в связующую таблицу на два поля добавлять ещё и синтетический ключ - чтобы раздуть её полем, которое никогда и нигде не будет использовано? и чтобы в явной форме создать уникальный индекс, который тоже сожрёт дополнительное дисковое пространство без какого-либо профита?
Какие у вас есть основания называть композитный ключ антипаттерном - ну может кроме того, что он вам лично не нравится?
martin_wanderer
05.06.2024 08:48+2И всё-таки иногда такой смысл есть - когда приходится эту таблицу постепенно перекачивать в другую БД и оказывается важно знать, какая запись уже выгружена, а какая - ещё нет
Akina
05.06.2024 08:48+1Для идентификации записи - в том числе для указанного случая,- в т.ч. и существует первичный ключ. И нет никакой разницы, композитный он или нет.
martin_wanderer
05.06.2024 08:48+1При миграции данных важно, чтобы в разных таблицах был единый способ идентификации записи
Ivan22
05.06.2024 08:48ну исключения бывают, и это таблицы на которые никакие другие ссылаться никогда не будут. Но если вдруг выяснится что нужен FK на таблицу с композитным ключем, начинается веселье
FAZAA
05.06.2024 08:48Отмечу, что не композитный ключ быстрее (но не всегда) работает при апсертах и джойнах
GeniyZ
05.06.2024 08:48+4Для таблиц организующих связь других, как многие ко многим, композитный ключ — самое то!
Ivan22
05.06.2024 08:48+17CREATE TABLE Restaurants (year TEXT NOT NULL,rank TEXT NOT NULL,restaurantName TEXT NOT NULL,cityName TEXT NOT NULL);Однако, согласно курсу, будет естественным определить ключ для таблицы
Restaurantsкак комбинациюrestaurantName,cityNameиyear.Ладно студенты, но как нормальный датамоделер может в таблицу ресторанов запихнуть year (это же год в котором выставлена оценка) ну и еще и сама оценка (Rank). Это все свойства не ресторана, а оценки. Т.е. нужна отдельная таблица Ranks где есть FK на ресторан, year и сам rank. А ресторан уникально определяется самим собой, иначе вообще что будет хранится в таблице
Restaurantsпри наличии рейтингов за несколько лет?p.s. так что проблема с естественным PK тут по мне еще и не на первом месте. И это я только одну таблицу увидел в этой БД
MeGaZip
05.06.2024 08:48+1Может year это дата основания/открытия ресторана
Ivan22
05.06.2024 08:48+1может и так. Однако потом текст "Допущение здесь заключается в том. что название и город идентифицируют ресторан уникальным образом. "
Если название и город уже ключ - зачем год???
Dolios
05.06.2024 08:48+4В моём городе 3 макдоналдса отрылись в один год. Хз, что они там собрались идентифицировать..
Gummilion
05.06.2024 08:48+2Никого не волнует, какая у ресторана была оценка 20 лет назад - смотрят по текущей. Даже если для каких-то целей нужна история оценок, если мы часто фильтруем рестораны по оценке, имеет смысл сделать денормализцию и таки добавить оценку к ресторану.
ris58h
05.06.2024 08:48+1имеет смысл сделать денормализцию и таки добавить оценку к ресторану
Имело бы, если бы там не было года в таблице. Если это таблица ресторанов, то год не нужен. Если это таблица оценок, то не нужны название и город.
Finesse
05.06.2024 08:48Оверинжениринг тоже приводит к проблемам. Смотреть нужно по задачам, которые база должна решать.
nickolaym
05.06.2024 08:48+2Про биекцию непонятно. Это что-то плохое, или это что-то недостижимое, или что автор хотел сказать? Фраза построена... странно.
safari2012
05.06.2024 08:48Неправильный номер кузова и мне вбивали ещё 90е годы на отечественные жигули. Исправили, правда тогда это всё тянуло за собой гемор и очереди. Сейчас наверное проще решать.
А ещё мне на правах категорий ABC в те же "лихие 90е" случайно категорию A напечатали с прочерком. И поверх синей печатью два раза зафигачили "разрешено". Всё бы ничего, но через 10 лет при смене прав пытались отказать, хотя я принес карточку из автошколы с записью о сданном экзамене. Случайно мимо проходил знакомый, сосед по подъезду, а по совместительству капитан том самом подразделении ГАИ, где всё оформлялось. Он и помог решить.
DustCn
05.06.2024 08:48+11Вот суть вопроса - нахрен биекции когда у тебя в соседях капитан подразделения!!
safari2012
05.06.2024 08:48+1А я понятия не имел. Сосед жил двумя этажами ниже и мы не очень то общались.
LF69ssop
05.06.2024 08:48Странная история.
А при утере прав вам бы вовсе отказали в замене? Или таки подняли бы свою картотеку в которых все ваши категории указаны?
VasiliyLiGHT
05.06.2024 08:48Сейчас наверное проще решать
Буквально вчера смотрел ролик на канале iling show, они пытались рафик на учёт поставить. Если вкратце: на кузове два VIN, один неполный, второй полный, но первые три цифры писали краской, которая за время испарилась. На всё про всё ушло 2,5 месяца. Сначала восстановили VIN (набили новый), потом добивались исправления опечатки в документах. Правильно - X1D, в доках X1B.
Kerman
05.06.2024 08:48+1Прочитав текст, я сделал вывод, что под "естественным" ключом подразумевается ключ из чужой ИС. Собственно вывод предсказуем: не доверяй никому. Особенно так делать нельзя, если планируется держать данные из разных сторонних ИС.
edo1h
05.06.2024 08:48+2Причём тут чужие ИС?
Набрал в гугле «естественный ключ», читаю:
Естественный Ключ (ЕК) – набор атрибутов описываемой записью сущности, уникально её идентифицирующий (например, номер паспорта для человека) или Суррогатный Ключ (СК) – автоматически сгенерированное поле, никак не связанное с информационным содержанием записи.
Даже не хочется перефразироватьKerman
05.06.2024 08:48+1При том, что номер паспорта - это ключ из чужой информационной системы, например. Он ни в одном месте не естественный )
Ivan22
05.06.2024 08:48+2так и есть, что в одной системе суррогатный ключ - то в другой естественный.
А когда данные проходят через пяток разных систем, они могут в итоге обрасти полудюжиной ключей разной степени суррогатности.
edo1h
05.06.2024 08:48то, что существует сторонняя ИС, в которой хранится номер паспорта, это несущественное в данном случае знание. доступа к той ИС у нас всё равно нет.
другой пример, у нас есть архив записей с видеокамеры, в качестве естественного ключа для инцидентов можно использовать дату + время начала события (я не говорю, что это удачное решение, просто иллюстрация естественного ключа, который не связан с какой-то внешней ИС).
или для всяких файлообменных вещей в качестве естественного ключа для файла часто используется хэш от его содержимого.
Grikhan
05.06.2024 08:48+4Ну, во первых, натуральный ключ - это всегда сущность предметной области. Выбирать необязательный атрибут в качестве ключа - так себе занятие. Номер кузова - это не номер всего автомобиля, а номер его кузова (удивительно, кто бы мог подумать). Для ГАИ это будут одни обязательные атрибуты, для СТО - другие. Например, для СТО - это объект обслуживания - vin автомобиля. При этом надо определить в результате бизнес-анализа vin из документа или vin с автомобиля? Что должно соответствовать чему? Что из этого мастер-данные, а что - производная. Адрес ресторана - это не сущность предметной области ресторанного бизнеса - это предметная сущность почтовой службы. Больше того, в самой постановке "мы создали БД ресторанов и надо выбрать ключ" - критическая ошибка постановки. Чтобы выбрать состав БД нужно сначала понять - зачем? В чем ее бизнес-значение? Что вы хотите от БД с именем ресторана, годом, оценкой и городом? Ваша табличка БД в целом лишена всякого смысла в отрыве от контекста, не только ключ. Может быть вы табличку с объектами в год по городам считаете и вам все равно на имя и rank? Ну тогда ключи "город" и "год" - вполне себе ключи.
Ivan22
05.06.2024 08:48+1В теории все верно. Но на практике в датамоделенге есть определенные шаблоны и устоявшийся порядок вещей. Например если есть таблице поле ID - на 90% мы знаем что это PK и есть, ну или если где-то в БД существует таблица с названием "Restaurants" то мы ожидаем что там будет список ресторанов и гранулярность: одна строка = один ресторан. Всякое исключение из этого правила должно быть обоснованно и дважды серьезно продумано.
Grikhan
05.06.2024 08:48Мне нравится этот шаблон и устоявшийся порядок на 90%! Предположение - мать всех ошибок. Чтобы не ошибаться в 10% случаев (на самом деле, в подавляющем большинстве случаев), не надо предполагать. Совершенно с вами согласен, что название должно отражать суть таблицы. Но имя- это имя. Это якорь после того, как вы узнали что это за таблица на самом деле - чтобы эффективно вспомнить. Может быть это ресторан года в данном городе? Или вообще представленность СЕТЕЙ ресторанов по городам с какого года? Предположений может быть очень много. Необходимо исходить из бизнеса, а не предположений изолированного в подвале DBA. Если в таблице есть ID - это скорее всего вообще синтетический ключ, а не натуральный. И если вы будете использовать именно его, то в большинстве случаев зря потратите ресурсы и время.
Ivan22
05.06.2024 08:48Может быть это ресторан года в данном городе?
вот и не надо так делать, созда "Restaurants_year_rank" с FK на таблицу ресторанов, и все будут счастливы.
Необходимо исходить из бизнеса,
Это все равно что сказать - необходимо иметь полную документацию. Верное, но абсолютно бесполезное утверждение. Ибо в реальном мире всегда приходится заниматься дебагингом кода без документации, ну и демоделингом модели данных заодно. И тут грамотно (по шаблонам) созданная модель - это как красиво написанный код, помогают, в разы ускоряя процесс
Portnov
05.06.2024 08:48+8Имхо главная проблема натуральных ключей — в том, что "натуральность" меняется со временем, и делать соответствующие доработки в ПО может оказаться слишком дорого. Ну, грубо говоря, ИНН сейчас есть у всех граждан РФ и не меняется в течение жизни, так что если вы делаете что-то типа госуслуг, то почему бы не сделать его первичным ключом? А через год принимают закон, по которому ИНН становится можно менять по заявлению, и... ой.
MiyuHogosha
05.06.2024 08:48А потом там закодированы данные региона и налоговой в той ИНН. Аока. А в итоге это может стать артефактом, если отделение переедет?
navferty
05.06.2024 08:48Нет, при переезде ИНН остаётся - то есть там закодирован скорее "регион в момент получения ИНН". Но с самим посылом о том, что ИНН - плохой первичный ключ, я согласен. Например, наличие (пусть и вследствие ошибки) двух ИНН у одного человека вполне возможно.
vikarti
05.06.2024 08:48Ну так выше был пример - хватило дури закодировать пол в местный аналог ИНН а потом решили что если мы считаем что человеку пол менять (ну или приводить записи в официальных документах с ним) можно - то надо и тут править. УПС.
kidaig
05.06.2024 08:48+12Свой первый естественный ключ в БД я использовал более 20 лет назад. Это было поле ИНН в таблице контрагентов. Ведь он же такой индивидуальный и такой уникальный! По крайней мере, так казалось вчерашнему студенту. Спустя пару недель начались небольшие неудобства. Потом начались проблемы, стопорение бизнес-процессов, путаница в контрагентах, кажется даже умудрились деньги отправить в неправильную организацию. Меня пинали все - от главбуха до уборщицы.
Автор совершенно прав: никогда, ни при каких обстоятельствах не используйте естественные ключи. Даже если вам угрожают пистолетом.sigprof
05.06.2024 08:48+2В общем случае ИНН организации не является уникальным идентификатором — например, ИНН филиала вуза может совпадать с ИНН головного вуза (в результате чего регулярно возникают различные проблемы).
vis_inet
05.06.2024 08:48+1Верно, в этом случае у них должны быть разные КПП.
Yumado
05.06.2024 08:48+1а в случае ИП что должно быть разным для разных филиалов ?
Хе.
Не ИНН не КПП не являются PK
Бух-ия заведет одного контрагента с несколькими наименованиями
Сережа*
Сережа услуги*
Сережа долг по суду*
Naf2000
05.06.2024 08:48У ИП филиалов нет. Но насчет не может быть ключом - согласен
Yumado
05.06.2024 08:48Согласно ст. 55 ГК РФ, обособленное подразделение может открывать только юридическое лицо.
Мы говорим и употребляем в слово филиал. В данном контексте и в частном случае, например склад за городом.
Так что филиал возможен, есть и будет.
А то что вы называете словом филиал с отдельным КПП это обособленное подразделение.
Naf2000
05.06.2024 08:48Тогда нужно выработать словарь терминов. Потому что одно дело говорить об официальных юр.лицах и ИП. Это одно. Говорить о партнерах (обычно формальный набор юр.лиц в руках общих владельцев) - это другое, а можно говорить о точках доставки (склад за городом). И это третье.
Потому что доставку при продаже разумно организовать с точностью до точки. А вот взаиморасчеты вести - нет. Однако тут можно вести взаиморасчеты (параллельно) по юр.лицам и по партнерам (менее формально и в суд с этим не пойдешь).
hogstaberg
05.06.2024 08:48+2Я так и не понял что мешает одновременно использовать в базе номер кузова как первичный ключ (ну он же реально должен быть уникальным, а если вы пытаетесь забить в базу такой же номер второй раз, то должно ругнуться и сказать: "такой номер уже есть, вот он, давайте разбирайтесь кто из них правильный, а кто с опечаткой/машину спёртую водит") и дать возможность пользователю его менять?
Ivan22
05.06.2024 08:48+5Ну сама по себе возможность менять (да еще и пользователю) PK - это очень плохо, ибо ссылочная целостность может поплыть в самых неожиданных местах.
Vin кузова - это просто свойство автомобиля, такое же как и модель и цвет и госновмер. PK же суррогатный существует независимо и вообще пользователям не должен быть виден
vvbob
05.06.2024 08:48VIN хоть и редко, но все-таки разрешено на законных основаниях перебивать. Вот например недавно смотрел видосик с приключениями автоблоггеров, которые ставили на учет старую машину, у которой на заводе номер был пробит с ошибкой. Предыдущим владельцам его поставили на учет как есть, а им отказались ставить, из-за некорректности. Экспертиза, заключение, разрешение на смену номера, и вот у машины изменился VIN. Если в БД он будет использоваться в качестве PK, то вы тут-же поимеете лютый гемор с ссылочной целостностью, с интеграциями с другими системами, возможно даже появятся дубли машин, одни со старым вином, другие с новым..
В общем - если не хочется отстрелить себе в будущем ноги, лучше реально не использовать ВИН в качестве первичного ключа, и уж тем более не стоит давать пользователям возможность эти ПК редактировать хоть каким-то способом. Вообще им незачем его видеть даже.
PAL_habr
05.06.2024 08:48+1Уникальность это в данном случае не самое главное. Если вин будет первичным ключе, то его изменение может потребовать каскадных изменений во всех связях, где он используется. Вот это проблема.
Допустим есть у вас таблица дополнительно, где хранится история тех.осмотров машин (каждая машина проходит несколько тех.осмотров). Если вин - первичный ключ, то его изменение в таблице машин (где он первичный ключ) потребует изменения соответствующих идентификаторов в таблице техобслуживаний.
surly
05.06.2024 08:48Если СУБД поддерживает конструкцию «ON UPDATE CASCADE», то львиная доля проблем со ссылочной целостностью снимается.
DBalashov
05.06.2024 08:48+1> Если вин будет первичным ключе, то его изменение может потребовать каскадных изменений во всех связях, где он используется
И изменённые записи в БД сразу перестанут соответствовать уже десяткам и сотням выданным на их основе печатным документам и далее по цепочке другим документам на их основе :)
Kahelman
05.06.2024 08:48Почитав статью и комментарии возникло беспокойство по поводу качества специалистов по информационным системам.
Посмотрите нормальную книжку по реляционным базар данных: первичный ключ это набор атрибутов сущности, которые ее однозначно идентифицируют.
Отсюда следуют 1,2,3 и N-ая формы нормализации
Вот и статья на хабре ищется.
https://habr.com/ru/articles/254773/
Про искусственные ключи в реляционной теории пробе ничего не говориться, хотя тут смотря как атрибуты интерпретировать.
Далее надо смотреть на предметную область.
Если у нас есть таблица как в примере:
Year, rank, name, city
То в общем случае с точки зрения реляционной теории это нормальное представление данных.
В конце концов мы тут одним избежать хранения дублирующиеся данных в БД.
Допустим у нас 3 ресторана с одним именем: МакДональдс в городе Мухосранск, которые в 2024 году имели ранк 5
Му очень прекрасно ужимаем данные до 1 записи:
2024,5, Макдональдс, Мухосранск
Поскольку запись уникальна , то она представляет собой первичный ключ для таблицы.
И если в начале года откроется еще один ресторан Макдональдс, который в середине 2024 года получит ранк 5, то нам ничего не придётся менять.
Как мы будем различать рестораны Макдональдс 1, Макдональдс 2 и т.д. Это дело десятое и зависит от конкретной предметной области.
Но естественные или искусственные первичные ключи это не вопрос реляционной теории.
Kahelman
05.06.2024 08:48+1Кстати, тут в голову пример пришёл, который показывает что искуственнне ключи только создают видимость что решают проблему:
Если у вас в базе два Ивана Иванивича, которые родились в один день в одном городе.
Вы же используете искусственные ключи как профессионал:
У вас в базе id1 -Иван Иванович ,
id2-Иван Иванович, а поскольку вы ещё и продвинутый, то в качестве ID вы Guid использовали.
И вот к вашему кассиру приходит Иван Иванович за зарплатой…
Вы как его идентифицировать будете?
Он же ваш GUID не знает?
Так что при наличии искусственного ключа проблема не решается без дополнительных аттрибутов.
Ivan22
05.06.2024 08:48+5ну у тебя как минимум и система и юзеры видят что Иванычей двое, дальше уже разберутся по номеру паспорта или еще чему, а без суррогатника был бы там Иваныч один. И второй остался бы без зарплаты и кассир рисковал получить в морду, вот к чему проблемы модели данных приводят
Kahelman
05.06.2024 08:48Вы добавляете атрибуты в запись, которые делают набор уникальным, т.е по сути- естественные ключи ( номер паспорта)
tsukasa_mixer
05.06.2024 08:48+4Внезапно, номер паспорта в маштабах страны не является уникальным.
Да и он может многократно менятся в течении жизни.т.е. синтетический ключ, это защита от проблем расширения и маштабирования системы в течении жизненного цикла.
Т.е. как пример, я потерял как я думал свидетельство о рождении.
Сходили заказали востановить, мне выдали новое и мы нашли старое.
И вот в чем соль у меня теперь 2 свидетельства с разными номерами, но оба легитимны.
Ivan22
05.06.2024 08:48наличие естественных (бизнес) ключей, не отменяет необходимость иметь суррогатный
MiyuHogosha
05.06.2024 08:48В принципе да. это скорее неверное использование ключа. Понадобится SELECT employee WHERE ITN = "табельный номер".
GUID останется когда человек уйдет с работы и ему не надо платить зарплату, а вот данные о нем надо хранить
Kahelman
05.06.2024 08:48Если есть табельный номер то guid не нужен. Поскольку табельный номер это уникальный ключ в пределах предприятия.
Вы предлагаете генерить два ключа и говорите что это класс.
Kahelman
05.06.2024 08:48Товарищи минусующие, вы бы хоть пару строчек черкнули….
Перейдите по. Ссылке на оригинал и почитайте комменты. Там как-раз пишут что синтетические ключи это не панацея.
navferty
05.06.2024 08:48+3Табельный номер могут ошибочно присвоить дважды одному человеку.
По решению начальства табельные номера "обнуляются", и начинают выдаваться заново с номера 1 (пропуская те номера, которые присвоены ещё работающим сотрудникам)
Табельные номера могут совпадать у разных сотрудников в результате слияния двух организаций.
Ivan22
05.06.2024 08:48да и вообще собрали БД работников холдинга из 50 предприятий. И-и-и-и-и??
navferty
05.06.2024 08:48Это были примеры случаев, когда использование табельного номера (используемого, например, в бухгалтерских документах) в качестве первичного ключа в БД может привести к проблемам. Ваш случай тоже вполне укладывается в этот ряд.
plumqqz
05.06.2024 08:48Вы правы, искусственный ключ по сути утверждение - "а вот это мы будем называть гнвоерк" и не более.
Соответственно, в некоторой базе с искусственным ключом один Иван Иваныч запросто может явиться во множестве ипостасей, как индийские боги, и искусственные ключи просто позволяют скрыть эту проблему, причем в первую очередь от разработчика.
По-хорошему, как мне кажется, надо иметь а)искусственный ключ, причем желательно не int, а что-то типа UUID или подобного б)уникальный естественный ключ (или что-то более замысловатое)Ivan22
05.06.2024 08:48+1так и есть, причем в аналитике даже термин уже давно для б) cуществует- "бизнес ключ"
sYB-Tyumen
05.06.2024 08:48+1Да. Естественный ключ часто может являться злом.
Но когда мы студентами обнаружили, что существует Erwin Examiner, оказалось, что самая распространенная ошибка - это забыть отслеживать уникальность естественного ключа при наличии синтетического.
Karington
05.06.2024 08:48+10Поделюсь своим, никому не нужным опытом
Тезис первый -- естественный ключ это очень хорошо, но только в том случае, если он никогда не меняется.
Тезис второй: Естественный ключ всегда будет меняться.
Исходя из тезисов 1 и 2: Лучше всегда использовать синтетический ключ.
Адрес дома, VIN автомобиля, фамилия, имя, отчество, рост, номер паспорта, номер СНИЛС, ИНН, корпоративный email, логин, код сотрудника, да даже пол и цвет кожи -- это всё не константные значения. И не изменятся они только в том случае, если вы не доживёте до их изменения: уволитесь или умрёте раньше.
Если кто-то не верит в тезис номер 2: то подумайте о том, что границы стран двигаются и даже у дома может измениться название улицы и страна в которой он находится
Akina
05.06.2024 08:48+2Если кто-то не верит в тезис номер 2
Достаточно учесть, что любой естественный ключ присваивается (его значение создаётся) человеком. Прямо или косвенно (через какую-то систему нумерации и учёта) - неважно. Даже штамп времени - и тот меняется при изменении параметров часового пояса или, не приведи господи, системы летосчисления. И вообще - человек, он, знаете ли, такой проказник...
Я вон там повыше слегка потроллил народ насчёт композитных ключей... до сих пор никому даже в голову не пришло, что комбинация двух искусственных ключей ну никак не может стать естественным ключом.
morijndael
05.06.2024 08:48Даже штамп времени - и тот меняется при изменении параметров часового пояса или, не приведи господи, системы летосчисления.
<зануда>Unix timestamp как раз не прыгает, потому что это просто количество секунд с определенного момента. Все переводы часов накладываются на него уже при конвертации в человеческий формат</зануда>
markedo
05.06.2024 08:48<зануда>Уже давно существую теории, что время - величина не равномерная и может течь по-разному. Равность секунд это лишь презумция -- договорённость между людьми для удобства. И эта презумция вполне может измениться. Не говоря уже о том, что даже самые точные часы имеют погрешность и через миллионы лет может набежать сдвиг в дни и годы между фактическим событием и временным штампом.</зануда>
plumqqz
05.06.2024 08:48"Тезис второй: Естественный ключ всегда будет меняться."
Разумеется нет. Вот, предположим, пришел некий Пупкин Василий Насильевич ставить машину с вином таким-то на учет, о чем и занесли факт в таблицу "заявления о постановке на учет" - дата-время, фио, паспортные данные, вин, что-то еще.Все, теперь делай что хочешь, но прошлое у греков не могли изменить даже боги, чего уж тут говорить о разработчиках.
Более того, изменения этих данных в принципе не должно быть, это уже предпосылка мошенничества.
Паспортные данные, время и вин в данном случае - отличный неизменный естественный ключ (я от него брал бы хеш, так удобнее и короче, но в общем разница непринципиальная).
Naf2000
05.06.2024 08:48А завтра стукнуло ему 45 лет и пришлось идти менять паспорт
plumqqz
05.06.2024 08:48И что? Он с новым паспортом окажется в прошлом?
Naf2000
05.06.2024 08:48Задача идентификации в этих "прошлых" данных становится сложнее
plumqqz
05.06.2024 08:48Я так понимаю, вы столь юны, что вам паспорт еще не меняли, иначе бы вы увидели там раздел "сведения о ранее выданных паспортах" :-)
Завидую. Честно :-)
А вот у меня там все вплоть до советского.
Но это вторичный вопрос; на самом деле если там хранить изменяемые данные, то невозможно точно ответить на простой вопрос - "кто именно подавал заявления на такую-то дату?"Naf2000
05.06.2024 08:48Спасибо за комплимент, недавно менял в 45 лет паспорт. Я же не сказал невозможно, но становиться "сложнее".
Получается в итоге номер/серия паспорта это ключ только паспорта (капитан очевидность), а вот по паспорту можно получить данные человека. Но мы уже уходим в область философии.
plumqqz
05.06.2024 08:48Так это философия и есть в чистом виде, см. Аристотель, "Категории". Вот прям с первых строк.
OlegUV
05.06.2024 08:48+2Подход в принципе неправильный, не с той стороны.
Автор взял сущность и набрал каких-то её атрибутов, а потом задаётся вопросом - как сделать из них уникальный ключ сущности. Это как тыкнуть пальцем в небо - может получится уникальный ключ, а может и нет.
Действовать нужно ровно наоборот - сначала узнать, что является уникальным ключом сущности, и использовать только его, а потом только добавлять какие-угодно атрибуты.
Batalmv
05.06.2024 08:48+3Мне кажется, что статья чуток не о том
Лично я всегда по дефолту рекомендую (я уже сам не занимаюсь кодингом) использовать синтетический ключ как primary. Причина проста. Цена вопроса копеешная, а ссылочная целостность и адресация объекта/записи на высоком уровне.
Но есть проблема идентификации. Внутри системы все прекрасно, как и везде, пока не пришли пользователи и не начали тыкать в кнопки. А что делать, когда вы с чем-то интегрированы. Тут ваш синтетический ключ уверенно встал и вышел, так как очевидно ваша система не пуп Земли и не MDM для всего сущего. Общение идет по естественным ключам в большинстве случаев, так как общим знанием об объекте являются именно есть естественные параметры.
И поэтому задачу управлять естественными ключами, которые заданы бизнес моделью никто не отменяет. Если в вашу систему прилетел пакет с данными про "Васю", то вам надо искать именно Васю. Если у вас их много, то естестввенные ключи никак не помогают
Ну или в примере
Так как номер кузова — очевидный кандидат на должность естественного ключа, я заранее предвидел, что изменение этого номера будет или невозможно, или приведёт ко всевозможным каскадным эффектам
Ну ок, поменял. Допустим в системе есть запись о машине и свыязанная с ней запись о владельце. Они связаны foreign key который построен опираясь на синтетический ключ. Но прикол в том, что этот ключ живет внутри системы. Когда автора статьи остановит полиция, она теоретически сверит регистрационный номер, номер кузова и ФИО владельца.
И "правильная" структура никак не защищает от
и в конечном итоге к уничтожению официальных записей, больше не признающих, что машина принадлежит мне.
Конечно, для софта естественный ключ решает потенциальную проблему дубликатов VINa, если бы он использовался как primary key. Но если окажется, что есть реальный дубликат, т.е. два авто с одинаковым VIN кодом, то хорошо все буде в базе и только.
В реальном мире проблема останется
В конечном итоге, вне зависимости от вашей уверенности в том. что естественный ключ стабилен и корректно отслеживает сущность, случаются ошибки данных.
Вот тут как по мне ошибка. Синтетический ключ отслеживает ЗАПИСЬ о сущности, которую элементарно можно подменить другой записью. А вот естественный ключ отслеживает именно СУЩНОСТЬ, так как сколько бы записей о "Васе" не было бы в разных системах, он все равно сначала Вася, а потом уже 0c0d49a0-d2e5-4f04-86a4-31c421ed4090
Резюмируя:
для консистентности данных очевидно надо использовать синтетические ключи
для идентификации - естественные
Yumado
05.06.2024 08:48коммент тянет на статью по уникальности записей и поиску в БД. речь шла про РК.
100% лучше синтетический ключ. Есть возможность для оптимизации "запись - чтение"
kinall
05.06.2024 08:48Не по конкретной статье, а вообще по переводам. Во-первых, как, интересно, даже не заметив плашки "перевод" я уже на третьем абзаце понял, что это именно он? Во-вторых, что за манера оставлять ссылки на англоязычную википедию? Например, статья про биекцию есть и на русском.
vladnet1
05.06.2024 08:48Тоже проблема нашли, вообще пользоваться естественными ключами можно только когда они регламентированы каким то органом серьёзным, например для валюты есть международный код.
В других случаях проще пользоваться guid. А то у вас получится что если пользователь ошибся в названии города, то все это уже другой ресторан. Дичь
nApoBo3
05.06.2024 08:48Всегда используйте суррогатные первичные ключи за исключением случаев когда вы абсолютно уверены, что это решении не проходит функциональные или не функциональные требования. Все.
speshuric
05.06.2024 08:48+1Как-то бессмысленно дискутировать с переводом, но таки напишу. Синтетические ключи - неплохой (часто лучший) выбор для сущностей, т.е. строк, в БД обладающих идентичностью. 2 человека - как ни меняй их имена, пол, дату рождения и паспорт всё равно остаются двумя разными людьми. Используйте синтетический ключ.
Если же запись таблицы не является сущностью или её сущность полностью описывается естественным ключом, то синтетический ключ только поломает всё.
Примеры, когда сущность полностью описывается ключом: таблица недействительных паспортов, таблица праздничных дней (часто любой календарь).
Примеры, когда запись таблицы не является сущностью: отношения многие ко многим (там просто составной ключ родительских таблиц какие бы они ни были).Плюс есть гора особых случаев. Не всё сводится к классическим справочникам и OLTP. Есть и time-series и OLAP, и куча того, что вообще отвратительно в таблицы ложится.
Akina
05.06.2024 08:48+1Примеры, когда запись таблицы не является сущностью: отношения многие ко многим
Неоднозначный (не скажу плохой) пример.
Контрпример. Таблица прав X. Таблица объектовY. Связующая таблица XY, хранящая право на объект. Таблица пользователей Z. И связующая таблица XYZ, которая хранит права пользователей на объекты, т.е. связь между XY и Z. В этом случае право на объект, которое хранит связующая таблица XY, есть ни что иное как самая настоящая сущность, которая к тому же может иметь собственные дополнительные атрибуты, например, срок актуальности.
speshuric
05.06.2024 08:48Так я как раз про то, что нет таких максималистских по-детски правил, что "всегда синтетика" или "естественный ключ есть? а если найду?". Более того, догматичность тоже не догма: иногда отход от догм позволяет увидеть крутое решение, иногда приводит к винегрету решений.
Kahelman
05.06.2024 08:48Рекомендация использовать синтетический ключ это вопрос реализации. Логично что проще работать с числом, короткой строкой чем с набором из 100 аттрибутов.
Но это не гарантирует вас от проблем.
Давайте посмотрим на натариуса: вы пришли к нему регистрировать сделку по покупке квартиры.
Он в книгу записал: запись номер 10, квартира расположеная по адресу, ,,, приобретена гражданином Ивановым,….
Если вы через год паспорт/фамилию поменяете или улицу город переименуют - сделка от этого не станет ничтожной.
И натариус не будет вам записи подчищать каждый раз когда вы паспорт/фамилию меняете.
Если с паспортом /фамилией ещё боле менее просто что-то подтвердить, то с изменением названий улиц/городов все гораздо сложнее.
Тем не менее человечество давно эту проблему решило. Полагаясь исключительно на естественные ключи.
И только у программистов почему-то проблемы возникают
Ivan22
05.06.2024 08:48ну гораздо проще отслеживать историю изменения любых атрибутов сущности имея неизменный сурогатный PK , чем не имея его.
p.s. А так-то человечество и берестяными грамотами обходилось конечно
vis_inet
Не совсем понял, как механик определил, что у машины неправильный VIN-номер?
И что значит "неправильный"?
Его же нанесли непосредственно на заводе изготовителе.
PatientZero Автор
В базу данных с ошибкой введён, наверно
Naf2000
Номер в документе отличается от выбитого на кузове
rdp
В мастерской забили в базу VIN с ошибкой. Не думаю, что механик имеет доступ к базам производителя и регистратора.
vis_inet
Отнюдь, у официальных автодилеров + сервисов есть доступ к определённым информационным системам производителей.
MiyuHogosha
Это отдельная база. Скорее всего у производителя все в порядке. Но у производителя нет доступа к данным клиента
gd77
Как вариант VIN в документах и VIN в "мозгах" был разный - когда-то меняли ЭБУ. Механик просто прописал в ЭБУ номер из документов.
vis_inet
В принципе, да, это возможно.
Но для прописывания в мозгах приносят ноут к машине, а не идут к компьютеру.
Возможно, описание ситуации неполное или недостаточно подробное.
kinall
Строго говоря, ноут уже мог быть подключён с самого начала работ. И "подошёл к компьютеру" - это два шага от капота (где выбит VIN) до салона (где на сиденье стоит ноут)
SoundBlasteR
Все пациенты врут. Dr.House
Batalmv
Контрольный разряд? Либо очевидное несовпадение модели
mentin
Это официальный механик, который вносит данные в государственную базу. Соответственно у него есть (1) документы владельца с регистрацией машины, (2) государственная база данных, в которую надо внести сведения о проверке, (3) собственно машина. Что-то разошлось, либо документы либо база разошлись с машиной. Цифре на машине наверное верят, а остальному нет.
vis_inet
Я это всё понимаю.
Меня смутило, что сначала
затем
Т.е. неправильность номера он оценил у машины, без обращения к компьютеру.