Чуть больше чем просто блог :)

Нормализация БД MySQL, Нормальные формы

При проектировании БД важно чтобы:

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

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

Всего существует 8 нормальных форм:

  1. Первая нормальная форма
  2. Вторая нормальная форма
  3. Третья нормальная форма
  4. Нормальная форма Бойса
  5. Четвёртая нормальная форма
  6. Пятая нормальная форма
  7. Доменно-ключевая нормальная форма
  8. Шестая нормальная форма

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

Я буду применять первые 4 нормальные формы.

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

Отношение находится в первой нормальной форме (сокращённо 1НФ), если:

  • Все строки таблицы различные.

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

Пример таблицы не приведенной к 1НФ:

Фирма

Модели

BMW

M5, X5M, M1

Nissan

GT-R

Нарушение нормализации 1НФ происходит в моделях BMW, т.к. в одной ячейке содержится список из 3 элементов: M5, X5M, M1, т.е. он не является атомарным. Преобразуем таблицу к 1НФ:

Фирма

Модели

BMW

M5

BMW

X5M

BMW

M1

Nissan

GT-R

Методы приведения к 1NF:

  • Устраните повторяющиеся группы в отдельных таблицах (одинаковые строки).

  • Создайте отдельную таблицу для каждого набора связанных данных.

  • Идентифицируйте каждый набор связанных данных с помощью первичного ключа (добавить уникальный id для каждой строки)

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

Отношение находится во 2НФ, если:

  • Находится в 1НФ;

  • Каждое не ключевое поле таблицы зависит от Первичного Ключа(ПК).

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

Например. Эта таблица находится в первой нормальной форме, но не во второй.

Модель

Фирма

Цена

Скидка

M5

BMW

5500000

5%

X5M

BMW

6000000

5%

M1

BMW

2500000

5%

GT-R

Nissan

5000000

10%

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

Модель

Фирма

Цена

M5

BMW

5500000

X5M

BMW

6000000

M1

BMW

2500000

GT-R

Nissan

5000000

Фирма

Скидка

BMW

5%

Nissan

10%

Методы приведения к 2NF:

  • Создайте отдельные таблицы для наборов значений, относящихся к нескольким записям;

  • Свяжите эти таблицы с помощью внешнего ключа (В нашем случае – это поле Фирма).

 

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

Отношение находится в 3НФ, когда:

  • Таблица находится во второй нормальной форме;

  • Любой её не ключевой атрибут функционально зависит только от первичного ключа.

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

Рассмотрим таблицу:

Модель

Магазин

Телефон

BMW

Риал-авто

87-33-98

Audi

Риал-авто

87-33-98

Nissan

Некст-Авто

94-54-12

Таблица находится во 2НФ, но не в 3НФ.

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

В результате разделения исходной таблицы получаем две, находящиеся в 3НФ:

Магазин

Телефон

Риал-авто

87-33-98

Некст-Авто

94-54-12

Модель

Магазин

BMW

Риал-авто

Audi

Риал-авто

Nissan

Некст-Авто

Благодаря этому правилу при удалении телефонных номеров мы не потеряем перечень автомобилей в магазинах.

Методы приведения к 3NF:

  • Удаление полей не зависящих от ключа

 

Нормальная форма Бойса-Кодда (НФБК) (частная форма третьей нормальной формы)

Отношение находится в НФБК, когда:

  • Таблица находится в третьей нормальной форме;

  • В таблице должен быть только один потенциальный первичный ключ.

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

Предположим, рассматривается отношение, представляющее данные о бронировании стоянки на день:

Номер стоянки

Время начала

Время окончания

Тариф

1

09:30

10:30

Бережливый

1

11:00

12:00

Бережливый

1

14:00

15:30

Стандарт

2

10:00

12:00

Премиум-В

2

12:00

14:00

Премиум-В

2

15:00

18:00

Премиум-А

Тариф имеет уникальное название и зависит от выбранной стоянки и наличии льгот, в частности:

  • «Бережливый»: стоянка 1 для льготников

  • «Стандарт»: стоянка 1 для не льготников

  • «Премиум-А»: стоянка 2 для льготников

  • «Премиум-B»: стоянка 2 для не льготников.

Недостатком данной структуры является то, что, например, по ошибке можно приписать тариф «Бережливый» к бронированию второй стоянки, хотя он может относиться только к первой стоянке.

Можно улучшить структуру с помощью разбиения таблицы на две (Тарифы и Бронирование) с добавлением атрибута Имеет льготы, получив отношения, удовлетворяющие НФБК:

Тарифы:

Тариф

Номер стоянки

Имеет льготы

Бережливый

1

Да

Стандарт

1

Нет

Премиум-В

2

Нет

Премиум-А

2

Да

Бронирование:

Тариф

Время начала

Время окончания

Бережливый

09:30

10:30

Бережливый

11:00

12:00

Стандарт

14:00

15:30

Премиум-В

10:00

12:00

Премиум-В

12:00

14:00

Премиум-А

15:00

18:00

Методы приведения к BCNF

  • Вынести в отдельную таблицу потенциальные первичные ключи

 


 

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

Как проще понять эти нормализации?

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

И еще раз вышеописанные правила, только в скомпонованом виде:

  1. Каждая таблица должна иметь только 1 первичный ключ;
  2. Строка данных не должна содержать в себе список значений, а должна быть максимально простой. Списки должны быть разбиты на соответствующее количество уникальных строк;
  3. Наборы данных, которые могут относится к нескольким записям хранить в отдельной таблице и связывать их по ключу;
  4. Разбивать таблицы по контексту. Например у вас есть таблица работников, разбитых по своим отделам производства, с указанием их зарплаты и контактными данными. Лучше разбить это на таблицы: “Отделы производства”, “Сотрудники”, “Контакты сотрудников”, “ЗП сотрудников”.
Другие материалы в этой категории: « Типы данных столбцов и их требования к памяти в MySQL

Оставить комментарий

Убедитесь, что вы вводите (*) необходимую информацию, где нужно
HTML-коды запрещены

Авторизация