Комментарии 120
НЛО прилетело и опубликовало эту надпись здесь
Описанные "Недостатки последовательных ключей" выглядят слабо.
- как будто бы помогает бороться с ошибками в написании джойнов. На самом деле ошибочный запрос с гуидами просто не будет возвращать ничего. Ошибочный запрос с сиквенсами будет возвращать лишнее. И первое не обязательно лучше.
- Современные СУБД как раз придумали уже кучу всего, чтобы система с сиквенсами нормально масштабировалась
- Надумано, так как импорт будет в любом случае в отдельную таблицу. Никто в здравом уме не будет вливать внешние данные в текущую структуру без ETL обработки, даже если там уже гуид как ИД
- предрассудок в чистом виде. Кроме того, например в Оракле, последовательность ИД как раз таки и не гарантируется. Как раз из-за п.2. Никто по ИД не сортирует.
- личные тараканы автора
Надумано, так как импорт будет в любом случае в отдельную таблицу. Никто в здравом уме не будет вливать внешние данные в текущую структуру без ETL обработки, даже если там уже гуид как ИД
Есть у меня на поддержке приложение, хоть и legacy, но активно дорабатываемое под изменения законодательства. У этого приложения есть БД(Informix) с парой 200-гиговых таблиц с ключами типа SERIAL. Знали бы вы, сколько боли причиняет этот SERIAL при любом чихе в сторону реорганизации структуры данных…
Я могу представить что инкрементный ключ делает сложной или даже бесполезной кластеризации. Но не могу представить какие ещё проблемы он несёт. Поделитесь примерами если это не секрет
НЛО прилетело и опубликовало эту надпись здесь
Тут скорее кумулятивная проблема. Главный пререквизит, что максимальное окно недоступности не более часа в неделю, невозможность кардинально переписать софт с этими данными работающий и завязка на текущее значение next. Любое разделение данных (а иначе альтер в окно не укладывается) приводит к шаманским пляскам вокруг счетчика
НЛО прилетело и опубликовало эту надпись здесь
Навскидку кейс: меняем структуру огромной таблицы. alter работает недопустимо долго. Типовое решение: создаём новую структуру как отдельную таблицу, приложение до окончания миграции пишет в обе таблицы, в фоне данные мигрируют, когда догоняют, приложение пишет в новую таблицу, а старая дропается. При такой схеме в идеальном случае минимум один раз нужно будет согласовывать текущие значения счётчиков. А уж если мы не можем по каким-то причинам просто использовать значения из одной таблицы для вставки в другую, используя в режиме двойной записи только одну физическую последовательность, то возникает множество проблем типа в одну таблицу запись прошла, а до другой не дошла. А последовательности часто нетранзакционны,
А как же производительность JOIN на 128-битных uuid, против 64-битных целых?
Первое, что бросилось в глаза, автор несколько путает ключи и ограничения. Например, в CREATE TABLE tax_brackets и CREATE TABLE flight_roster ключей как таковых нет. То, что здесь указано — это ограничения, а не ключи. Ключ служит для идентификации записи, ограничение — для соблюдения бизнес-правил. Иногда они могут совпадать, иногда нет. Это как раз те примеры, когда для идентификации подходящего естественного ключа нет, нужно суррогатный вводить.
По поводу суррогатного ключа uuid vs автоинкремент vs что-то другое, могу сказать своё мнение: если с базой предполагается работа администратора/саппорта напрямую, делайте ключи читабельными. Если не предполагается, смело делайте uuid. Человеческое время поддержки намного ценнее, чем время разработки или несколько процентов плюса в бенчмарке.
По поводу суррогатного ключа uuid vs автоинкремент vs что-то другое, могу сказать своё мнение: если с базой предполагается работа администратора/саппорта напрямую, делайте ключи читабельными. Если не предполагается, смело делайте uuid. Человеческое время поддержки намного ценнее, чем время разработки или несколько процентов плюса в бенчмарке.
В теории реляционных БД есть такая сущность как «естественный ключ». На практике такой ключ должен стать либо первичным ключом, либо ограничением уникальности.
Часто делаю два суррогатных ключа в таблицах: автоинкремент/последовательность для PK и использования в качестве цели FK в пределах базы(приложения) и uuid для интеграций с внешними системами, особенно когда интеграция планируется в режимах мультимастер.
Есть несколько вопросов к автору. Где можно найти определения искуственных но не суррогантых ключей а так же внутренних ключей? Какое отношение имеет ограничение UNIQUE к ключам? (Напомню ограничение UNIQUE не нарушают повторяющиеся значения значения NULL) С моей точки зрения не совсем раскрыта тема что индекс, ключ, суррогатный ключ и ограничение — это вещи практически из разных галактик. Поясню что я имею в виду. Ключ относится к логичесой организации данных. Суррогатный ключ относится к уровню приложения. Наличие суррогатного ключа не делает базу нормализованной хотя формально первичный ключ вроде бы и есть. Индекс так это способ ускорить выборку. Кстати MySQL на каждый внешний ключ создает дополнительный индекс. А вот PostgreSQL — не создает. И многие удивляются что выборка тормозит. Нужно создавать явно увы. А ограничение это и есть ограничение отменит в случае чего добавление обновление удаление записи.
Потребовалось какое-то время, чтобы в реализациях SQL пропало несоответствие ключей и реляционной модели, самые ранние базы данных были заточены под низкоуровневую концепцию первичного ключа. Первичные ключи в таких базах требовались для идентификации физического расположения строки на носителях с последовательным доступом к данным. Вот как это объясняет Джо Селко:
Термин «ключ» означал ключ сортировки файла, который был нужен для выполнения любых операций обработки в последовательной файловой системе. Набор перфокарт считывался в одном и только в одном порядке; невозможно было «вернуться назад». Первые накопители на магнитных лентах имитировали такое же поведение и не позволяли выполнять двунаправленный доступ. Т.е., первоначальный Sybase SQL Server для чтения предыдущей строки требовал «перемотки» таблицы на начало.
Хотелось бы посмотреть на оригинал или периевод цитаты. Я ее не нашел в переводе популярной книги Джо Селко (который катати был противникам суррогатных ключей судя по его пекомендациям в этой книге).
Есть два момента. Действительно с 1963 года начали использовать индексный метод доступа к данным ISAM который предвосхитил современный базы данных. ФИшкой этого метода было как раз то что в индексе содержались физические адреса данных а доступ к индексу можно было органпзовать по человекопонятным цифрам (например код товара, ISBN и т.п.) Это я о первом абзаце (слова автора)
Что касается цитаты Джо Селко (я конечно надеюсь узнать о первоисточнике цитаты) Хранение индексных наборов было сдорогостоящей операцией а до изобретения НЖМД еще и медленной т.к. для поиска лента перематывалаь в разные стороны. Поэтому реляционную модель реализовывали при помощи плоских файлов и их сортировки по ключу. Это позволяло делать расчеты. Приведу пример. Есть два плоских файла остатки товара и прайс. Оба файла сортируются по коду товара и сливаются в один файл. ПАосле этого полученный файл сортируется например по материально ответсвенному лицу и получаются итоги в денежном выражение «в разрезе» как это было принято говорить материально ответсвенных лиц.
Если у объекта отсутствует какой-либо код, то как различить две банки с напитком или две коробки с овсянкой, кроме как по их расположению в пространстве или по небольшим различиям в весе или упаковке?
В физическом мире происходит примерно так. Есть совокупность элементов материи (материальные точки), связанных между собой некими взаимодействиями (векторами). То есть материя организуется в независимые кластеры. При действии внешнего взаимодействия на некоторые точки оно распространяется по векторам в пределах этого кластера. В результате у всех точек меняется поведение. Пример: взять коробку с полки из ряда других.
Кластеры как объекты мы выделяем при наблюдении поведения. У них нет естественных ключей. Можно выделить нечто вроде отношения "равно / не равно", которое показывает, распространится ли взаимодействие между двумя точками или нет, то есть принадлежат ли они одному кластеру или разным. А это основное свойство первичного ключа.
А поскольку кластеры это счетное множество, то можно их пронумеровать. Именно поэтому автоинкрементные ключи удобны в использовании.
Если говорить о теории реляционных баз данных то двух не различимых строк не может быть суррогат здесь не спасёт. Говоря о конкретном примере например продукт питания то давайте проследим его путь от производства до потребителя.производитель присваивает изделию номер партии на которую составляет документы о его качестве. Внутри партии коробки с овсянкой неразличимы и выступают в своём количестве. Далее партия или её часть отгружается промежуточному складу или напрямую магазину. так появляется номер накладной. После этого в магазине вы закупаете товар по чеку. И все ещё по прежнему вы можете посмотреть номер партии производителя. Хотя напимер для вас как для клиента теряется информация о номере накладной.
Суррогат как раз спасет, так как именно он и будет различаться.
Мне кстати не нравится термин «суррогат». Именно искусственная нумерация первична. Именно она позволяет со 100% вероятностью различить 2 объекта.
Как простите суррогат который в базе данных сделает различимыми два объекта которые на полке?
База это модель. Он моделирует их различие.
Вы не сможете различить 2 одинаковых объекта на полке ни по каким внешним признакам самих объектов.
Вы не сможете различить 2 одинаковых объекта на полке ни по каким внешним признакам самих объектов.
Да именно. Модель. То есть если объекты в отношении различаются только суррогатным полем то или их различие несущественно и в данном случае должно быть не две строчки коробка, ещё коробка а одна строка — две коробки. Или же нужно выявить их различие. Как будет удаляться коробка которая из?
Вы не оттуда начинаете. Начинать надо не с базы и отношений, а с моделируемых объектов. Объекты в реальности различаются поведением. 2 разных объекта на полке это уже свершившийся факт, который надо смоделировать.
Тут я с вами согласен. И вот если мы принимаем реляционную модель то нужно её создать по реляционными правилам нормализованной. Тогда получим профит который делает проще операции crud — не более того. Тут ни о каком суррогате речь ещё не идёт. Далее мы имеем orm или просто библиотеки которые привыкли работать с суррогатов и мы его добавляем как технический приём. К логике модели суррогат не имеет никакого отношения
Нет. Реляционная модель это не абстрактная магия «надо делать так», а она и появилась для моделирования этих фактов. Если она не позволяет смоделировать 2 разных объекта с одинаковыми характеристиками, значит она применяется неправильно.
Мы некоторым образом различаем 2 объекта на полке, можем сказать, что вот это один объект, а вот это не тот а другой объект. Числовой ключ — это именно модель нашего восприятия объектов. Вернее, модель это отношение равенства чисел. Можно использовать любые другие ключи с таким свойством, набор разных строк например. У самих объектов таких характеристик нет.
Мы некоторым образом различаем 2 объекта на полке, можем сказать, что вот это один объект, а вот это не тот а другой объект. Числовой ключ — это именно модель нашего восприятия объектов. Вернее, модель это отношение равенства чисел. Можно использовать любые другие ключи с таким свойством, набор разных строк например. У самих объектов таких характеристик нет.
Согласен. В правильно смоделированном отношении (таблице) два кортежа (две строки) всегда различаются (без применения суррогата). Весь вопрос в том чтобы правильно выделить объект с точки зрения решаемой задачи.
Что касается «магии нормализации». То я не утверждел что магия существует. Нормализация сильно упрощает операции вставки-изменения-удаления и выборки данных. И не более того. И сама нормализация не является абстраконой а всегда связана с решением конкретной задачи.
Что касается «магии нормализации». То я не утверждел что магия существует. Нормализация сильно упрощает операции вставки-изменения-удаления и выборки данных. И не более того. И сама нормализация не является абстраконой а всегда связана с решением конкретной задачи.
Но у вас нет возможности сделать нормализацию на основе свойств самих объектов. Потому что в данном примере они все одинаковые.
Про магию я имел в виду вывод «мы используем реляционную модель, значит нам надо искать естественные ключи и не использовать искусственные, потому что она так требует по каким-то там магическим причинам». Это не так, потому что все наоборот. Нам нужно смоделировать 2 объекта и способ их различия, для этого можно использовать нумерацию, в реляционной модели нумерация будет являться первичным ключом.
Про магию я имел в виду вывод «мы используем реляционную модель, значит нам надо искать естественные ключи и не использовать искусственные, потому что она так требует по каким-то там магическим причинам». Это не так, потому что все наоборот. Нам нужно смоделировать 2 объекта и способ их различия, для этого можно использовать нумерацию, в реляционной модели нумерация будет являться первичным ключом.
Свойство объекта если перейти на другой уровень абстракции — не только его внутреннее своство а еще может быть и такое свойство как принадлежность к некоторому виду объектов. Которое будет харектеризоваться количеством таких объектов. Если мы реализуем реляционную модель то мы не должны искать ключи в объекте в котором их не найдешь. Мы должны правильно выделить объект из окружающего мира. Например для серийного изделия это будет партия.
Количество — это не свойство объекта, это свойство его контейнера. А контейнер это другой объект.
Ок, вот у нас 2 коробки. В базе есть строка, у которой свойство quantity = 2. Какой у нее первичный ключ?
2 одинаковые коробки и 2 человека с одинаковым именем и внешностью по принципам моделирования ничем не отличаются. Как вы таких людей будете в систему заносить?
НЛО прилетело и опубликовало эту надпись здесь
Суррогат не касается логики данных. Таблица с суррогатным ключом по прежнему останется таблицей без первичного ключа. Суррогатный ключ это всего лишь технический приём который сильно облегчает работу приложения т.к. избавляет от составных ключей и делает ненужным касквдное обновление при изменении естественного ключа.
У реальных объектов нет первичного ключа. Всегда может быть 2 объекта с одинаковыми характеристиками.
Два обьекта с одинаковыми характеристиками это объекты аоторые описываются характеристикой р количеством! Если это две книги например одинаковые то при поступлении в библиотеку они для различения нумеруются и тогда они в базе данных две строки. А в магазине это номер накладно с количеством, номер чекатс кошичесовом
То есть, по вашему, 2 близнеца с одинаковым именем могут получить только один паспорт на двоих?
Или наоборот, были 2 разных коробки, различались допустим цветом, в модели у них были разные первичные ключи. Потом краска осыпалась. По-вашему, они должны схлопнуться в один объект с количеством? А с первичными и внешними ключами что делать?
Контейнер с количеством это сущность более высокого уровня, чем объект в этом контейнере.
Или наоборот, были 2 разных коробки, различались допустим цветом, в модели у них были разные первичные ключи. Потом краска осыпалась. По-вашему, они должны схлопнуться в один объект с количеством? А с первичными и внешними ключами что делать?
Контейнер с количеством это сущность более высокого уровня, чем объект в этом контейнере.
Пример не корректен. Все рассматривается с точки зрения решаемой задачи. Для какого то класса задач неразличимы люди даже не состоящие в родственных связях
Мы обсуждаемых не объект и сущность а кокрентнг строгую теорию реляционных баз данных.
Мы обсуждаем общее средство моделирования, используем общие термины типа «сущность» и «первичный ключ». Если теория не позволяет смоделировать какие-то ситуации, значит она подмножество более общей теории.
Почему Вы считаете что мв обсуждаем не теорию реляционных баз данных? Статья посвящена именно теории релаяионных баз данных и все мои кмментарии также касались теории реляционнах баз данных. В теории реляционных баз данных нет понятия сущность, объект, модель. Там все строго кортеж, отношение.
Мы обсуждаем моделирование с помощью этих кортежей и отношений. Основным понятием является первичный ключ. Кортеж это аналог понятия сущность.
Какую модель данных применять пр моделированиее это уже другой вопрос. Если задача не укадывается в реляционную модель то она там не применяется. Если применяется то будет профит если придерживаться нормализации данных. Если нормализация нарушается то если это не ошибка проектирования — денормализацию проводят осознанно для решения других задач. (например классический вариант проход расход и баланс баланс является зависимым от прихода и расхода. Тем не менее для выборки когда сумма прихода и расхода вычилсяется по трерабайтам порой хранят или окончательный баланс или промежуточный как это былло в ранних версиях 1с не знаю как сейчас)
Да, только я о том, что ваше понятие о нормализации не совсем верное. Искусственный ключ не нарушает нормализацию.
Моя мысль в том, что у объектов не бывает собственных естественных ключей. Номер договора, номер счета, название из заданного списка — это все заранее придуманные искусственные ключи. Нет никакой проблемы в том, чтобы использовать свой искусственный ключ.
Даже если он у сущности уже придуман, необязательно использовать именно его, достаточно задать соответствие 1:1 с другим ключом. Ограничения сугубо технические, и сводятся к вопросам вида «можем ли мы позволить перестройку unique индекса на вставку, чтобы ускорить чтение по более удобному ключу».
Моя мысль в том, что у объектов не бывает собственных естественных ключей. Номер договора, номер счета, название из заданного списка — это все заранее придуманные искусственные ключи. Нет никакой проблемы в том, чтобы использовать свой искусственный ключ.
Даже если он у сущности уже придуман, необязательно использовать именно его, достаточно задать соответствие 1:1 с другим ключом. Ограничения сугубо технические, и сводятся к вопросам вида «можем ли мы позволить перестройку unique индекса на вставку, чтобы ускорить чтение по более удобному ключу».
Я не говорил что искусственные ключ нарушает нормализацию. Я говорил что сурррогатный ключ не делает базу нормализованной. Это не просто перестановка слов.
Что означает "не делает нормализованной" и почему естественный "делает"?
Там не всё так просто. Есть теория реляционной модели данных и есть реляционные базы данных, основанные на этой модели. Это не одно и то же. Реляционная модель данных оперирует кортежами, отношениями и т.д., и это математическая теория. Реляционная БД оперирует таблицами, записями, и это прикладная инженерная область. И при этом таблица РБД на первый взгляд напоминает отношение в реляционной модели, но на самом деле отношением не является, равно как и строка таблицы не является кортежем. Поэтому надо эти вещи различать. Если мы обсуждаем вопросы по проектированию хранилища для каких-то реальных данных, то надо оперировать понятиями реляционных БД, а не абстрактной реляционной модели.
Реляционную модель данных применяли еще до создания серверов реляционных баз данных и реализовывали на плоских файлах с их сортировкой по ключам. Реляционную модель данных применяют и сейчас в проектах на движках NOSQL баз если например есть преимущества от использования движка базы по его кластеризации и шаридованию но данные по природе реляционные. Движки реляционных баз данных современные все без исключения реализуют удобную работу с реляционной моделью данных. Но можно на них естественно реализовать нереляционную модель. Например все в одной таблице типа key/value.
Вы не поняли. Реляционная модель данных не полностью соответствует тому, что реализовано в реляционных БД, независимо от их архитектуры. Например, у вас может быть в базе данных два человека с одинаковым ФИО и без других атрибутов. Это нормально и допустимо (насколько такая архитектура правильна, вопрос за рамками этого обсуждения). А в реляционной модели данных такое в принципе невозможно. Поэтому если речь идет не о математике, а о БД, следует оперировать объектами, сущностями и т.д., а не отношениями/кортежами. Это разные вещи.
Я все понял. С моей точки зрения не может быть неправильной реляционной модели. Есть реляционная модель и не реляционная модель. В базе данных нет объектов сущностьей. Там таблицы и строки в которые можно записать все что угодно. Объект, сущность — это уже совсем другой уровень абстрации.
С моей точки зрения не может быть неправильной реляционной модели.
Это вещь, не зависящая от вашей точки зрения :) Есть же определения. Отношение в реляционной модели — это подмножество декартового произведения атрибутов входящих в него кортежей. Соответственно, набор атрибутов, который не выражается как подмножество декартового произведения, отношением не является. Поскольку табличные данные реляционной БД этому в общем случае не соответствуют, то это как раз тот парадокс, когда это на самом деле неправильная реляционная модель. Т.е. она построена в общем на реляционных правилах, но не соответствует им целиком.
Реляционные базы данных это как алгебра
Мы же не пишшем в алгебре так
*** + ** = *****
Мы же не пишшем в алгебре так
*** + ** = *****
Не может в общем случае. Если у вас у двух объектов, представляющих сущности предметной области, нет различимых характеристик, то или вы недостаточно углубились в предметную область, например, не учитываете факт того, что объекты имеют разные пространственной временные координаты, либо то, что вы выделили в объект является не объектом, а классом объектов в предметной области, а поэкщемплярный учёт не нужен в ней.
Пространственные/временные координаты не являются ключом. Едет грузовик, на полках стоят коробки, подпрыгивают на кочках. Координаты меняются, даже относительно грузовика, и обратившись по запомненным ранее координатам вы можете получить совсем другую коробку. Допустим, на одной полке едет 3 полных коробки и 3 пустых. Какие им можно задать первичные ключи? А еще бывают смешанные жидкости, поля или силы. И точность измерений.
Реальные объекты могут иметь одинаковые характеристики, потому что состоят из одинаковых частиц материи, которые связаны одинаковыми силами и взаимодействуют одинаковыми квантами энергии.
Вам тот же вопрос. 2 близнеца с одинаковым именем. По какому первичному ключу их различать?
Реальные объекты могут иметь одинаковые характеристики, потому что состоят из одинаковых частиц материи, которые связаны одинаковыми силами и взаимодействуют одинаковыми квантами энергии.
Вам тот же вопрос. 2 близнеца с одинаковым именем. По какому первичному ключу их различать?
Ключом является то, что позволяет однозначно идентифицировать объект. Есть у нас 6 коробок в грузовике — для каждой из них можно э задать как идентифицирующий признак, например, параметры какой-то функции от времени, в первом приближении начальные координаты в грузовике. Или хранить историю изменения координат в виде набора пар время-координаты.
Это не ответ) Какие именно свойства будут первичным ключом и какие будут 6 значений?
Начальные координаты в грузовике.
То есть такие: [(0, 0, 0), (0, 0, 1), (0, 0, 2), (0, 0, 3), ...]
Здравствуй, инкремент.
Ок, я обращаюсь по координатам (0,0,0), ожидаю получить первую полную коробку, но оказывается из-за тряски они поменялись местами и теперь там пустая. Вывод — местоположение это не первичный ключ.
Зато если задать соответствие числа и коробки, все проблемы исчезают. Мы можем отличить 2 коробки независимо от любых характеристик.
Здравствуй, инкремент.
Ок, я обращаюсь по координатам (0,0,0), ожидаю получить первую полную коробку, но оказывается из-за тряски они поменялись местами и теперь там пустая. Вывод — местоположение это не первичный ключ.
Зато если задать соответствие числа и коробки, все проблемы исчезают. Мы можем отличить 2 коробки независимо от любых характеристик.
С близнецами и просто полными тезками, пока не решён вопрос биометрической идентификации, поступать нужно путём введения бизнес-правила, запрещающего давать/брать имена, приводящие к коллизиям в рамках набора идентифицирующих признаков (составного ключа). Это если мы говорим о разработке системы первичного учёта физических лиц а-ля системы ЗАГСа. Кто первый встал — того и тапки. Остальные системы должны уже ссылаться на первичную.
Кстати, насколько я знаю, в СССР и странах, унаследовавших его систему ЗАГС, не дадут зарегистрировать детей с одинаковыми ФИО, датой и местом (населенным пунктом) рождения, поскольку именно эти данные являются идентифицирующими на государственном уровне. С другой стороны, регистрируя близнецов государство (по крайней мере до недавнего времени) доверяло родителям и самим близнецам вопрос их идентификации, не будучи способным проверить их утверждения кто из них кем является.
Кстати, насколько я знаю, в СССР и странах, унаследовавших его систему ЗАГС, не дадут зарегистрировать детей с одинаковыми ФИО, датой и местом (населенным пунктом) рождения, поскольку именно эти данные являются идентифицирующими на государственном уровне. С другой стороны, регистрируя близнецов государство (по крайней мере до недавнего времени) доверяло родителям и самим близнецам вопрос их идентификации, не будучи способным проверить их утверждения кто из них кем является.
Но вы ведь идентифицируете 2 близнецов как 2 разных объекта, наблюдая их одновременно. Значит для различения необязательно использование биометрической идентификации.
Нам ведь не нужны конкретные характеристики. Нужен только способ отличить 2 сущности и обратиться к одной из них. Независимые числовые ключи это самый простой и естественный способ.
Нам ведь не нужны конкретные характеристики. Нужен только способ отличить 2 сущности и обратиться к одной из них. Независимые числовые ключи это самый простой и естественный способ.
Эта идентификация сугубо локальная, основанная обычно на текущих пространственных координатах или каких-то иных временных характеристиках типа одежды или причёски. Я знаю, что передо мной Таня и Галя, но кто из них кто я могу решить только доверившись авторитетным в данной ситуации источникам. Которые могут и обмануть, и сами находиться в заблуждении.
Статью нужно было сократить раз в 5 минимум и не вводить вилами по воде. (Это конечно к автору, а не к переводчику).
Если коротко, то суррогатных ключей следует, по возможности, избегать.
Вот пост разработчика PostgreSQL на эту тему:
www.databasesoup.com/2015/03/primary-keyvil-reprised.html
Вот пост разработчика PostgreSQL на эту тему:
www.databasesoup.com/2015/03/primary-keyvil-reprised.html
А вы не слушайте этого разработчика, все разработчики PostgreSQL поголовно совсем не обязательно должны разбираться и в проектировании БД. Он неправ. Не надо избегать суррогатных ключей. Наоборот, если вы стоите перед выбором, натуральный ключ или суррогатный, у вас должны быть проверки:
1. Натуральный ключ составной — значит, нафиг его.
2. Данные, которые будут в натуральном ключе, имеют хоть малейшую вероятность изменения — значит, нафиг его.
3. Натуральный ключ громоздкий, и негативно влияет на производительность — значит, нафиг его.
4. Суррогатный ключ можно превратить в используемый в бизнес-процессах идентификатор, опять же таки, нафиг натуральный.
Собственно, у вас в реальной жизни будет крайне мало кейсов, где вам будет лучше использовать натуральный ключ.
1. Натуральный ключ составной — значит, нафиг его.
2. Данные, которые будут в натуральном ключе, имеют хоть малейшую вероятность изменения — значит, нафиг его.
3. Натуральный ключ громоздкий, и негативно влияет на производительность — значит, нафиг его.
4. Суррогатный ключ можно превратить в используемый в бизнес-процессах идентификатор, опять же таки, нафиг натуральный.
Собственно, у вас в реальной жизни будет крайне мало кейсов, где вам будет лучше использовать натуральный ключ.
Я почти со всем согласен, но с п.1 не совсем:
не вижу ни одной разумной причины создавать суррогатный ключ для таблицы many-to-many, там самым правильным вариантом мне видится использовать составной естественный ключ, являющийся комбинацией foreign key, которые хранит эта таблица.
Есть, конечно, Django и др. недоразвитые фреймворки, которые заставляют использовать численный id с автоинкрементом в каждой таблице, но у них, как правило, помимо этого столько всяких других недостатков, что я не понимаю как можно их использовать в 2018 году.
не вижу ни одной разумной причины создавать суррогатный ключ для таблицы many-to-many, там самым правильным вариантом мне видится использовать составной естественный ключ, являющийся комбинацией foreign key, которые хранит эта таблица.
Есть, конечно, Django и др. недоразвитые фреймворки, которые заставляют использовать численный id с автоинкрементом в каждой таблице, но у них, как правило, помимо этого столько всяких других недостатков, что я не понимаю как можно их использовать в 2018 году.
Ещё одно соображение: у меня часто бывает, что использование естественных ключей (даже если это VARCHAR(30) или составной ключ) позволяет вообще избежать JOIN'ов, потому что нужная информация уже в ключе.
Здесь есть нюанс. Вполне вероятно, что если вы закидываете в ключевое текстовое поле какую-то информацию, присутствующую в дочерних таблицах, которую потом приходится извлекать из ключа, то вам с точки зрения производительности было бы эффективнее просто денормализовать эти данные.
Приведу пример: мероприятия проходят в разных помещениях, в таблице locations с помещениями я сделал название помещения (varchar(30)) первичным ключом. Мало того, что оно уникально, так это наверное, самая первая и важная информация, которую мы хотим знать о нём.
И теперь на странице, где находится таблица мероприятий я сразу имею название помещения без всяких JOIN'ов, потому что оно и есть foreign key.
Следует ли это как-то денормализовать?
И теперь на странице, где находится таблица мероприятий я сразу имею название помещения без всяких JOIN'ов, потому что оно и есть foreign key.
Следует ли это как-то денормализовать?
Та небольшие проблемы начнутся если у Вас этот ключ будет участвовать например в расписании мерприятия и будет пару миллионов записей Когда нужно будет изменить наименование помещения то все будет каскадно довольно долго обновляться в транзакции (а если к базе праллельно сотни тысяч обращений в это время — вообще все очень тормозит). Поэтому использовать суррогат здесь свмый нормальный вариант. Ну или графориентированные базы данных у которых вы просто задаете связь и никаких суррогатов или JOIN-ов не нужно.
Даже если изменение названия будет, это по определению редкое событие.
К тому же, если речь идёт о малом бизнесе, где количество событий на помещение измеряется единицами в месяц, а большинство бизнесов не переживают рубеж в 2 года, либо количество данных настолько небольшое, что проблемы с производительностью находятся вообще на другой планете, чем большинство предприятий, которые используют базы данных.
К тому же, если речь идёт о малом бизнесе, где количество событий на помещение измеряется единицами в месяц, а большинство бизнесов не переживают рубеж в 2 года, либо количество данных настолько небольшое, что проблемы с производительностью находятся вообще на другой планете, чем большинство предприятий, которые используют базы данных.
В вашем случае, пожалуй, не стоит. Но это частный кейс, хорошо работающий только на небольших объемах данных.
Если у вас мало данных/нагрузки и это не представляет проблемы, то зачем JOIN-ов бояться? С ORM это могут быть не JOIN, а отдельные запросы по id, которые проще и результаты можно кешировать. А если нужна дополнительная информация (этаж, вместимость), то все равно придется джойнить или дублировать все в названии.
Насчёт п.2: хоть малейшую вероятность изменения имеет буквально всё. Меняющиеся постоянно требования и условия — тяжёлая реальность и никакой магии, которая бы нас от этого спасла, нет. Во всяком случае это не суррогатные ключи.
Если мы работаем с базами вручную — пишем схемы и запросы сами, то любое изменение характера данных будет очень больно бить, именно поэтому создают ORM.
А если используется ORM, то менять схему и запросы гораздо легче, потому что они генерируются автоматически. В таком случае буквально все аргументы против естественных ключей (кроме производительности — а её надо сначала измерять прежде чем что-то говорить) отпадают. И если использовать нормальный ORM типа SQLALchemy, то он позволяет и естественные ключи и композитные — всё, что угодно.
Если мы работаем с базами вручную — пишем схемы и запросы сами, то любое изменение характера данных будет очень больно бить, именно поэтому создают ORM.
А если используется ORM, то менять схему и запросы гораздо легче, потому что они генерируются автоматически. В таком случае буквально все аргументы против естественных ключей (кроме производительности — а её надо сначала измерять прежде чем что-то говорить) отпадают. И если использовать нормальный ORM типа SQLALchemy, то он позволяет и естественные ключи и композитные — всё, что угодно.
Так не бывает естественных ключей. Все ключи специально придуманные искусственные.
Вроде разница объяснена в посте — ключи, пришедшие извне системы, для системы являются естественными. Если она генерирует их сама — искусственными.
Ну так это не значит, что своих ключей надо избегать. Если у сущности уже есть удобный искусственный ключ, можно использовать его, если нет, надо придумать самим. Не надо искать уникальный набор атрибутов, это невозможно. На то она и сущность, а не значение. Число 2 одно, а переменных со значением 2 может быть много.
На то она и сущность, что у неё должен быть как набор идентифицирующих её значений, так и набор значений, характеризующих её состояние. Если мы не можем определить идентифицирующие признаки, то, скорее всего, это просто сложное значение, value object например. И отдельный ключ для него создавать надо только в рамках каких-то технических оптимизаций типа нормализации. Или в рамках обхода технических ограничений, не позволяющих, например, эффективно работать с массивами как с одним столбцом.
Нет. В том то и дело. У сущности есть характеристики состояния, и всё. Больше у нее ничего нет. У любых 2 сущностей одного типа может быть одинаковое состояние. Именно поэтому и навешиваются искусственные ключи, такие как номер счета или инвентарный номер.
У них есть индивидуальность. Сущности с идентичным состоянием — это всё равно разные сущности. Тут есть небольшая нестыковка с традиционным ООП при применении его для моделирования сущностей — и состояние сущности, и её индивидуальность обычно выражаются через состояние объекта, через его свойства. Но, грубо говоря, свойства, которые задаются в конструкторе, не меняются во время жизни и не влияют, обычно, на поведение и в совокупности реализуют индивидуальность, не являются состоянием сущности, хотя являются состоянием объекта.
Они разные, я о том и говорю. В оперативной памяти они различаются адресом — сторонней независимой характеристикой. В более абстрактном хранилище нужен более абстрактный адрес, для чего хорошо подходит числовой id.
В оперативной памяти объекты, а не сущности. Два объекта с идентичным состоянием представляют одну сущность в одном состоянии. Два объекта с идентичными идентифицирующими свойствами, но разными другими, представляют собой одну сущность в разных состояниях и наоборот, разные сущности в одном состоянии.
Не-не, я о той ситуации, если бы не надо было в базу сохранять, если бы оперативная память не сбрасывала данные при выключении. Мы бы просто создавали объекты, соответствующие коробкам, и устанавливали одинаковое состояние, соответствующее свойствам этих коробок. Без всяких первичных ключей. Все действия и связи были бы по ссылке на объект. Вот id это то же самое, только уровнем выше.
Пример реально естественного ключа — количество протонов и нейтронов в ядре химического элемента. Формула молекулы вещества. Эти значения объективны, существуют независимо от человека.
Неа) Это ключ для типа вещества. А молекул с конкретно таким количеством может быть много. Кроме того, он сам по себе числовой и инкрементый.
Не понял, что значит «тип вещества».
Нет реального объекта «водород» с одним протоном и электроном. Есть много реальных атомов с одном протоном и электроном, проявляющих одинаковые свойства. 2 атома вы не можете различить по этому ключу. А еще атомы могут ионизироваться.
Собирательные типы да, могут основываться на естественных свойствах. Их значения и являются критерием объединения. Такие типы существуют каждый в единственном экземпляре и образуют перечисление. Для химических элементов значение свойства соответствует номеру в перечислении.
Собирательные типы да, могут основываться на естественных свойствах. Их значения и являются критерием объединения. Такие типы существуют каждый в единственном экземпляре и образуют перечисление. Для химических элементов значение свойства соответствует номеру в перечислении.
Про изотопы вы, наверное, слышали?
Насчёт п.2: хоть малейшую вероятность изменения имеет буквально всё
Абсолютно верно. Вот поэтому суррогатный ключ чаще всего предпочтительней. Ключ «Код клиента КЛН-123456» плюс констрейнт «Номер паспорта — уникальный» всяко лучше, чем ключ «Номер паспорта АА 654321»
Я думаю практически все согласны с тем, что для таблицы людей нужно вводить суррогатные ключи.
А вот дальше… например, я делаю возможность некоторым людям выступать не только в роли записей в таблице, но и логиниться на сайте и что-то делать. Создал таблицу users, которая имеет поле person_id, ссылающееся на суррогатный первичный ключ таблицы людей. Но этот же ключ используется как первичный и для этой таблицы — зачем создавать ещё один?
А вот дальше… например, я делаю возможность некоторым людям выступать не только в роли записей в таблице, но и логиниться на сайте и что-то делать. Создал таблицу users, которая имеет поле person_id, ссылающееся на суррогатный первичный ключ таблицы людей. Но этот же ключ используется как первичный и для этой таблицы — зачем создавать ещё один?
А потом у вас появятся коллективные пользователи, а также «боты».
Вот табличка users — это как раз тот редкий случай, когда натуральный ключ вполне уместен в качестве первичного. Я бы там сделал первичным ключом логин. Очень удобно в различных полях, связанных с настройками или журналированием действий пользователей, видеть их логины, а не person_id
Заодно и решается проблема, какой person_id завести для пользователя, который являет собой бездушного робота, выполняющего какие-то регламентные задачи или синхронизацию с чем-то в вашей базе.
Заодно и решается проблема, какой person_id завести для пользователя, который являет собой бездушного робота, выполняющего какие-то регламентные задачи или синхронизацию с чем-то в вашей базе.
1. А как ссылочную целостность контролировать и обеспечивать?
Не понял сути вопроса, если честно. Так же, как и обычно, какая разница, составной ключ или нет?
Есть, например, составной естественный ключ химического элемента из количества протонов и нейтронов. При попытках указания элемента где-то снаружи таблицы можно сразу определить есть такая запись или нет, имея гарантию, что сочетание протонов и нейтронов уникально. Если первичным ключом делаете какой-нибудь автоинкремент или uuid, то для обеспечения ссылочной целостности вам нужно будет как-то контролировать и обеспечивать, что записи в других таблицах вида "2 протона 3 нейтрона" ссылаются на существующую запись в таблице элементов.
А нахрена в других таблицах писать «2 протона 3 нейтрона» если для этой цели первичный ключ есть?
Потому что это естественный первичный ключ, а не введенный с какими-то целями суррогатный.
Нет, после ввода суррогатного ключа естественный ключ более не является первичным. В частности, это означает, что для ссылок на эту таблицу следует использовать именно суррогатный ключ.
Усложнение архитектуры получается и нарушение инкапсуляции. Клиенту мало знаний об естественном идентификаторе какой-то бизнес сущности, ему ещё нужно получить её суррогатный идентификатор, в бизнес-процессах никого не интересующий.
что записи в других таблицах вида «2 протона 3 нейтрона» ссылаются на существующую запись в таблице элементов.
В общем случае таких записей там быть не должно, это уже денормализация данных без оснований. Если же основания есть, то это будет составной внешний ключ, а в основной таблице там будет констрейнт на уникальность.
В конкретно вашем примере лучшим первичным ключом напрашивается как раз наименование элемента по таблице Менделеева, в нотации, как это принято, с атомной массой для изотопов.
На сколько я помню школу, то номер ячейки в таблице Менделеева однозначно определяет количество протонов в ядре, а количество нейтронов плавающее. Откуда тут денормализация?
На сколько я помню школу, то номер ячейки в таблице Менделеева однозначно определяет количество протонов в ядре, а количество нейтронов плавающее
Нотация этот вопрос уже давно решила, H — водород, а 2H — дейтерий. Для первичного ключа, который содержит всю необходимую информацию, этого достаточно.
Откуда тут денормализация?
По факту, вы группу свойств описываемого объекта из предметной области предлагаете использовать для его идентификации, и дублировать в разных наборах данных. Это чистой воды денормализация.
Чем это отличается от 1-0 и 1-1? По сути лишь разные формы нотаций одного и того же естественного ключа. При том, что "моя" является более простым преобразованием, не требует таблиц преобразования.
Это не я предложил, а Менделеев, насколько я понимаю, идентифицировав даже не обнаруженные на то время элементы.
Чем это отличается от 1-0 и 1-1
Примерно тем же, чем IVANOFF отличается от {D12BEB59-6259-4FA1-A733-ADCD523D72DC}. Первое блюдо готово к употреблению, второе лишь полуфабрикат. Для использования нужно лезть в таблицу, искать и выводить «человеческое описание».
Зависит от задач. Я так же могу сказать, что чтобы узнать количество протонов и нейтронов по нотации типа 2H, мне нужно лезть в таблицу. По сути такие нотации и есть суррогатные ключи поверх естественных, в буквальном смысле слова — данных природой. Причём изначальная нотация, чисто буквенная, оказалась недостаточной для практических задач, когда выяснилось, что количество нейтронов в ядре, масса ядра, не выводится однозначно из количества протонов.
Логика статьи понятна. Помогите разобраться с терминологией. Всю жизнь думал суррогатный ключ — искусственно созданный ключ для упрощения доступа (напимер serial или uuid вместо составного естественного ключа).
И тут, внезапно, натыкаюсь на объяснение Тома Кайта, почему в Оракле нет on update:
«Primary keys are supposed to be imutable, never changing, constant. It is an excessively bad practice to have to update them ever. If there is a 0.00001% chance you will have to update a primary key — then it is not a primary key, its a surrogate key and you need to find the true primary key (even if you have to make it up via a sequence) » ( asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5773459616034 )
Здесь, просто «surrogate key» не «суррогатный ключ», что-то другое?
И тут, внезапно, натыкаюсь на объяснение Тома Кайта, почему в Оракле нет on update:
«Primary keys are supposed to be imutable, never changing, constant. It is an excessively bad practice to have to update them ever. If there is a 0.00001% chance you will have to update a primary key — then it is not a primary key, its a surrogate key and you need to find the true primary key (even if you have to make it up via a sequence) » ( asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5773459616034 )
Здесь, просто «surrogate key» не «суррогатный ключ», что-то другое?
Есть два, как минимум, контекста, когда речь заходит о первичных, альтернативных, суррогатных и прочих ключах: контекст модели данных приложения и контекст модели хранения. В обоих контекстах понятия разных ключей схожи, но в первом ключи идентифицируют сущности, а во втором — записи.
С точки зрения базы, идентификатор записи не должен изменяться никогда, а если бизнес-процессы допускают изменение идентификатора сущности (например опечатка при вводе ИНН или возможность смены логина пользователем), то этот идентификатор не может служить идентификатором записи, для базы он искусственно созданный для упрощения кому-то (не базе) ключ.
С точки зрения же приложения, бизнес-модели при наличии естественного идентификатора, который в некоторых случаях может меняться, суррогатным будет ключ который в базе никогда меняться не будет, о связи которого с первичным ключом приложения приложению нужно заботиться отдельно.
Перефразирую вопрос.
У Тома фраза звучит как: сурогратный ключй — не настоящий ключ, а ерунда на постном масле. В этом же предложении есть про первичный ключ на основе последовательности, как один из вариантов определения «настоящего» первичного ключа. А в терминах хабровской статьи первичный ключ на основе последовательности — это суррогатный ключ.
Возможно, просто, наложились термин «суррогатный ключ» и какой-то альтернативный перевод слова «surrogate».
У Тома фраза звучит как: сурогратный ключй — не настоящий ключ, а ерунда на постном масле. В этом же предложении есть про первичный ключ на основе последовательности, как один из вариантов определения «настоящего» первичного ключа. А в терминах хабровской статьи первичный ключ на основе последовательности — это суррогатный ключ.
Возможно, просто, наложились термин «суррогатный ключ» и какой-то альтернативный перевод слова «surrogate».
Там немного в другом смысле употребляется. Общепринятое мнение таково, что суррогатный ключ — это ключ, не являющийся свойством сущности в предметной области и выдуманный при проектировании БД, и что это плохо. Но оно неправильное. При этом путаются 2 ситуации — когда уже в самой предметной области у сущности есть суррогатный ключ, и когда его нет. В первом случае он почему-то считается обычным свойством реального объекта. Поэтому во втором случае возникают проблемы, когда пытаются применить тот же подход и найти уникальное сочетание свойств. On update одна из них. Если в предметной области нет ключа, его надо придумать самим, а не выискивать заведомо ненастоящий ключ. Например пронумеровать сущности. Формулировка в цитате выглядит кривовато, но в целом верно.
Долго не мог вкурить про уникальность сочетания рубашки и лица, пока не посмотрел оригинал. Suit - это не "рубашка", а МАСТЬ! "Рубашка" - это оборотная сторона карты! Ну как так можно переводить?!...
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
SQL ключи во всех подробностях