Статья основана на втором видео из 30 тем курса SQL c 0 от Аристова Евгения. Ссылки на видео на платформах RUTUBE и VK видео.
В данной статье подробно разбираются нормальные формы, уровни и правила проектирования баз данных.
В прошлой статье про реляционную модель не раз упоминался термин нормализация. Пришло время разобрать, что такое нормальные формы.
Нормальные формы
Представим, что нам нужно хранить автомобили с их моделями. Допустим, у BMW три модели, у Nissan одна. В данном случае всего четыре машины (например, в наличии в автосалоне).
Первая нормальная форма предполагает, что одному атрибуту одной строки должно соответствовать одно значение (по реляционной теории 70-х годов).
Денормализованный вид таблицы:
Первая нормальная форма:
Применяется по причине того, что несколько значений, хранящихся в одной ячейке, имеют проблемы с обновлением (update).
Вторая нормальная форма предполагает, что если от ключевого реквизита зависит не ключевое поле, то нужно разделить на две таблицы.
В данном случае ключ (уникальное поле, которое однозначно идентифицирует запись) – “модель+фирма”
Денормализованный вид таблицы:
Вторая нормальная форма:
Почему это необходимо? Для минимизации логической избыточности и исключения дублирования. Предположим, в таблице миллион строчек. Без использования второй нормальной формы для обновления скидки нам придется обновить все записи (то есть миллион). В нормализованном виде фирмы из родительской таблицы(первой) записаны в дочернюю со скидкой(вторую).
Третья нормальная форма предполагает, что если от одного неключевого поля зависит другое неключевое, то нужно разделить на несколько таблиц.
В данном случае поле Телефон зависит от поля Магазин
Денормализованный вид таблицы:
Третья нормальная форма:
Сопоставив магазин с магазином, можно найти номер телефона.
Нормальная форма Бойса-Кодда, четвертая, пятая и шестая нормальные формы используются крайне редко. Их в данной статье разбирать не будем.
Нормализацию (в основном третью нормальную форму) используют для OLTP нагрузки (много маленьких запросов). Для OLAP нагрузки наоборот денормализуют (собирают в общие большие таблицы во избежание join).
Уровни проектирования
Жизненный цикл проекта состоит из трех этапов: концептуальная, логическая и физическая модели.
- Концептуальная модель
На этом этапе нужно определиться с сущностями (что храним, как храним) и задокументировать их. Далее создать ER-модель (то есть определить связи между сущностями). Определить атрибуты каждой сущности и первичные ключи(набор записей, делающих запись уникальной), а также внешние ключи (ссылка в дочерней таблице на родительский ключ).
На картинке зеленое – сущности, красное – реквизиты, а голубое – отношения.
Сущность (entity) – это реальный или представляемый тип объекта, информация о котором должна сохраняться и быть доступна.
Связь (relationship) – это графически изображаемая ассоциация, устанавливаемая между двумя сущностями. Связь может существовать между двумя разными сущностями или между сущностью и ей же самой (рекурсивная связь)
Возможны связи на основе отношений:
Один-ко-многим – самый распространенный вид связи. Один – родительский объект (первичный ключ), многие – дочерний объект (внешний ключ – foreigh key).
Один-к-одному – самый редкий вид связи. Чаще используется единая таблица.
Многие-ко-многим – вид связи, который обычно создается не напрямую, а через промежуточную таблицу(с сопоставлением объектов). Например, книги и авторы: у одного автора может быть много книг и у одной книги много авторов.
- Логическая модель
На этом этапе выбирается вид модели данных (SQL, NoSQL, newSQL – разницу рассмотрим на шестой теме). Далее определяется набор таблиц (прописываются типы данных). Проводится декомпозиция (процесс, похожий на нормализацию – создание атомарных объектов, которые потом связываются через первичный и внешний ключи), нормализация, иногда денормализация (избыточность, например materialized view). Также определяются набор поддерживаемых транзакций и соответствие ему структуры данных, требования поддержки целостности данных.
Логическая модель обсуждается с заказчиками и утверждается ими.
- Физическая модель
На этом этапе проектируются таблицы средствами выбранной СУБД (в рамкой одной СУБД могут быть разные движки, виды индексов и т.д.), реализуются бизнес правила (ограничения на данные, например, возраст >18 лет), определяются транзакционная модель и физическая организация данных (системы хранения таблиц), настраивается высокая доступность (использования нескольких серверов для переключения между ними в случае неполадок) и бэкапы (например, для тестовых стендов). Планируются ресурсы (pool connect, proxy), определяются правила безопасности и защиты информации (обсуждаются с заказчиком).
На уровне физической модели организуется сопровождение, мониторинг и алертинг!
Примеры физических моделей:
На следующей лекции подробно разберем, как лучше именовать первичные и внешние ключи, чтобы из схемы сразу было понятно отношение объектов (в базе Учебный центр легко запутаться из-за некорректного именования полей).
Где можно проектировать?
Самый простой вариант – psql (терминальный клиент для работы с PostgreSQL), однако в нем отсутствует как таковая визуализация. Реверсивный анализ в таком случае можно строить в Dbeaver (бесплатная GUI для работы с PostgreSQL)
Ещё варианты:
- app.diagrams.net (необходимо выбрать диаграмму связей сущностей)
- Microsoft Access
- Lucida
- Fiddle – в нем есть режим коллаборации, несколько человек могут удаленно работать над одним и тем же запросом. Минус сервиса – не поддерживает русский язык в данных.
Правила проектирования
Имена объектов задаются на английском без пробелов
Имена должны иметь логический смысл (не x1, x2, y1, y2)
2 классических вида именования:
- snake_case – имена через подчеркивание)
- camelCase – имена слитно, каждое новое слово с большой буквы. Имена приводится к нижнему регистру автоматически
В кавычках можно использовать разный регистр, русский язык
Если имя поля неочевидное – нужно прописывать документацию в комментариях
Презентация к статье здесь.
Третья из 30 тем будет скоро доступна. Если вы хотите быстрее получить доступ – присоединяйтесь к онлайн группе, ссылка доступна в описании курса.
Добавить комментарий