Нормализация и денормализация БД

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

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

Нормализация

Нормализация строится вокруг ряда правил - форм, которым должна следовать БД.

Так, нормализация сводится к 8 правилам - 8 нормальным формам (НФ).

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

В целом, я не сторонник зазубривания НФ или их академических определений - они достаточно тяжелы для восприятия. Вместо этого попытаемся разбираться на примерах. Такой подход может сузить понимание отдельных НФ от их изначального значения, но, на мой взгляд, позволяет уловить их суть и связать с конкретными задачами и проблемами, возникающими на практике. Если есть желание ознакомиться именно с академическими определениями - в целом, подойдет даже статья на вики: ссылка

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

Первая нормальная форма (1НФ)

1НФ говорит о том, что в рамках таблицы не должно быть полей, хранящих не атомарные данные.

К не атомарным данных можно отнести две большие группы:

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

Логичным видится вынесения аэропортов в отдельную таблицу и построение связи М2М между аэропортами и пассажирами. Но об этом позже.

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

Иван      Иванов      2000.01.01      Москва
Иван      Иванов      2000.01.01      Минск
Петр      Петров      2000.01.02      Киев
Петр      Петров      2000.01.02      Варшава
…

• Комплексные типы. Скажем, мы могли бы в рамках таблицы Пассажиры хранить ФИО единым полем. Но это может усложнить работу с таблицей - например, затруднить поиск пассажиров по имени или фамилии. Таким образом, в рамках приведения к 1НФ потребовалось бы разделение колонки ФИО на 3: Имя, Фамилия и Отчество. В нашем случае отчество не хранится, поэтому вполне достаточно колонок Имя и Фамилия.

Кроме того, есть несколько правил для 1НФ. Здесь они приведены в вольной трактовке:

• Порядок записей (строк) в таблице не должен иметь значения;
• Порядок колонок не должен иметь значения;
• Не должно быть дубликатов - строк, где значения всех столбцов эквивалентны;

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

• Не должно быть нескольких колонок, относящихся к одному атрибуту в рамках доменной области системы. Условно, не должно быть булевых колонок Минск, Варшава, Москва и т.д., определяющих наличие такого аэропорта в списке любимых у конкретного пассажира. Вместо этого должна быть колонка Любимый аэропорт, которая будет хранить идентификатор (например, название) такого аэропорта;
• Не должно быть скрытой функциональности. Т.е. не должно быть функциональности на основании применения к таблицам каких-либо специальных операторов или добавления дополнительных эффектов для стандартных операторов. Говоря еще проще, данные в таблице должны быть просто данными, не неся в себе скрытого смысла.

Вторая нормальная форма (2НФ)

2НФ регламентирует отношения между значениями в таблице и их идентификатором - то, что мы называем Primary Key.

Итак, значения отдельных ячеек в таблице в рамках записи должны зависеть только от РК.

Если РК представлен одной колонкой - можно говорить о соблюдении 2НФ (при условии, что она соответствует 1НФ - помните, что каждая следующая НФ требует выполнения предыдущей), если все колонки зависят только от РК.

Если РК является составным, не должно быть колонок, значение которых зависит только от его части - такие колонки колонки должны быть обособлены в отдельную таблицу с собственным РК.

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

Допустим, в ней есть колонки: Продавец, Номер рейса, Аэропорт отправления, Аэропорт прибытия, Класс билета, Цена.

Первичным ключом определим колонки Номер рейса и Класс билета. 

В этой таблице есть проблема: цена на билет зависит от продавца, соответственно, зависит не только от РК. Таким образом, в рамках приведения к 2НФ требуется выделить таблицу Цена на билеты с колонками Номер рейса, Класс билета (как FK на Билеты), Продавец и Цена. В новой таблице РК будет состоять из Номер Рейса, Класс билета и Продавец. 

Ит таблица Билеты, в свою очередь, будут исключены атрибуты Продавец и Цена.

В описанном примере есть еще одна проблема: часть атрибутов (Аэропорт отправления, Аэропорт прибытия) в таблице Билеты относятся только к части составного ключа. Но их декомпозицию правильнее будет рассмотреть в рамках следующего пункта.

Третья нормальная форма (3НФ)

3НФ развивает идею 2НФ и говорит о том, что таблица не должна содержать транзитивных зависимостей. Т.е. в рамках таблицы не должно быть данных, которые раскрывают детали не сущности, которая представлена таблицей, а лишь какой-то ее составной части.

Логично, что Аэропорт отправления и Аэропорт прибытия зависят от номера рейса, но не зависят от класса билета. Т.е. эти атрибуты являются транзитивными по отношению к номеру рейса.

Следовательно, для приведения к 3НФ требуется выделить таблицу Рейс с РК Номер рейса и не ключевыми атрибутами (колонками) Аэропорт отправления и Аэропорт прибытия.

Таблица билет, в свою очередь, будет состоять лишь из двух колонок - Номер рейса и Класс билета.

По сути, 3НФ может быть реализована в рамках 2НФ - ведь транзитивная зависимость является частным случае зависимости не от РК. Разница этих форм, в основном, идеологическая:

• 2НФ регламентирует зависимость атрибутов от полного РК;
• 3НФ регламентирует, что неключевые колонки должны предоставлять детали, характеризующие РК. Соответственно, если колонка характеризует другую сущность - она и должна относиться к этой сущности.

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

На практике такие ситуации, как правило, регулируются на уровне приложения и их фиксация на уровне БД не так критична.

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

Нормальная форма Бойса — Кодда

Эта НФ регламентирует зависимость не на уровне колонок, а на уровне возможных значений этих колонок.

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

Соответственно, возможны ситуации, когда указанная у машины модель не существует для указанной марки.

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

Таким образом, хоть сама НФ регламентирует соответствие зависимостей на уровне данных, а не структуры, практически ее можно свести к наличию транзитивных зависимостей, что делает ее продолжением 3 НФ. Поэтому нормальную форма Бойса — Кодда также называют усиленной третьей нормальной формой.

Четвёртая нормальная форма (4НФ)

4НФ регламентирует разделение различных наборов атрибутов (колонок), которые имеют многозначные зависимости от других атрибутов на уровне данных.

В рамках примера предлагаю рассмотреть приведенный на Википедии: ссылка

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

Но, что еще более важно, в данном примере также вводится понятие внедренной зависимости и разбирается ситуация, когда начальный набор атрибутов нельзя декомпозировать в силу объективных причин. Как следствие, 4НФ формально достигается, хотя изначальная проблема остается.

Пятая нормальная форма (5НФ)

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

Пример (осторожно, много букв): ссылка

Доменно-ключевая нормальная форма

Данная НФ регламентирует введение набора допустимых значений для каждого атрибута (колонки). По понятным причинам, это возможно не для всех атрибутов.

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

По сути, это является весьма строгим продолжением 3НФ (и Нормальная форма Бойса — Кодда).

Шестая нормальная форма (6НФ)

6НФ регламентирует полную декомпозицию. Иными словами, избавление от любых возможных зависимостей на уровне данных в рамках сущности до тех пор, пока это возможно (помним о понятии внедренной зависимости введенном в 4НФ).

Пример с вики (информацию о хронологических БД можно опустить): ссылка

Сама по себе это НФ не вводит каких-то четких требований в рамках борьбы с конкретными зависимостями, лишь декларирует, что декомпозиция на основании нетривиальных зависимостей должна быть произведена даже если не была реализована в рамках приведения к предыдущим НФ.

Денормализация

Итак, мы разобрались с нормальными формами, их актуальностью и проблемами, которые они призваны решить.

Но нормализация плотно связана с декомпозицией таблицы и, соответственно, увеличением числа связей между таблицами.

На практике это может привести к тому, что отдельные SELECT-запросы станут слишком дорогими из-за множества JOIN’ов, которые требуются для формирования выборки. 

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

Это не значит, что во всей БД структура лишится соответствия 3НФ - лишь в местах, которые требуют оптимизации производительности. Но о соответствии конкретной НФ нельзя говорить до тех пор, пока существует хоть одно отношение в БД, которое этой НФ не соответствует.

Кроме того, денормализация может означать отказ и от более ранних НФ - например, использование массивов или json в колонках БД однозначно является отказом от 1НФ, но может использоваться. Часто это бывает актуально для данных, которые нет смысла выделять в отдельные сущности. Обычно это актуально, если подобная декомпозиция привела бы к О2О. 

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

В целом, денормализация является достаточно простым (технически) и очевидным инструментом увеличения производительности SELECT-запросов с JOIN’ами. Но она повышает риски несогласованности данных и увеличивает затраты на уровне приложения (или, в отдельных случаях, БД через механизм триггеров и др.) для поддержания данных в корректном состоянии. Вследствие этого, решение о денормализации следует принимать исключительно при глубоком понимании разрабатываемой системы и для решения (или избегания) конкретных проблем.

На сегодня все!

Если что-то непонятно или не получается – welcome в комменты к посту или в лс:)

Канал: https://t.me/ViamSupervadetVadens

Мой тг: https://t.me/ironicMotherfucker

Дорогу осилит идущий!