Основные этапы проектирования баз данных

02

Почему базы данных нельзя создавать «сразу в коде»?

Кризис ранней разработки программного обеспечения и цена архитектурной ошибки.

Стихийное создание таблиц («Антипаттерн»)

Разработчик начинает проект сразу с команды CREATE TABLE в СУБД под текущие экраны приложения. Результат:

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

Системный жизненный цикл (DBLC)

База данных строится как фундамент информационной системы на основе формального маршрута проектирования:

  • Разделение уровней абстракции (архитектура ANSI/SPARC).
  • Независимость логики бизнеса от конкретной СУБД.
  • Гарантия целостности данных на уровне математической теории.
03

Жизненный цикл базы данных (DBLC)

Пять непрерывных этапов от постановки задачи до вывода системы из эксплуатации.

Этап Название этапа Главная цель фазы Ключевой выходной артефакт
1 Системный анализ Исследование предметной области и сбор требований Техническое задание (ТЗ) на разработку БД
2 Концептуальное проектирование СУБД-независимое моделирование сущностей и связей Инфологическая ER-диаграмма
3 Логическое проектирование Трансляция в реляционную модель и нормализация Реляционная схема в 3НФ (таблицы, PK, FK)
4 Физическое проектирование Привязка к СУБД, оптимизация производительности DDL-скрипты, схемы индексов, партиции
5 Эксплуатация и сопровождение Непрерывная работа, резервирование, мониторинг План бэкапов (RPO/RTO), схемы репликации
04

Архитектурный фундамент: Трехуровневая модель ANSI/SPARC

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

Внешний уровень (External)

Представления (Views) для различных категорий пользователей. Бухгалтер видит расчетный счет, врач — медицинскую карту. Скрывает детали общей структуры.

Концептуальный уровень

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

Внутренний уровень (Internal)

Физическое размещение данных на носителях: форматы страниц данных, B-Tree деревья индексов, сжатие, дисковые массивы RAID, кодировки.

05

Этап 1: Системный анализ и сбор требований

Понимание бизнес-процессов заказчика и выявление информационных потоков.

Входы и источники информации

  • Первичные документы (накладные, акты, рецепты, квитанции).
  • Регламенты и стандарты (ГОСТы, 152-ФЗ о защите персональных данных).
  • Глубинные интервью с топ-менеджерами и операторами.

Выходные документы и критерий перехода

  • Техническое задание (ТЗ): реестр функций, объемы данных.
  • Спецификация SLA: допустимое время отклика запроса.
  • Gate (переход): официальное подписание ТЗ обеими сторонами.
06

Этап 2: Концептуальное (инфологическое) проектирование

Моделирование предметной области на языке сущностей, свойств и взаимосвязей.

Ключевые элементы ER-модели

  • Сущность (Entity): концептуальный класс объектов реального мира.
  • Атрибут (Attribute): содержательная характеристика сущности.
  • Связь (Relationship): логическое взаимодействие (1:1, 1:N, M:N).

Главное правило концептуального уровня

Никакой зависимости от конкретной СУБД! На данном этапе не существует типов данных SQL, ограничений памяти, дисковых индексов и движков таблиц.

Модель валидируется непосредственно с предметными экспертами.

07

Этап 3: Логическое проектирование и реляционная модель

Трансляция концептуальной ER-диаграммы в реляционные отношения и нормализация.

Правила трансформации модели

  • Сущность → реляционная таблица (отношение).
  • Атрибут → столбец отношения с заданным доменом.
  • Связь 1:N → добавление внешнего ключа (FK) в дочернюю таблицу.
  • Связь M:N → создание кросс-таблицы (таблицы связки).

Математическая нормализация

Последовательное приведение схемы к Третьей нормальной форме (3НФ):

  • 1НФ: атомарность значений атрибутов.
  • 2НФ: отсутствие частичной зависимости от составного ключа.
  • 3НФ: отсутствие транзитивных функциональных зависимостей.
08

Осознанная денормализация: OLTP против OLAP

Различие архитектурных подходов в транзакционных системах и аналитических хранилищах.

Транзакционные системы (OLTP)

  • Цель: быстрая и надежная запись, обновление, вставка.
  • Структура: строгая нормализация (3НФ/БКНФ).
  • Минимизация: полное устранение избыточности и риска аномалий обновления.

Аналитические хранилища (OLAP / DWH)

  • Цель: мгновенное чтение и агрегация миллиардов строк.
  • Структура: денормализованные схемы («Звезда», «Снежинка»).
  • Компромисс: избыточность данных ради исключения медленных многотабличных JOIN.
09

Этап 4: Физическое проектирование — Привязка к СУБД

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

Выбор СУБД и типов данных

Подбор СУБД (PostgreSQL, ClickHouse) и точных системных типов (BIGINT, NUMERIC, TIMESTAMP WITH TIME ZONE, JSONB).

Стратегия индексирования

Проектирование B-Tree индексов для диапазонных поисков, Hash-индексов для равенства, GiST/GIN для геоданных и полнотекстового поиска.

Партиционирование данных

Разделение сверхбольших таблиц (логи, телеметрия, чеки) на секции по диапазонам дат (Partitioning by Range) для локализации ввода-вывода.

10

Физический уровень: Как работают индексы?

Переход от полного сканирования таблицы (Seq Scan) к логарифмическому поиску (Index Scan).

B-Tree индекс (Сбалансированное дерево)

Обеспечивает сложность поиска O(log N). Позволяет мгновенно находить записи по первичному ключу и интервалам дат:

CREATE INDEX idx_orders_date ON orders(order_date);

При миллионе строк поиск требует всего 3–4 дисковых чтения страниц.

GiST и пространственные индексы

Незаменимы для гео-приложений (каршеринг, доставка, службы такси):

CREATE INDEX idx_cars_loc ON cars USING GIST (current_location);

Быстрый расчет радиуса «найти свободные авто в радиусе 500 метров» со временем отклика < 10 мс.

11

Физический уровень: Безопасность и соответствие 152-ФЗ

Инженерная защита персональных данных и конфиденциальной информации.

Прозрачное шифрование (TDE)

Шифрование табличных пространств на уровне файловой системы алгоритмом AES-256. Кража дискового накопителя не приводит к утечке.

Журнал аудита доступа

Фиксация каждого факта просмотра и изменения медицинских или финансовых записей: идентификатор пользователя, IP-адрес, временная метка.

Ролевая модель (RBAC)

Принцип минимальных привилегий (Least Privilege). Запрет прямого доступа к таблицам — доступ только через интерфейсные представления и процедуры.

12

Этап 5: Эксплуатация, администрирование и надежность

Обеспечение непрерывной доступности бизнеса и защита от аварийных потерь.

Метрики RPO и RTO при авариях

  • RPO (Recovery Point Objective): максимально допустимый объем потерь данных во времени (например, потеря транзакций не более чем за 5 минут).
  • RTO (Recovery Time Objective): время, за которое база данных должна быть полностью поднята из резервной копии.

Стратегия резервного копирования

  • Full Backup: полный дамп структуры и данных (еженедельно).
  • Differential / Incremental: копирование только изменившихся блоков (ежедневно/ежечасно).
  • WAL Archiving: непрерывная репликация журнала транзакций.
13

Инженерная карта перехода между фазами DBLC

Критерии прохождения Quality Gates в реальных IT-проектах.

Фаза DBLC Входные данные Выходные документы Критерий перехода (Gate)
1. Системный анализ Первичка, регламенты, интервью ТЗ, матрица SLA Подписание ТЗ заказчиком
2. Концептуальный проект ТЗ, словарь данных ERD (Chen/Crow's Foot) Валидация бизнес-логики
3. Логический проект ER-модель Схема в 3НФ, PK/FK/CHECK Отсутствие аномалий данных
4. Физический проект Логическая схема, профиль SLA DDL, индексы, партиции Прохождение нагрузочных тестов
5. Эксплуатация Прод-база, транзакции Регламент бэкапов, репликация Непрерывный цикл работы
14

Практическое применение: Отраслевые сценарии

Четыре направления командного и индивидуального моделирования на рабочем листе.

Вариант 1: Сеть аптек со складом

Учет серий и сроков годности препаратов (FEFO), рецептурные бланки, распределенная логистика между 50 точками.

Вариант 2: Каршеринг (HighLoad)

Телеметрия с CAN-шины авто, расчет геозон с GiST-индексами, требование SLA к отклику запроса < 100 мс.

Вариант 3: Аналитика маркетплейса

Кликстрим, корзины, платежи. Осознанная денормализация под схему «Звезда» в аналитическом хранилище ClickHouse.

Вариант 4: Медицинская клиника

Строгое соблюдение 152-ФЗ, разделение идентификаторов пациентов и диагнозов, шифрование TDE и журнал аудита.

15

Уроки проектирования: Чему нас учит «Bobby Tables»?

Культовый мем мировой IT-культуры о катастрофических последствиях пренебрежения целостностью данных.

xkcd #327: Exploits of a Mom

Комикс xkcd №327: «Exploits of a Mom» (автор Рэндалл Манро) — наглядная иллюстрация того, что происходит, когда валидация доменов и параметров запросов игнорируется проектировщиками.

Мама назвала сына:
Robert'); DROP TABLE Students;--

Школа склеивала SQL-запросы напрямую из пользовательского ввода без проверки ограничений. База данных выполнила команду удаления и уничтожила ведомости за весь учебный год.

Атрибуция и свободная лицензия первоисточника:
Произведение: xkcd #327 "Exploits of a Mom" (автор Randall Munroe).
Лицензия: Creative Commons Attribution-NonCommercial 2.5 Generic (CC BY-NC 2.5).
Методический вывод: строгая спецификация доменов атрибутов, ограничения целостности CHECK и параметризованные запросы на физическом этапе — фундаментальный долг инженера БД.