company_banner

Скромное руководство по схемам баз данных

Автор оригинала: Mike Alche
  • Перевод

Geometry of Flowers by Mookiezoolook

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

Но как оценить, какая схема лучше? И что вообще значит «лучше», когда мы говорим об архитектуре БД? Команда Mail.ru Cloud Solutions предлагает познакомиться с рекомендациями Майка Алча, консультанта по разработке программного обеспечения. Нам кажется, что он довольно лаконично резюмировал некоторые принципы грамотной архитектуры.


Директор: «Думаю, мы должны построить базу данных SQL».

Разработчик (он вообще понимает, о чем говорит, или просто увидел какую-то рекламу в бизнес-журнале?..): «Какого цвета хотите базу данных?».

Директор: «Пожалуй, у сиреневого больше всего памяти».

Несколько базовых советов


Итак, важно стремиться к двум основным вещам:

  1. При разбиении информации по таблицам сохраняется вся информация.
  2. Избыточность хранения минимальна.

Что касается второго момента: хотим ли мы уменьшить избыточность только из-за проблемы с размером хранилища? Нет, мы делаем это главным образом потому, что наличие избыточных данных приводит к проблемам несогласованности, если во время обновления вы не обновляете все поля, представляющие одну и ту же информацию.

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

  1. Используйте как минимум третью нормальную форму (в которой каждый неключевой атрибут «должен предоставлять информацию о ключе, полном ключе и ни о чем, кроме ключа», согласно формулировке Билла Кента).
  2. Создайте последнюю линию обороны в виде ограничений.
  3. Никогда не храните в одном поле целые адреса.
  4. Никогда не храните в одном поле имя и фамилию.
  5. Установите соглашения для имен таблиц и полей и придерживайтесь их.


— Над чем работаешь?

Оптимизирую этот SQL-запрос. Он тормозит, и пользователи начинают жаловаться.

А нецензурная лексика в комментариях обязательна для оптимизации?

Если бы ты видел оригинальный код, то не спрашивал бы.

Рассмотрим эти рекомендации подробнее.

1. Используйте как минимум третью нормальную форму


Архитектуру баз данных можно разделить на следующие категории:

  • Первая нормальная форма.
  • Вторая нормальная форма.
  • Третья нормальная форма.
  • Нормальная форма Бойса-Кодда.

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

Первая нормальная форма


Для первой нормальной формы каждое значение каждого столбца каждой таблицы в БД должно быть атомарным. Что значит атомарным? Если вкратце, атомарное значение представляет собой «единичную вещь».

Например, у нас есть такая таблица:
first_name last_name age areas
Jhon Doe 27 {“Дизайн веб-сайтов”, “Исследование клиентуры”}
Mary Jane 33 {“Долговременное стратегическое планирование”, “Найм персонала”}
Tom Smith 35 {“Маркетинг”}

Здесь столбец areas («Области») содержит значения, которые не являются атомарными. Например, в строке Джона Доу поле хранит две сущности: «Дизайн веб-сайтов» и «Исследование клиентуры».

Таким образом, эта таблица не находится в первой нормальной форме.

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

Вторая нормальная форма


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

Что это значит?

Допустим, у вас такая архитектура базы (я подчеркнул поля, соответствующие первичному ключу в этой таблице):
employee_id project_id Hours employee_name project_name
1 1 10 Джон “дизайн веб-сайта”
2 1 20 Мэри “дизайн веб-сайта”

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

Аналогично, имя проекта однозначно определяется идентификатором project_id.

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

Каждого из этих примеров было бы достаточно, чтобы выбросить эту таблицу из второй нормальной формы.

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

Третья нормальная форма


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

Что это значит?

Допустим, у вас следующая архитектура (которая далека от идеала):
employee_name employee_id age department_number department_name
Джон 1 27 123 “Маркетинг”
Мэри 2 33 456 “Оперативный”
Том 3 35 123 “Маркетинг”

В этой таблице department_number можно вывести из employee_id, а department_name можно вывести из department_number. Таким образом, department_name транзитивно зависит от employee_id!

Если существует такая транзитивная зависимость: employee_id → department_number → department_name, то данная таблица не находится в третьей нормальной форме.

Какие проблемы возникают из-за этого?

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

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

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

Всех этих проблем можно полностью избежать в третьей нормальной форме.


Мамины эксплойты. Ее дочь зовут Помогите! Меня заставляют подделывать паспорта

2. Создайте последнюю линию обороны в виде ограничений


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

Ограничения устанавливают правила, какие значения можно вносить в поля БД.

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

Обязательно укажите, что должно произойти при удалении и обновлении строки, связанной с другими строками в других таблицах (правила ON DELETE и ON UPDATE).

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

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

Интересный факт: в апреле 2020 года именно такое ограничение в программном обеспечении помешало торгам на московской бирже ММВБ, потому что цена на нефтяные фьючерсы WTI опустилась ниже нуля. В отличие от московской биржи, Нью-Йоркская товарная биржа NYMEX обновила софт за неделю до инцидента, поэтому сумела успешно провести сделки по отрицательной цене, то есть с доплатой покупателю от продавца — прим. пер.

Обо всех ограничениях PostgreSQL можно почитать здесь.

3. Никогда не храните в одном поле целые адреса


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

Но что делать, если нужно объединить покупки клиентов по городам, чтобы посмотреть, в каком городе какой продукт более популярен? Вы сможете это сделать?

Это будет очень тяжело!

Поскольку полный адрес хранится как строка в поле БД, в первую очередь придется разбираться, какая часть этой строки является городом! И это почти невыполнимая задача, учитывая все возможные форматы адресов в этом поле.

Поэтому обязательно разбивайте универсальное поле «Адрес» на конкретные поля: улица, номер дома, город, область, почтовый индекс и так далее.

Еще одна проблема адресов — «анонимные» поля


Вот иллюстрация из книги Майклза Блаха «Медная пуля для улучшения качества программного обеспечения»:


Какие тут видны возможные проблемы? Сможете ли вы легко отличить город Чикаго от улицы Чикаго? Наверное, нет.

Поэтому не забывайте всегда давать четкие имена столбцов каждой единице информации.


Как составлять резюме

У тебя есть опыт в SQL?

Нет (No).

Так и пиши: эксперт по NoSQL.

4. Никогда не храните в одном поле имя и фамилию


Аналогично ситуации с адресами: количество вариаций имени и фамилии слишком велико, чтобы их четко различать.

Конечно, можно отделить имя от фамилии, если между ними пробел.

Например, «Майк Альче» → имя «Майк» и фамилия «Альче».

Но что делать, если пользователь ввел второе имя? Или у него двойная фамилия? А что, если есть и второе имя, и двойная фамилия?

Как определить, где имя, а где фамилия, чтобы разделить строку? Ошибки неизбежны.

Способ избежать многих проблем — создать отдельные поля (в формах) для имен пользователей first_name и last_name. Таким образом, вы позволите пользователям разделить свои собственные имена и сможете хранить данные согласованным образом.

Примечание: я не говорю, что в полях БД запрещены пробелы. Например, для таких имен, как «Хуан Мартин Дель Потро», первая часть «Хуан Мартин» входит в поле first_name, а «Дель Потро» — в поле last_name. Конечно, это не идеально. Можно дополнительно завести столбцы middle_name и second_last_name. Посмотрите подробнее о возможных вариациях имен и фамилий в списке «Заблуждения программистов об именах» и статье «Заблуждения программистов об именах — с примерами». Придется согласиться на какой-то компромисс между точностью и практичностью.

5. Установите соглашения для имен таблиц и полей и придерживайтесь их


Довольно неприятно работать с данными, которые выглядят как user.firstName, user.lst_name, user.birthDate и так далее.

Я бы посоветовал установить правила именования с подчеркиванием, потому что не все SQL-движки одинаково обрабатывают заглавные буквы, а заключать всё в кавычки весьма утомительно.

Выберите так же, как называть таблицы — во множественном или единственном числе (например, users во множественном числе или user в единственном). Мне больше нравится единственное число, но все фреймворки для бэкенда, кажется, по умолчанию настроены на множественное. Приходится следовать шаблону и использовать множественное число.

Что еще почитать:

  1. Какую базу данных выбрать для проекта, чтобы не пришлось выбирать снова.
  2. Базы данных в IIoT-платформе: как Mail.ru Cloud Solutions работают с петабайтами данных от множества устройств.
  3. Наш канал в Телеграме о цифровой трансформации.
Mail.ru Group
Строим Интернет

Похожие публикации

Комментарии 11

    0

    полезные конечно короткие примеры(но далеко не все), но есть замечания (я видел что это перевод):


    "Архитектуру баз данных можно разделить на следующие категории:"? (посмотрел оригинал "The design of databases can be separated into these categories:", суть в том что база данных соответствует одной из нормальных форм(это не перевод), кстати список нормальных форм не полный)


    по поводу коде стайла для именования https://www.sqlstyle.guide/ru/ хороший пример с объяснениями.

      +8
      Используйте как минимум третью нормальную форму

      А потом мы просыпаемся, грустим и идём писать код, который должен работать с реальными данными на реальных серверах.
        +4
        Именно. Третья форма — это инструмент. А такие советы исходят из того, что это цель — что конечно же не так. Если у нас нет проблем несогласованности, зато есть проблемы производительности — то денормализация это наше фсё.
        +1
        Какой-то набор банальностей из нулевых или даже девяностых. В мире победившего JSON нельзя хранить адрес в одном поле? Areas точно не может быть «единичной вещью»? Массив из значений ENUM в 2020 запрещён? Констрайнты, серьёзно? А как их потом обрабатывать в слое бизнес-логики? INSERT INTO… — облом. А что делать с этим обломом? У нас упала база, у нас кончились UUID-ы, или просто новый пользователь ввёл возраст меньше 13 лет?
        Про нормализацию и денормализацию выше уже сказали.
        Прошёл по ссылке — удивился потоку елея. Что с интернетом, с нами происходит?! Надо писать (и переводить) отдельные статьи про нормальные формы? В 2020 году, серьёзно?!
          +4
          В реальном мире советы: «храните адрес в одном поле» и «не храните адрес в одном поле» одинаково, кстати, бесполезны. В идеале должны быть оба варианта, потому что охватить все 100% кейсов с адресами всё равно невозможно и обязательно нужно поле, чтобы можно было оставить адрес типа: «третья улица в нонейм посёлке у реки Грязька, северный угол третьего красного дома». А вообще, да, статья во многом оторвана от реальности, те же констрейты городить нет смысла, как и бизнес-логику в хранимки запихивать в большинстве случаев.
            +5
            Констрайнты, серьёзно? А как их потом обрабатывать в слое бизнес-логики? INSERT INTO… — облом.

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


            А что делать с этим обломом? У нас упала база, у нас кончились UUID-ы, или просто новый пользователь ввёл возраст меньше 13 лет?

            Проверить ошибку, возвращаемую СУБД?


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

            +2
            Хотелось бы заметить, что почти все, что описано в статье, скорее применимо к OLAP, а в случае работы с хранилищами DWH, то скорость получения агрегатов и сложных выборок предпочтительна перед малым размером базы, так что денормализация часто применяется, особенно когда это выгрузки из OLAP систем, где данные уже были валидированы.
              +3
              Статья по нормализации без примеров исправления ненормализованных примеров, серьёзно? Если уж рассказывать о нормализации, то должен быть пример «до», описание проблем, пример «после».
                0

                Меня другое мучает. Например у заказа есть варианты доставки: самовывоз, доставка до клиента своими силами, доставка транспортной компанией (ограничим для простоты тремя).


                1. В интерфейсе мы должны сделать недоступным/невидимым поле адрес, если самовывоз. Аналогично с полем Транспортная компания — если она не используется. Тоже ограничение мы прописываем при проверке логики: на сервере приложения или в триггере БД. Не кажется это дублированием кода? Но не проверять нельзя — возможно данные будут получены из сторонних источников.
                2. Что делать в логике когда заполнены поля, когда они должны быть пустыми? Например заполнен адрес и/или транспортная компания при варианте самовывоз? Бросать исключения, откатывать транзакцию, проглатывать как есть или обнулять поле?
                3. Если ничего не делаем, то это переходит в ответственность следующего звена. Например отчёт по графику отгрузки/сборки. Где нужно ничего не выводить в полях адреса и перевозчика.

                Есть четкие методики или как всегда нет правильного и неправильного решения?

                  0

                  Смешались в кучу кони, люди…


                  1. Проблемы интерфейса — это проблемы только и исключительно интерфейса, и касаются они исключительно юзабилити, а не корректности. Конкретнее: интерфейс может скрывать или не скрывать "лишние" поля визуально, позволять или не позволять юзеру вводить изначально некорректные данные (дублируя тем самым проверки бэка, вполне возможно некорректно) — всё это не принципиально до тех пор, пока он в состоянии показать юзеру полученную от бэка ошибку при отправке данных. Иными словами источник истины о корректности данных — всегда только бэк и возвращаемые им ошибки.


                  2. Насколько я понял, вопрос про логику бэка. Если некорректные данные (включая "лишние поля") получены от юзера — вернуть юзеру ошибку, в которой ясно описана проблема. Если некорректные данные получены из БД — тут сложнее, и вариантов корректного поведения больше одного, потому что многое зависит от доступных бэку инструментов уведомления разработчиков о проблемах (отправка писем, метрики, логирование, …), доступных инструментов для откладывания обработки (пометка проблемных записей в БД, откладывание события по обработке текущей записи, dead letter queue, …), но, главное, критичности и рискам для бизнеса в случаях игнорирования, откладывания или некорректной обработки этих данных.


                  3. Если на предыдущем этапе принято бизнес-решение обработать данные любой ценой, даже если это потенциально некорректно, то как минимум к результату обработки стоит приложить дополнительное поле с текстовым комментарием, в котором описать возникшие при обработке ошибки (лишние поля с их содержимым, например). Возможно эту информацию никто никогда не увидит, но по возможности стоит её стараться всегда показывать хоть как-нибудь.



                  P.S. Разумеется, в идеале не стоит в принципе создавать возможность получения некорректных данных из БД — на то нам SQL даёт множество разных инструментов для поддержания внутренней целостности данных. Но если БД уже спроектирована криво, и нет возможности её мигрировать в более строгий формат одновременно исправляя некорректные данные, тогда и возникают описанные в 2. сложности. Хотя я бы рекомендовал всё-таки очень постараться мигрировать БД, это однократная операция и она обычно обойдётся намного-намного дешевле.

                  0
                  Никогда не храните в одном поле имя и фамилию

                  Аналогично ситуации с адресами: количество вариаций имени и фамилии слишком велико, чтобы их четко различать.

                  А зачем их различать? Чтобы по имени обратиться? Практика показывает, что деление на first_name и last_name проблем больше приносит, чем удобств от обращения по имени. Например, когда вы приходите на азиатский рынок, а абстрактный пользователь сам не знает как поделить свое имя Trần Thị Mai Loan на ваши два (или что еще хуже — три) поля (или просто не хочет об этом думать). И в итоге вы получаете одно из двух:
                  1. Либо в first_name все равно оказывается не first name и вы не достигли цели.
                  2. Либо в first_name и last_name вы имеете абсолютно две одинаковых строки Trần Thị Mai Loan.

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

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

                  Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                  Самое читаемое