Ключи и связи
Тема, где легко показать зрелость: вопрос про естественный ключ почти всегда превращается в разговор о том, что случится с моделью через пять лет. А это как раз тот горизонт, на котором работают аналитики и не работают срочные задачи.
Стержень: первичный ключ обязан быть неизменным, внешний задаёт поведение при удалении, а слово «удалить» в терминах бизнеса очень редко означает физическое стирание.
// Формулировки: «что взять первичным ключом?», «что делать с заказами при удалении клиента?», «как связать данные из трёх систем?»
Естественный ключ против суррогатного
Естественный ключ несёт смысл сам по себе: номер паспорта, идентификационный номер налогоплательщика, артикул товара. Суррогатный придуман системой, смысла не несёт и нужен только для связей.
Проблема естественных ключей в том, что жизнь меняется, а ключ обязан быть вечным. Паспорт заменяют при смене фамилии, при утере, по возрасту. Артикул переназначают при смене поставщика. И вот что происходит в этот момент: на первичный ключ ссылаются, скажем, двенадцать таблиц - заказы, платежи, обращения, доставки. Смена ключа означает согласованное обновление всех двенадцати, желательно атомарно, желательно под нагрузкой.
Правильная схема разводит две роли, которые в естественном ключе слиплись. Суррогатный ключ отвечает за связи внутри системы и не меняется никогда. Человекочитаемый номер живёт отдельным уникальным атрибутом - для людей, для поиска в поддержке, для печати на документе.
// Выгода видна сразу, как только бизнес решит поменять формат нумерации договоров. При разведённых ролях это правка одного поля. При естественном ключе - переделка половины схемы.
Внешний ключ - это вопрос к бизнесу
Внешний ключ делает две вещи. Не даёт сослаться на несуществующую запись - и это техническая часть. И задаёт поведение при удалении родителя: запретить удаление, обнулить ссылку или удалить каскадом всё связанное. А вот это уже не техническая часть, а прямой вопрос к бизнесу.
Формулируется он так: что должно случиться с заказами, если клиента удаляют? Каскад уместен для настоящих частей целого - позиции заказа вне заказа бессмысленны, «две штуки по 500 рублей» само по себе не значит ничего. Для платежей каскад катастрофичен: бухгалтерии они нужны и после ухода клиента, а часть документов по закону хранят годами.
Под словом «удалить» бизнес почти всегда понимает «убрать из работы», а не «стереть из истории». Клиент должен пропасть из списков, поиска и подсказок - и всё, никто не просил уничтожать его платежи.
// Отсюда мягкое удаление: пометка неактивности вместо физического стирания. Связи остаются целыми, отчётность продолжает сходиться, а из рабочих экранов запись исчезает. Стоит это одного логического поля.
Данные из нескольких источников
Когда данные о клиентах приходят из трёх систем, у каждой своя нумерация, и соблазн привязаться к номерам одной из них велик - она кажется главной. Соблазну лучше не поддаваться: главную систему однажды заменят, или в ней случится перенумерация, и вся консолидированная модель поедет следом.
Рабочая схема состоит из двух частей. Собственный идентификатор в консолидированной модели, не связанный ни с одним источником. И таблица соответствий: наш ключ, система-источник, идентификатор в ней. Три колонки, которые решают всю задачу.
Выгода проявляется при первом же изменении ландшафта. Подключить четвёртый источник - просто добавить строки в таблицу соответствий. Пережить перенумерацию у партнёра - обновить его столбец, не трогая остальное.
// Заодно эта таблица становится естественным местом для разбора конфликтов сопоставления: две записи из разных систем оказались одним человеком, или наоборот - одна запись при разборе распалась на двух однофамильцев.
Как отвечать: «Бизнес просит удалять клиента, но платежи должны остаться в отчётности»
Предложил бы мягкое удаление: пометку неактивности вместо физического стирания. Под словом «удалить» здесь имеется в виду «убрать из работы» - клиент пропадает из списков, поиска и подсказок, но связи и финансовая история остаются целыми. Каскадное удаление уничтожило бы платежи, а это разрушает отчётность и, скорее всего, нарушает требования к сроку хранения документов. Отдельно проговорил бы вопрос персональных данных, потому что его обычно смешивают с этим: если человек требует удалить свои данные, это не то же самое, что убрать клиента из работы. Там нужно обезличивание, при котором сам факт операции и её сумма сохраняются, а личность перестаёт быть определимой.
Кандидат разводит три разных смысла слова «удалить» и сам поднимает вопрос персональных данных - тот самый, из-за которого такие задачи и попадают на стол аналитику.
На чём валятся
- −− Берут номер паспорта первичным ключом и получают каскадное обновление при его смене.
- −− Ставят каскадное удаление на связи, где история обязана сохраниться.
- −− Понимают «удалить» буквально, вместо того чтобы выяснить, что именно имеется в виду.
- −− Привязывают консолидированную модель к нумерации одного из источников.
- −− Путают удаление клиента с удалением его персональных данных.
Проверьте себя
Пять вопросов из банка по этой подтеме. Всего их 13, остальные разбираются в тренажёре.
- Почему номер паспорта — плохой первичный ключ для гражданина?A)Он слишком длинный для индексаB)Его запрещено хранить в открытом видеC)Он повторяется у разных людейD)Он меняется при замене документа
показать ответ и разбор
+D)Он меняется при замене документа// разбор: Первичный ключ должен быть неизменным: на него ссылаются десятки таблиц. Паспорт меняют при достижении возраста, смене фамилии, утере — и каждая замена превращается в каскадное обновление половины базы. Правильно завести суррогатный ключ, а номер документа хранить обычным атрибутом с историей.
- Бизнес требует, чтобы номер договора был вида «Д-2026-00147». Как это сочетать с суррогатным ключом?A)Сделать этот номер первичным ключомB)Хранить номер отдельным уникальным атрибутомC)Собирать номер на лету при выводе на экранD)Отказать бизнесу в такой нумерации
показать ответ и разбор
+B)Хранить номер отдельным уникальным атрибутом// разбор: Это классическое разделение ролей: внутренний ключ для связей и внешний человекочитаемый номер для людей и документов. Номер получает ограничение уникальности и правило формирования, но на него ничто не ссылается. Тогда смена формата нумерации не задевает структуру связей.
- Что означает каскадное удаление по внешнему ключу?A)Удаление откладывается до конца дняB)Удаление запрещается при наличии ссылокC)Удаление родителя стирает связанные записиD)Удаление помечает записи как неактивные
показать ответ и разбор
+C)Удаление родителя стирает связанные записи// разбор: Каскад автоматически удаляет всё, что ссылалось на родителя. Приём уместен для настоящих частей целого — позиций заказа, которые вне заказа бессмысленны. Опасен там, где связь слабее: удаление клиента не должно стирать его платежи, потому что бухгалтерии они нужны и после ухода клиента.
- Бизнес просит «удалять клиента», но платежи должны остаться в отчётности. Что предложить?A)Каскадное удаление всех связанных данныхB)Запрет удаления клиентов вообщеC)Перенос платежей на технического клиентаD)Мягкое удаление: пометка вместо стирания
показать ответ и разбор
+D)Мягкое удаление: пометка вместо стирания// разбор: Под словом «удалить» бизнес обычно понимает «убрать из работы», а не «стереть из истории». Пометка неактивности сохраняет связи и отчётность, а из списков и поиска запись пропадает. Отдельно решается вопрос обезличивания персональных данных, если требуется исполнить право на забвение.
- Данные приходят из трёх систем, у каждой своя нумерация. Как связать их в общей модели?A)Использовать номера первой системы как основныеB)Свой ключ плюс таблица соответствий источникамC)Складывать номера всех систем в одно полеD)Перенумеровать записи во всех системах-источниках
показать ответ и разбор
+B)Свой ключ плюс таблица соответствий источникам// разбор: У консолидированной модели должен быть собственный идентификатор, не зависящий от источников, плюс таблица соответствий: наш ключ, система-источник, её идентификатор. Это даёт возможность подключить четвёртый источник, пережить перенумерацию у партнёра и разобрать конфликты сопоставления.
дальше
Теорию прочитали. Навык ставится повторением
В Сеньорчике эта подтема идёт в ежедневных сессиях: движок возвращает её, пока ответы не станут уверенными, и ведёт прогресс отдельно по каждой подтеме. Теория внутри тоже бесплатна, лимит только на количество вопросов в день.