Денормализация базы данных
Всем привет! Сегодня мы с Вами подробно рассмотрим процесс денормализации базы данных, Вы узнаете, что такое денормализация, как и зачем вообще проводить эту денормализацию.
Нормализация базы данных
В предыдущих материалах мы подробно рассмотрели нормализацию базы данных и каждую нормальную форму базы данных, вот соответствующие статьи:
- Ненормализованная форма или нулевая нормальная форма (UNF)
- Первая нормальная форма (1NF)
- Вторая нормальная форма (2NF)
- Третья нормальная форма (3NF)
- Нормальная форма Бойса-Кодда (BCNF)
- Четвертая нормальная форма (4NF)
- Пятая нормальная форма (5NF)
- Доменно-ключевая нормальная форма (DKNF)
- Шестая нормальная форма (6NF)
Сегодня мы с Вами поговорим об обратном процессе, т.е. о денормализации.
Денормализация
Ранее мы выяснили, что данные в базе данных хранятся в таблицах, иными словами, база данных – это набор таблиц.
Суть проектирования правильной базы данных заключается в том, что необходимо сформировать тот набор таблиц, который будет отражать предметную область и соответствовать требованиям этой предметной области и требованиям разрабатываемой системы.
Проще говоря, необходимо сделать так, чтобы сформированные таблицы можно было удобно и эффективно использовать в работе, они не должны содержать каких-то аномалий и противоречивых данных, именно это и является целью проектирования базы данных.
В процессе проектирования базы данных нам на помощь приходит нормализация, которая помогает привести базу данных к тому состоянию, которое будет удовлетворять в первую очередь нашим требованиям, т.е. требованиям предметной области и требованиям разрабатываемой системы.
Иными словами, нормализация — это не процесс целенаправленного приведения базы данных к какой-то определенной нормальной форме, нормализация – это набор принципов, зная и следуя которым, мы можем спроектировать базу данных, структура которой будет гарантировать нам отсутствие определенного рода аномалий.
Таким образом, нет требования, которое обязывало бы нас приводить базу данных к максимально возможной нормальной форме, например, к 5 или 6, и не нужно нормализовать базу данных только для того, чтобы она была нормализована.
Максимально нормализованная база данных – это плохая база данных.
Дело в том, что нормализация базы данных до 5 или 6 нормальной формы значительно снижает производительность, работа с такой базой будет неэффективна и неудобна, а управлять ей будет очень сложно.
Поэтому разработчики с целью нахождения компромисса между устранением аномалий и повышением производительности могут не выполнять требования той или иной нормальной формы, более того, они могут прибегать к так называемой денормализации, т.е. обратному процессу нормализации.
Денормализация — намеренное приведение структуры базы данных в состояние, не удовлетворяющее требованиям нормализации.
Денормализация обычно проводится путем добавления избыточных данных в таблицу, т.е. тех данных, которые по требованиям той или иной нормальной формы должны выноситься в отдельную таблицу.
Например, в процессе эксплуатации базы данных у Вас возникла необходимость добавить новую характеристику, однако по требованиям определенной нормальной формы Вы должны под эту характеристику создать новую таблицу, однако требования предметной области и требования разрабатываемой системы позволяют этого не делать, при этом Вы четко понимаете, что невынесение этих данных в отдельную таблицу положительно скажется на эксплуатации базы данных. В этом случае Вы выполняете как раз денормализацию.
Также денормализация проводится и в процессе проектирования базы данных, например, когда по требованиям той или иной нормальной формы Вы должны выполнить декомпозицию таблицы, а Вы ее намерено не выполняете, все по тем же причинам, т.е. это удовлетворяет Вашим требованиям.
Методика нормализации базы данных
Существует методика нормализации, которая поможет Вам спроектировать достаточно хорошую базу данных. Суть ее в следующем.
Вы проектируете базу данных в соответствии с требованиями предметной области и требованиями разрабатываемой системы, при этом Вы строго соблюдаете все принципы нормализации и приводите базу данных практически к максимально возможной в Вашем случае нормальной форме.
В итоге Вы получаете базу данных, которая по идее не будет содержать аномалий. Однако такая версия базы данных скорей всего будет иметь не очень хорошую производительность, и с ней не очень удобно будет работать.
Поэтому затем Вы анализируете полученную структуру и руководствуясь здравым смыслом выявляете таблицы, которые можно было бы не декомпозировать, и никаких требований предметной области, которые обязывали бы делать такую декомпозицию, нет.
И таким образом, Вы выполняете денормализацию.
В конечном итоге Вы получите базу данных, в которой будет минимум аномалий, и при этом она будет иметь хорошую производительность.
Поэтому процесс проектирования базы данных иногда называют процессом нахождения компромисса между нормализацией и денормализацией, т.е. между устранением аномалий и повышением производительности.
Дело в том, что компромисс между этими составляющими даст наилучший результат, т.е. наилучшую базу данных, в каждом конкретном случае, в каждой предметной области, т.е. нормализация, как это уже не раз было отмечено, зависит от требований предметной области и требований разрабатываемой системы.
Отсюда мы можем сформулировать определение хорошей базы данных.
Хорошая база данных – это база, которая достаточно нормализована, чтобы не создавать аномалии для пользователей этой базы данных, в то же время она достаточно денормализована, чтобы иметь хорошую производительность.
Единственное, следует отметить, что в общей практике 3 нормальная форма является как раз тем компромиссом между отсутствием аномалий и приемлемой производительностью, поэтому база данных, находящаяся в третьей нормальной форме, считается достаточно нормализованной, и ее принципы все-таки рекомендовано соблюдать.
Заметка! Если Вас интересует язык SQL, то рекомендую почитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней язык SQL рассматривается как стандарт, чтобы после прочтения данной книги можно было работать с языком SQL в любой системе управления базами данных.
На сегодня это все, надеюсь, материал был Вам полезен и интересен, пока!
Что такое нормализация и денормализация
Занятие 4. Нормализация базы данных
(Продолжительность занятия 25 минут)
- описать разработку структуры базы данных на основе отношений между элементами;
- перечислить правила нормализации;
- объяснить, зачем требуется денормализация базы данных;
- описать три стратегии денормализации базы данных.
Процесс нормализации
Для разработки оптимальной структуры реляционной БД необходимо проанализировать составляющие ее элементы и отношения между ними, а затем нормализовать БД. Эти процедуры помогают выработать логическую модель данных.
Анализ элементов и отношений
Анализ элементов БД и отношений между ними позволяет построить структуру реляционной базы данных на основе идентификации объектов данных и связей между ними (рис. 6.16).
Рис. 6.16 Анализ элементов БД и отношений между ними
- элементы (или объекты), составляющие БД;
- первичные ключи (или другие идентификаторы каждого элемента/объекта);
- элементы данных или атрибуты каждого элемента или объекта. Тип данных должен обеспечивать минимально необходимое пространство для хранения элемента информации — это позволит уместить больше строк на одной странице данных и тем самым уменьшить число операции ввода/вывода;
- соотношения между элементами/объектами для создания отношений между первичными и внешними ключами.
Отношения позволяют связать таблицы средствами оператора SQL Join. Например, для связи таблиц Customers и Orders служит поле CustomerID.
Результат анализа — модель «элементы-отношения», которая логически представляет данные и связи между ними и служит основой физической реализации базы данных. Нормализация обычно является составной частью процесса анализа.
Создание отношений
В этом упражнении Вы создадите новую нормализованную базу данных.
Предположим, Вас — администратора БД одного из колледжей — попросили спроектировать базу данных для хранения информации о студентах, преподавателях и читаемых курсах.
> Создание новой базы данных
- Создайте новую базу данных, используя Microsoft Access. Назовите ее Col-ledge.mdb и сохраните в папке WA\Practice\Ch06
- Создайте таблицы и поля на основе приведенной ниже информации.
Информация о студенте
Информация о преподавателе
Информация о курсе
- В меню Tools щелкните команду Relationships.
- Добавьте все три таблицы и щелкните кнопку Close.
- Перетащите поле Faculty ID таблицы Faculty Information на поле Faculty Instructor таблицы Class Information. По умолчанию в результате этой операции создается отношение «один ко многим». Это вполне разумно — ведь преподаватель может читать несколько разных курсов.
- Щелкните кнопку Create.
- Сохраните изменения и закройте Access.
Нормализация
Цель нормализации БД — разработка хорошо организованной, оптимизированной и логичной модели базы данных до начала ее физической реализации. Этот подход минимизирует затраты на доводку базы данных на поздних стадиях разработки. Нормализация БД повышает производительность за счет экономии пространства для хранения данных и времени на их обработку.
Правила нормализации
В соответствии с правилами проектирования баз данных — правилами нормализации — каждая таблица должна описывать объекты одного типа: людей, места события или вещи. Существуют три формы нормализации базы данных (рис! 6.17), каждая из которых определяет состояние данных в БД.
Рис. 6.17 Нормальные формы
- Первая нормальная форма: запрещает повторяющиеся группы или множественные столбцы значений.
- Вторая нормальная форма: запрещает неключевому полю зависеть от части составного первичного ключа — разрешена только зависимость от первичного ключа в целом.
- Третья нормальная форма: запрещает неключевому полю зависеть от другого неключевого поля.
База данных, спроектированная по правилам нормализации, состоит из большего, чем в ненормализованной, числа компактных простых таблиц, что уменьшает избыточность данных и объем пространства, необходимого для их хранения.
Ссылочная целостность
Ссылочная целостность — это система правил, гарантирующих корректность отношений между записями связанных таблиц и исключающих возможность случайного удаления или изменения части связанных между собой данных. Для каждой строки таблицы с внешним ключом ссылочная целостность удостоверяет наличие соответствующей строки в таблице с первичным ключом. Кроме того, этот механизм предотвращает удаление строки из таблицы с первичным ключом, если она связана с таблицей с внешним ключом; чтобы все-таки выполнить это действие, необходимо сначала удалить отношение между таблицами.
Периодическая проверка ссылочной целостности помогает убедиться, что жизненно важные данные — например, уникальный идентификатор — по мере эволюции базы данных остаются корректными и доступными. Ссылочная целостность также включает операции по изменению соответствующих значений в таблицах, когда внешний ключ одной из них содержит то же значение, что и первичный ключ другой.
Процесс денормализации
Чрезмерная нормализация порождает проблемы: множество связей между таблицами вызывают избыточную нагрузку на ресурсы сервера при выполнении таких операций, как запросы. Выборочная денормализация позволяет более эффективно использовать процессорные ресурсы, что увеличивает производительность. Возможно несколько вариантов денормализации (рис. 6.18).
Рис. 6.18 Варианты денормализации
Введение избыточности
Если в результате нормализации связей становится слишком много, попробуйте искусственно ввести избыточность на уровне атрибута (столбца) или объекта (таблицы) следующим образом:
- добавьте дублирующие атрибуты (столбцы) в объекты (таблицы) базы данных;
- добавьте в таблицы БД производные атрибуты — например, агрегаты, максимальные величины и т.д.
Денормализация путем введения избыточных значений исключает излишние затраты, связанные с доступом к дополнительным таблицам.
Переопределение атрибутов
Переопределение атрибутов уменьшает объем данных одного вида. Вот как это сделать:
- добавьте производные атрибуты (столбцы) в таблицы БД;
- составной ключ, участвующий во множестве отношений, часто лучше заменить производным ключом меньшей сложности. Для этого годятся большие ключи и текстовые поля. Последнее лучше переопределить или дополнить абстрактным полем длиной не более 255 символов.
Этот способ уменьшает размер столбцов таблицы, снижая затраты ресурсов сервера во время запросов и других операций с БД.
Переопределение объектов
Переопределите объекты БД (таблицы), чтобы уменьшить влияния постороннего атрибута (столбца) или строки. Вот несколько вариантов денормализации.
- Разделить объект по атрибутам (столбцам) на два, чтобы отделить часто используемые данные. В этом случае первичный ключ дублируется в каждой новой таблице, однако повышается эффективность параллельного доступа, а таблицы часто становятся более компактными.
- Разделить объект на два по строкам. Этот прием годится для таблиц, содержащих много данных. Им стоит пользоваться и если необходим доступ к строкам по логическим подмножествам (отдел, сегмент рынка, регион и т.п.). Кроме того, любое подмножество большого набора данных, используемое активнее других, — подходящий кандидат на выделение в отдельную таблицу.
Резюме
После разработки реляционная база данных должна быть нормализована. Оптимальная реляционная БД обычно конструируется на основе анализа элементов и отношений между ними с последующей нормализацией.
Анализ элементов БД и отношений между ними позволяет выработать структуру реляционной базы данных. Цель нормализации базы данных — разработка хорошо организованной, оптимизированной и логичной схемы БД до начала ее физической реализации.
Чрезмерная нормализация БД иногда вызывает избыточную нагрузку на ресурсы сервера при выполнении таких операций, как запросы. Разумная выборочная денормализация позволяет повысить производительность БД благодаря более эффективному использованию ресурсов.
О нормализации и денормализации данных
Одним из ключевых моментов проектирования баз данных является нормализация данных — устранение избыточности информации, при которой каждый факт должен храниться только в одном месте. Аспекты нормализации данных исследованы и разработаны уже достаточно давно как с методологической, так и с математической стороны.
И все вроде бы хорошо: приводи базу как минимум к третьей нормальной форме и будет всем счастье. ) Однако всегда есть НО.
Нельзя сказать, что понятие нормализации устарело. Я думаю и надеюсь, вы со мной согласитесь, нормализация была, есть и будет важным моментом проектирования баз данных. Без нее в базах данных, во всяком случае, реляционных, будет хаос. Однако давайте вспомним, что основным принципом нормализации является разбиение таблиц на более мелкие, обладающие лучшими свойствами добавления, удаления и редактирования данных. А это означает, что увеличивается количество таблиц, приходится вводить искусственные первичные и внешние ключи, выполнять индексацию и устанавливать по ключам связи между таблиц. То есть вместо выборки данных из одной таблицы приходится собирать их по разным таблицам. В принципе, ничего страшного в этом нет, кроме необходимости хорошего знания команды select языка SQL и различных видов join-ов.
Однако в последнее время положение дел стремительно меняется. Предметные области усложняются, количество сущностей, описывающих предметную область, увеличивается, а с применением нормализации, их число возрастает как минимум в 2-3 раза. Объемы хранимой информации постоянно растут, таблицы с десятками и сотнями тысяч записей никого не удивляют. В современных СУБД все больше появляются и активно используются специализированные типы данных (например, универсальные уникальные идентификаторы UUID, xml, json, геометрические типы), которые могут занимать большие объемы памяти. Сами таблицы уже не хранятся в одной папке одного диска, а могут быть распределены по нескольким дискам, находящимся на разных серверах. В результате, выборка и анализ данных из таких таблиц становится уже дорогостоящим занятием. Объединение распределенных таблиц с большим количеством записей и объемными полями может занимать большое количество времени и ресурсов.
В связи со всем вышеизложенным, на передний план все чаще выходит понятие денормализации данных. Процесс, обратный нормализации, когда приходится объединять данные в одну таблицу или добавлять дополнительные поля. Такие действия сразу же ведут к появлению избыточности данных, но позволяют уменьшить затраты на получение информации.
Важным моментом денормализации является то, что она используется НЕ вместо нормализации, а ПОСЛЕ ее. Здесь нужно найти золотую середину: до каких пор мы можем разбивать данные, чтобы облегчить выполнение команд insert, update и delete, и когда можно допустить некоторую избыточность для ускорения выполнения команды select.
Если подходить к вопросу с точки зрения конечного пользователя, то временные затраты на выполнение запросов играют первостепенную роль. В таком случае, группировка данных по каким-то признакам и хранение их в сводных таблицах является обоснованным решением. В зависимости от частоты выполнения запросов и обновления данных, сводные таблицы можно реализовывать как в виде физических, так и в виде временных таблиц или представлений. Такие таблицы, безусловно, будут денормализованными, однако, если данные в них формируются из нормализованных таблиц, и пользователь не имеет возможности их редактирования, то это вполне приемлемый вариант. Даже при необходимости предоставления прав редактирования сводных таблиц, реализовать проверку целостности данных можно через навешивание на них триггеров или через внешние процедуры.
Часто в таблицы приходится добавлять поля, хранящие результаты выполнения агрегатных функций. Они очень сильно помогают в формировании итоговых сводных отчетов. Подобные действия также приводят к нарушению нормализации данных, но это не сильно критично, особенно если поля являются вычисляемыми или проверяются опять-таки соответствующими триггерами.
Вопрос нормализации/денормализации будет еще долго открытым. До недавних пор я сам был приверженцем строго нормализованных баз данных. Однако возможность ощутимого повышения производительности и упрощение выполнения select-ов привели меня к пониманию того, что денормализация данных при аккуратной реализации не зло, а объективная необходимость. Что мне нравится в проектировании баз данных, так это то, что есть четко проработанные инструменты, но нет идеальных решений на все случаи жизни, а значит, всегда есть место для творчества и движения вперед.
Описание основных приемов нормализации базы данных
В данной статье, ориентированной на начинающих, объясняется терминология нормализации баз данных. Понимание этой терминологии помогает вести разговор об архитектуре и проектировании реляционных баз данных.
Описание нормализации
Нормализация — это процесс организации данных в базе данных, Она включает в себя создание таблиц и установление связей между ними в соответствии с правилами, разработанными как для защиты данных, так и для повышения гибкости базы данных, устраняя избыточность и несогласованную зависимость.
Избыточность данных приводит к непродуктивному расходованию свободного места на диске и затрудняет обслуживание баз данных. Например, если данные, хранящиеся в нескольких местах, потребуется изменить, в них придется внести одни и те же изменения во всех этих местах. Изменение адреса клиента проще реализовать, если эти данные хранятся только в таблице Customers и нигде в базе данных.
Что такое «несогласованные зависимости»? Хотя пользователю интуитивно понятно искать в таблице Клиенты адрес конкретного клиента, возможно, не имеет смысла искать там зарплату сотрудника, который обращается к данному клиенту. Зарплата сотрудника связана с сотрудником (зависит от него), поэтому эти сведения следует хранить в таблице Employees (сотрудники). Несогласованные зависимости могут затруднять доступ к данным, так как путь к данным при этом может отсутствовать или быть неправильным.
Существует несколько правил нормализации баз данных. Каждое правило называется «обычной формой». Если соблюдается первое правило, база данных, как говорят, находится в «первой нормальной форме». При соблюдении первых трех правил база данных считается в «третьей нормальной форме». Хотя и другие уровни нормализации возможны, третья нормальная форма считается самым высоким уровнем, необходимым для большинства приложений.
Как и во многих формальных правилах и спецификациях, реальные сценарии не всегда позволяют обеспечить идеальное соответствие требованиям. Как правило, для выполнения нормализации приходится создавать дополнительные таблицы, и некоторые клиенты считают это нежелательным. Собираясь нарушить одно из первых трех правил нормализации, убедитесь в том, что в приложении учтены все связанные с этим проблемы, такие как избыточность данных и несогласованные зависимости.
В описаниях ниже приведены соответствующие примеры.
Первая нормальная форма
- Устраните повторяющиеся группы в отдельных таблицах.
- Создайте отдельную таблицу для каждого набора связанных данных.
- Идентифицируйте каждый набор связанных данных с помощью первичного ключа.
Не используйте несколько полей в одной таблице для хранения похожих данных. Например, для слежения за товаром, который закупается у двух разных поставщиков, можно создать запись с полями, определяющими код первого поставщика и код второго поставщика.
Что произойдет при добавлении третьего поставщика? Добавление поля не является ответом; он требует изменений в программе и таблице и не обеспечивает плавное размещение динамического числа поставщиков. Вместо этого можно поместить все сведения о поставщиках в отдельную таблицу Vendors (поставщики) и связать товары с поставщиками с помощью кодов товаров или поставщиков с товарами с помощью кодов поставщиков.
Вторая нормальная форма
- Создайте отдельные таблицы для наборов значений, относящихся к нескольким записям.
- Свяжите эти таблицы с помощью внешнего ключа.
Записи не должны зависеть от чего-либо, кроме первичного ключа таблицы (составного ключа, если это необходимо). Возьмем для примера адрес клиента в системе бухгалтерского учета. Этот адрес необходим не только таблице Customers, но и таблицам Orders, Shipping, Invoices, Accounts Receivable и Collections. Вместо того чтобы хранить адрес клиента как отдельный элемент в каждой из этих таблиц, храните его в одном месте: или в таблице Customers, или в отдельной таблице Addresses.
Третья нормальная форма
- Исключите поля, которые не зависят от ключа.
Значения в записи, которые не являются частью ключа этой записи, не принадлежат в таблице. Если содержимое группы полей может относиться более чем к одной записи в таблице, попробуйте поместить эти поля в отдельную таблицу.
Например, в таблицу Employee Recruitment (наем сотрудников) можно включить адрес кандидата и название университета, в котором он получил образование. Однако для организации групповой почтовой рассылки необходим полный список университетов. Если сведения об университетах будут храниться в таблице Candidates, составить список университетов при отсутствии кандидатов не получится. Таким образом, создайте вместо этого отдельную таблицу Universities и свяжите ее с таблицей Candidates при помощи ключа — кода университета.
ИСКЛЮЧЕНИЕ: Придерживаться третьей нормальной формы, хотя теоретически желательно, не всегда является практическим. Например, для устранения всех возможных зависимостей между полями таблицы Customers придется создать отдельные таблицы для хранения сведений о городах, почтовых индексах, торговых представителях, категориях клиентов и любых других сведений, которые могут дублироваться в нескольких записях. Теоретически нормализация стоит проводить. Однако значительное увеличение числа маленьких таблиц может привести к снижению производительности СУБД или исчерпанию памяти и числа дескрипторов открытых файлов.
Выполнять нормализацию до третьей нормальной формы может быть целесообразно только для часто изменяемых данных. Если при этом сохранятся зависимые поля, спроектируйте приложение так, чтобы при изменении одного из этих полей пользователь должен был проверить все связанные поля.
Другие формы нормализации
Четвертая нормальная форма, также называемая Boyce-Codd нормальной форме (BCNF), и пятая нормальная форма существуют, но редко рассматриваются в практическом проектировании. Игнорирование этих правил может привести к не совсем идеальному дизайну базы данных, но не должно влиять на функциональные возможности.
Нормализация таблицы примеров
Ниже приведен пример нормализации таблицы с вымышленными данными о студентах.
- Таблица до нормализации:
Student# Advisor Adv-Room Class1 Class2 Class3 1022 Петров 412 101-07 143-01 159-02 4123 Иванов 216 101-07 143-01 179-04 - Первая нормальная форма: нет повторяющихся групп Таблицы должны иметь только два измерения. Так как один студент изучает несколько курсов, эти курсы следует указать в отдельной таблице. Наличие полей Class1, Class2 и Class3 в приведенных выше записях свидетельствует о неудачном проектировании таблицы. В электронных таблицах часто используется третье измерение, но таблицы не должны. Другой способ взглянуть на эту проблему заключается в отношениях «один ко многим», не помещайте одну сторону и много сторон в одну таблицу. Вместо этого создайте другую таблицу в первой обычной форме, исключив повторяющуюся группу (Class#), как показано в следующем примере:
Student# Advisor Adv-Room Class# 1022 Петров 412 101-07 1022 Петров 412 143-01 1022 Петров 412 159-02 4123 Иванов 216 101-07 4123 Иванов 216 143-01 4123 Иванов 216 179-04 - Вторая нормальная форма: устранение избыточных данных Обратите внимание на несколько значений Class# для каждого значения Student# в таблице выше. Класс# функционально не зависит от Student# (первичного ключа), поэтому эта связь не является второй нормальной. В следующей таблице представлена вторая нормальная форма: Таблица Students:
Student# Advisor Adv-Room 1022 Петров 412 4123 Иванов 216 Таблица Registration:
Student# Class# 1022 101-07 1022 143-01 1022 159-02 4123 101-07 4123 143-01 4123 179-04 - Третья нормальная форма: устранение данных, не зависящих от ключа В последнем примере значения Adv-Room (номер кабинета научного руководителя) функционально зависят от атрибута Advisor. Решить эту проблему можно, переместив данный атрибут из таблицы Students в таблицу Faculty (факультет): Таблица Students:
Student# Advisor 1022 Петров 4123 Иванов Таблица Faculty:
Имя Room Dept Петров 412 42 Иванов 216 42
Обратная связь
Были ли сведения на этой странице полезными?