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

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

В снове моей нелюбви лежит высосанность из пальца. Как и во многих других областях знаний, теория в основном развивалась значительно позднее (1970-е гг.) накопленной практики применения (примерно с середины 1960 гг. по 1970 г, IBM). Фактически она была навязыванием теоретической наукообразной "скорлупы", сложно сочетаемой с практикой. Хуже того, на планете нет ни одной БД, соответствующей всем нормальным формам. Еще хуже того, сами формы в некоторых формулировках своих определений содержат обсуждение своей же абсурдности. И, напоследок, ещё камень. Беседуя с преподавателями, разглагольствующими о бесценности изучения понятия нормализации в их учебных курсах, я заметил одну закономерность. Большинство из них вообще никогда не имели дел с программированием БД, либо делали это несколько раз. Успешно, как они полагают. Само собой, что кроме аргументов "это же и так понятно/очевидно", "все/каждый это знает/понимает", "без этого вообще никак не управиться" и т.п., ответа на мой простой вопрос о прикладном назначении нормальных форм я не получил ни разу. Найдете – пришлите, буду безмерно благодарен.

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

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

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

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

  1. Нет упорядочивания строк, то есть порядок строк не имеет значения и смысла.
  2. Столбцы также следуют в произвольном порядке.
  3. Отсутствуют повторяющиеся строки.
  4. Каждое пересечение строки и столбца содержит ровно одно значение из соответствующего набора данных.
  5. Все столбцы не являются составными.

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

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

  1. Повторяющиеся значения полей (то есть внутри колонки таблицы).
  2. Поле содержит несколько значений.
  3. Значение поля может быть разделено, хотя и представляет собой единое целое

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

Также важным положением является запрет на хранение рассчитываемых данных.

Ключевые поля

Данное понятие имеет множество равнозначно используемых синонимов: ключ, идентификатор, ID, key.

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

Первичный ключ – поле таблицы БД, используемое для однозначной идентификации записи. Такое поле может быть только одно.

Потенциальный ключ – поле таблицы БД, которое соответствует требованиям к первичному ключу, но не используются в качестве такового.

Суперключ (superkey) – комбинация атрибутов (полей), которая может использоваться в качестве первичного ключа. Обычно в таблице можно выделить множество таких суперключей.

Тип данных и размер ключевого (идентификационного) поля

Для разбора этого вопроса потребуется вспомнить некоторые правила расчета количества информации. Разнообразие вариантов содержания поля равно количеству вариантов одного символа в степени количества отведенных для поля символов (n). Для двоичной записи это будет 2n, для десятичной – 10n, для русского текста – 66n, а если использовать символы Unicode – (216)n.

Также вспомним, что в реляционной СУБД поле содержит целое количество байт.

Из сказанного выше вытекает выраженное в цифрах:

Знаков (байт)Запись символовЧисло вариантов
1Двоичная256
Десятичная10
Русская66
ANSI256
UnicodeНужно кратно 2 батам
2Двоичная65 536
Десятичная100
Русская66
ANSI65 536
Unicode65 536
3Двоичная16 777 216
Десятичная1000
Русская4 356
ANSI16 777 216
Unicode65 536
4Двоичная4 294 967 296
Десятичная10 000
Русская287 496
ANSI4 294 967 296
Unicode4 294 967 296
5Двоичная1 099 511 627 776
Десятичная1 000 000
Русская1 252 332 576
ANSI1 099 511 627 776
Unicode4 294 967 296

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

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

Двоичная система является простейшей, но однобайтовое число состоит из 8 цифр, а пятибайтовое – из 40. Попробуйте его прочитать. Нереально! Зато эффективность (информационная емкость разряда) существенно выше, чем во всех остальных случаях.

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

Русские буквы для нас также крайне привычны (66 их потому, что заглавные и строчные различаются). Но, представьте себе, что вам придется читать набор из шести случайных букв разных регистров (ЪйяьЦы). Не очень приятно, правда?

ANSI/ASCII (8 бит) следует рассматривать в качестве стандартного компьютерного набора символов, где каждый из них кодируется одним байтом. Однако, не все они могут быть оценены просто глазами, так как могут оказаться пустыми, псевдографическими, диакритическими, трудно отличимыми и т.д. Для того, чтобы оценить картину полностью, запустите стандартную программу Таблица символов (файл CharMap.exe) и просмотрите любой шрифт. Отсюда вытекает, что подобный код может существовать, но работать с ним может только компьютер без участия пользователя или программиста.

Unicode здесь приведен скорее в качестве курьеза, так как этот формат плохо реализован в СУБД и должен иметь поля кратные двум. В остальном он количественно повторяет стандартную кодировку. Символов для обозначения всех жителей нам потребуется только три (но шесть байт!), однако туда войдут ВСЕ символы, в том числе и иероглифические и те, места для которых просто зарезервированы, то есть увидеть их нельзя!

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

Именно в связи с этим хочется обратить дополнительное внимание на целочисленный тип данных, который описывает числа по сути просто двоичной системой и, соответственно, обладает наибольшей читаемостью, емкостью и удобством манипулирования. Отсюда сделаем вывод, что для ключевых полей (идентификаторов) следует использовать поля целочисленных типов. Многие современные СУБД позволяют задать и количество байт, отводимых для этого поля, а не фиксировать его, как это практиковалось ранее. Также для них прекрасно реализовано автоматическое наращение (автоинкремент, autoincrement), что избавляет разработчика от написания сложных и крайне ответственных процедур.

Избыточность или денормализация

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

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

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

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

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

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

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

Оценка объема БД для всех жителей планеты

По оценке, на конец 2013 года население Земли составляет около 7 135 000 000 человек, а к 2050 г. по оценке ООН, оно составит 9,5 млрд человек. Для удобства расчетов мы примем его за 10 млрд, что позволит учеть рост базы на 30–35 лет (нереально большой срок) и упростит расчеты.

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

... Посещаем занятия.


Copyright © 1993–2020 Мацкявичюс Д.А. Все права защищены.
Никакая часть сайта не может быть воспроизведена никаким способом без письменного разрешения правообладателя и явной ссылки на данный ресурс.