Тема 4. Проектирование структур баз данных — Средства проектирования структур БД: объекты

1. Введение: от реляционной алгебры к системным объектам СУБД

В 3 семестре вы изучили реляционную модель данных, правила нормализации отношений от 1NF до BCNF, основы SQL-запросов и построение первичных индексов. На этом этапе база данных рассматривалась преимущественно как совокупность таблиц (отношений) и связей между ними.

Однако в реальных промышленных информационных системах (банковских АБС, медицинских архивах PACS, логистических центрах, ERP) база данных представляет собой высокоорганизованный комплекс взаимосвязанных объектов, управляемых системным каталогом СУБД. Грамотное проектирование требует применения специализированных объектов, решающих задачи изоляции подсистем, разграничения доступа, кэширования ресурсоемких расчетов и потоковой генерации уникальных ключей.

2. Иерархия объектов в системном каталоге реляционной СУБД

Каждая промышленная реляционная СУБД (PostgreSQL, Oracle, Microsoft SQL Server) организует пространство хранения в виде строгой многоуровневой иерархии:

Разбор уровней иерархии:

• Кластер СУБД (Cluster): единый запущенный программный экземпляр СУБД, использующий общий пул оперативной памяти и один системный порт (например, TCP 5432). Кластер содержит один или несколько независимых каталогов баз данных.
• База данных (Database): замкнутое хранилище. По умолчанию транзакции выполняются строго внутри одной базы данных, обеспечивая полную изоляцию прикладных систем.
• Схема (Schema / Namespace): внутреннее логическое пространство имен внутри конкретной базы данных. Схемы позволяют организовать модульность архитектуры (например, модуль logistics, модуль security, модуль billing), предотвратить конфликты имен таблиц и тонко настраивать права доступа групп пользователей.
• Объекты хранения и обработки (Schema Objects): таблицы, представления, индексы, генераторы последовательностей, триггеры и хранимые процедуры.

3. Последовательности (SEQUENCE) и генераторы первичных ключей

SEQUENCE (Последовательность) — это специализированный объект базы данных, предназначенный для генерации неповторяющейся числовой последовательности (чаще всего целочисленной: INTEGER или BIGINT).

Зачем нужна отдельная SEQUENCE вместо MAX(id) + 1?

Наивная попытка вычислить следующий идентификатор через SELECT MAX(id) + 1 FROM table приводит к двум катастрофическим проблемам:

1. Гонка условий (Race Condition): при одновременной вставке двух транзакций они прочитают одинаковое значение MAX(id), что приведет к нарушению ограничения уникальности PRIMARY KEY.
2. Падение производительности: блокировка всей таблицы ради вычисления максимума превращает многопоточную систему в медленную однопоточную очередь.

Объект SEQUENCE работает вне транзакционного контекста через атомарные счетчики в оперативной памяти (Shared Memory):

-- Создание независимой последовательности
CREATE SEQUENCE billing.receipt_seq
    START WITH 100000
    INCREMENT BY 1
    MINVALUE 100000
    MAXVALUE 999999999
    CACHE 20;

-- Основные функции работы с последовательностью:
-- 1. nextval('receipt_seq') - атомарно инкрементирует и возвращает новое значение.
-- 2. currval('receipt_seq') - возвращает текущее значение в рамках ДАННОЙ пользовательской сессии.
-- 3. setval('receipt_seq', 500000) - устанавливает счетчик на заданное число.

4. Представления: Динамические (VIEW) против Материализованных (MATERIALIZED VIEW)

Представление (View) в SQL — это именованный сохраненный запрос. В современной инженерии баз данных существует два принципиально разных вида представлений:

Синтаксис и регламент обновления MATERIALIZED VIEW:

-- 1. Создание материализованной витрины
CREATE MATERIALIZED VIEW billing.mv_monthly_debt_summary AS
SELECT 
    s.district_id,
    date_trunc('month', a.accrual_date)::date AS billing_month,
    count(DISTINCT s.subscriber_id) AS active_subscribers_count,
    sum(a.amount) AS total_accrued_rub,
    sum(coalesce(p.paid_amount, 0)) AS total_paid_rub,
    sum(a.amount - coalesce(p.paid_amount, 0)) AS total_debt_rub
FROM billing.subscribers s
JOIN billing.accruals a ON s.subscriber_id = a.subscriber_id
LEFT JOIN (
    SELECT accrual_id, sum(amount) AS paid_amount
    FROM billing.payments
    GROUP BY accrual_id
) p ON a.accrual_id = p.accrual_id
GROUP BY s.district_id, date_trunc('month', a.accrual_date);

-- 2. Создание обязательного УНИКАЛЬНОГО индекса для фонового обновления
CREATE UNIQUE INDEX idx_mv_debt_pk 
ON billing.mv_monthly_debt_summary (district_id, billing_month);

-- 3. Регламентное обновление данных:
-- Обычное обновление (блокирует чтение витрины эксклюзивным замком):
-- REFRESH MATERIALIZED VIEW billing.mv_monthly_debt_summary;

-- Конкурентное фоновое обновление (клиенты продолжают читать старый снимок без задержек):
REFRESH MATERIALIZED VIEW CONCURRENTLY billing.mv_monthly_debt_summary;

5. Доменные типы данных: ENUM и составные структуры

Реляционная модель требует атомарности значений (1NF). Однако введение перечислимых и составных типов на уровне СУБД повышает надежность модели данных и предотвращает запись мусорных строковых значений:

-- Перечислимый тип для статусов логистических отправлений
CREATE TYPE logistics.parcel_status AS ENUM (
    'REGISTERED',
    'IN_TRANSIT',
    'ARRIVED_AT_HUB',
    'OUT_FOR_DELIVERY',
    'DELIVERED',
    'RETURNED'
);

-- Составной тип для географических координат
CREATE TYPE logistics.coordinates AS (
    lat NUMERIC(9, 6),
    lon NUMERIC(9, 6)
);

6. CASE-инструменты визуального проектирования и DDL-инженерия

В современной промышленной разработке проектирование архитектуры СУБД осуществляется в специализированных CASE-пакетах (Computer-Aided Software Engineering):

• pgModeler: графический редактор с открытым исходным кодом, ориентированный на PostgreSQL. Позволяет проектировать все типы объектов (схемы, таблицы, материализованные представления, индексы, роли) на диаграмме и в один клик получать валидный DDL.
• DBeaver EE / PRO: универсальный клиент с модулем визуального моделирования ERD, обратной инженерии и синхронизации схем данных.
• dbForge Studio: промышленный комбайн для работы со схемами БД, визуального построения запросов и отслеживания различий структур.
• ERwin Data Modeler: классический enterprise-инструмент для сквозного проектирования на концептуальном, логическом и физическом уровнях.

Концепции Forward и Reverse Engineering:

• Forward Engineering (Прямое проектирование): преобразование визуальной диаграммы сущностей и объектов в текстовый DDL-скрипт (набор операторов CREATE ...) для развертывания на боевом сервере.
• Reverse Engineering (Обратное проектирование): подключение к работающей СУБД, чтение системных таблиц каталога (интроспекция) и автоматическая реконструкция визуальной схемы БД со всеми внешними ключами и зависимостями.