Логическая и физическая модель данных

После концептуальной ER-диаграммы (03.03.01 ER-диаграммы (сущность-связь)) аналитик детализирует структуру данных на логическом и физическом уровнях. Логическая модель отвечает на вопрос «что храним и какие правила действуют», физическая — «как это реализовать в конкретной СУБД с учётом производительности и ограничений». Разделение уровней сохраняет трассируемость от требований до DDL и снижает риск «проектирования на коленке» прямо в базе.

Логическая модель данных (LDM) — формальная спецификация сущностей, атрибутов, ключей и ограничений предметной области без привязки к СУБД. Физическая модель данных (PDM) — реализация LDM в выбранной технологии: типы столбцов, индексы, партиции, правила каскадирования FK.

1. Суть темы

Моделирование данных на проекте обычно идёт цепочкой из трёх уровней — не смешивайте их на одном артефакте без явной пометки:

Вход и выход LDM: CDM из 03.03.01 ER-диаграммы (сущность-связь) — прямой предшественник LDM. На вход аналитик приносит согласованную ER (сущности, связи, ключевые атрибуты) и фрагмент глоссария по терминам домена. На выходе — LDM с разрешёнными M:N, доменами, ключами и ограничениями плюс полный словарь атрибутов; далее LDM трансформируется в PDM или в спецификацию БД сервиса.

Граница с ER: в 03.03.01 ER-диаграммы (сущность-связь) вы согласовываете что существует в предметной области. Здесь — как это формализуется: разрешение M:N, выбор ключей, нормальные формы, перенос правил в ограничения и типы. ER фиксирует сущности и связи; словарь данных — все атрибуты LDM с доменами, источниками и правилами — см. 03.03.03 Словарь данных (описание).

Жизненный цикл: в классическом SDLC LDM создаётся на анализе, PDM — на проектировании. В Agile оба уровня часто эволюционируют в одном инкременте: LDM уточняется по ходу спринта, PDM — вместе с миграциями в конвейере (07.06 CICD и конвейеры — влияние на требования). Критерий готовности к разработке не «идеальная диаграмма», а согласованный артефакт спецификации БД с трассировкой к требованиям (02.03.04 Трассировка требований (концепция), 10.01.15 Шаблон матрицы трассировки).

LDM ≠ модель обмена: одна логическая модель сервиса может иметь несколько физических представлений — таблицы PostgreSQL, JSON в REST (03.05.03 Спецификации контрактов (OpenAPI, AsyncAPI, Protobuf)), события Kafka (03.05.04 Асинхронное взаимодействие (очереди, Kafka, Events)). Структура полей в API и топиках должна согласовываться с LDM, но не дублировать её один в один: в контракте допустимы view-модели, агрегаты и поля только для транспорта. В распределённой архитектуре одна LDM-сущность может разойтись на несколько таблиц в разных сервисах, а один документ NoSQL — объединить несколько логических сущностей; граница транзакции и агрегата задаёт, что меняется атомарно — см. 03.06.02 Основы Domain-Driven Design без углубления в DDD-паттерны.

LDM и DFD: хранилища на диаграмме потоков данных соответствуют сущностям LDM, но DFD описывает движение данных между процессами, а LDM — структуру внутри хранилища. При трассировке «процесс → store → сущность» сверяйте имена, не подменяя одно другим.

Когда ER, когда Class Diagram: для структуры хранения достаточно ER/LDM. UML Class Diagram (06.01.01 Структурные диаграммы (Class, Object, Package)) уместна, когда моделируете поведение объектов в коде (наследование, операции, интерфейсы) — не заменяет LDM, а дополняет её на границе домена и реализации.

2. Ключевые понятия

2.1 Компоненты логической модели

LDM включает разрешение M:N через ассоциативные сущности и вынос многозначных атрибутов в отдельные сущности. Детализацию атрибутов (логический тип, допустимые значения, источник, правила вычисления) фиксируют в словаре данных — не на ER-диаграмме.

2.2 Суррогатный и естественный ключ

• Естественный ключ — бизнес-атрибут (Email, ИНН, НомерДоговора). Риск при смене формата или значения.
• Суррогатный ключ — искусственный идентификатор (UUID, автоинкремент), не несущий бизнес-смысла.

Практика для аналитика: PK сущности — суррогатный; естественный ключ — UK с явным бизнес-правилом уникальности. Исключение: слабая сущность с составным PK из FK родителя и порядкового номера (см. 03.03.01 ER-диаграммы (сущность-связь)). Для dedup и интеграции фиксируйте natural key отдельно в спецификации, даже если PK суррогатный.

2.3 Нормализация

Нормализация — пошаговое устранение избыточности и аномалий обновления. Для OLTP-систем целевой уровень LDM — 3НФ.

• 1НФ: атрибуты атомарны, нет повторяющихся групп. Вместо поля Телефоны — сущность ТелефонКлиента.
• 2НФ: 1НФ + каждый неключевой атрибут полно зависит от всего составного PK. Пример: сущность ПозицияЗаказа с PK (order_id, line_no); атрибут НаименованиеТовара зависит только от product_id, а не от (order_id, line_no) — его место в Товар, не в позиции.
• 3НФ: 2НФ + нет транзитивных зависимостей неключевых атрибутов. В Сотрудник не хранят НазваниеОтдела, если есть FK на Отдел.
• BCNF: строже 3НФ. Если на ревью модели всплывают спорные функциональные зависимости — эскалируйте к архитектору БД или DBA, не «дожимайте» форму самостоятельно.

Когда ослаблять нормализацию — правило для аналитика:

JSON/XML внутри одного атрибута на LDM трактуйте как нарушение 1НФ, если поля внутри JSON участвуют в бизнес-правилах — либо выносите в отдельные атрибуты/сущности, либо явно моделируете документ как отдельную сущность с осознанным компромиссом. Поля контракта API (JSON) и атрибуты LDM согласуются отдельно — см. 07.03 Основы XML, JSON, YAML.

2.4 Производные атрибуты: снимок vs денормализация

СуммаЗаказа в Заказ может появиться по разным основаниям:

1. Бизнес-снимок на LDM — сумма фиксируется на момент оформления и не пересчитывается при смене цены товара. Основание: правило из требований, не производительность.
2. Денормализация на PDM — поле дублирует агрегат из позиций ради ускорения отчётов; требует триггера, batch-пересчёта или допустимой eventual consistency.

На ревью модели явно укажите основание — иначе разработка и QA по-разному интерпретируют поведение при изменении цен.

2.5 Физическая модель: от LDM к реализации

Трансформация LDM → PDM для реляционной СУБД:

1. Сущность → таблица (customers, orders).
2. Атрибут → столбец с физическим типом (Деньги (10,2) → NUMERIC(10,2) в PostgreSQL).
3. PK → PRIMARY KEY; часто с SERIAL/UUID.
4. FK → FOREIGN KEY с ON DELETE / ON UPDATE, отражающими бизнес-правила.
5. UK и NOT NULL — из логических ограничений.
6. Индексы и партиции — по сценариям доступа и NFR.

NoSQL и гибриды: LDM для ядра домена остаётся реляционной спецификацией. Физически сущность может стать коллекцией документов (MongoDB), семейством колонок (Cassandra) или графом. Маппинг фиксируйте в спецификации: какие сущности — в документе целиком, какие — в отдельном хранилище, как сохраняются FK-семантика и уникальность без JOIN. Детали SQL и типов — 07.01 Базы данных и SQL на уровне аналитика.

Типовые физические паттерны (без ухода в DBA-глубину):

• Soft delete — deleted_at вместо физического удаления; влияет на UK и запросы.
• Audit columns — created_at, updated_by для операционной трассировки; требования регуляторики (хранение, доступ к чувствительным данным, шифрование) задаются отдельно — см. 08.06 Юридические и регуляторные требования РФ (PCI-DSS) и 08.07 Основы кибербезопасности для системного аналитика.
• Multi-tenant — tenant_id в PK/FK или отдельная схема; правило изоляции — в LDM как ограничение.
• Retention и архив — политики хранения и партиции по дате из NFR (RPO/RTO, срок жизни записи).

2.6 Индексы и партиции

Индексы — физические структуры по столбцам из WHERE, JOIN, ORDER BY. Аналитик в спецификации описывает сценарий доступа и поля («заказы клиента по email», поля customer_id, email, status); тип индекса, порядок столбцов и финальный DDL — зона DBA или разработчика БД.

Пример: сценарий «все заказы клиента по email» — join Клиент ↔ Заказ по ссылке на клиента; в спецификации зафиксируйте необходимость индекса по полю связи в Заказ и UK на Email в Клиент. Если фильтр часто комбинирует клиента и статус — передайте DBA требование к составному индексу, не проектируя его самостоятельно.

Партиционирование больших таблиц (по дате, региону) опирается на прогноз объёмов и retention из NFR.

3. Как применяется в работе аналитика

3.1 Процесс LDM → PDM

1. Нормализовать CDM до LDM: разрешить M:N, уточнить ключи и домены, довести до 3НФ (с обоснованными исключениями).
2. Согласовать LDM с бизнесом и архитектором; заполнить словарь данных по всем атрибутам.
3. Выбрать СУБД и стратегию хранения (реляционная / документная / гибрид).
4. Построить PDM: типы, FK-политики, индексы (сценарии — аналитик; DDL — DBA), денормализация только с NFR-обоснованием.
5. Зафиксировать решения в 10.01.03 Шаблон спецификации БД сервиса — маппинг «сущность → таблица», «атрибут → столбец», трассировка к требованиям.
6. Согласовать изменения: приоритизация в backlog — 02.05.02 Управление приоритетами в условиях изменчивости; формальное одобрение изменения схемы через CCB — 02.05.02 Управление изменениями (Change Control Board) (это отдельный процесс от приоритизации, несмотря на совпадающий номер в каталоге).

Критерии «готово к разработке»: LDM согласована; словарь данных покрывает все атрибуты; PDM или миграционный план описан; бизнес-правила продублированы в CONSTRAINT или документированы в application layer; критичные сценарии доступа названы; трассировка к требованиям есть.

Reverse engineering: при миграции legacy восстанавливайте LDM из существующей схемы, фиксируете gap analysis относительно to-be, затем целевую PDM — цепочка as-is LDM → gap → to-be PDM; см. 10.02 Разбор кейса "Миграция legacy-системы".

3.2 Пример: интернет-магазин

Логическая модель того же магазина, что в 03.03.01 ER-диаграммы (сущность-связь): домены и связи, без типов СУБД. Ключи и правила — в словаре данных.

M:N Заказ–Товар разрешена через ПозицияЗаказа. Отгрузка — 1:1: уникальная ссылка на заказ, самого заказа без отгрузки ещё может не быть. сумма_заказа и цена_единицы — производные снимки по бизнес-правилу, не денормализация ради JOIN. PK/FK/UK и маппинг имён на физические таблицы — в словаре данных и спецификации БД.

Физическая модель (PostgreSQL, фрагмент):

CREATE TABLE customers (
    customer_id       SERIAL PRIMARY KEY,
    email             VARCHAR(255) NOT NULL UNIQUE,
    full_name         VARCHAR(500) NOT NULL,
    registration_date DATE NOT NULL DEFAULT CURRENT_DATE
);
 
CREATE TABLE categories (
    category_id       SERIAL PRIMARY KEY,
    name              VARCHAR(200) NOT NULL UNIQUE
);
 
CREATE TABLE products (
    product_id        SERIAL PRIMARY KEY,
    category_id       INTEGER NOT NULL
        REFERENCES categories(category_id) ON DELETE RESTRICT,
    name              VARCHAR(1000) NOT NULL,
    current_price     NUMERIC(10,2) NOT NULL CHECK (current_price >= 0),
    stock_quantity    INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0)
);
 
CREATE TABLE orders (
    order_id          SERIAL PRIMARY KEY,
    customer_id       INTEGER NOT NULL
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    order_date        TIMESTAMP NOT NULL DEFAULT NOW(),
    total_amount      NUMERIC(12,2) NOT NULL,
    status            VARCHAR(50) NOT NULL DEFAULT 'NEW'
);
 
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);
 
CREATE TABLE order_items (
    order_item_id     SERIAL PRIMARY KEY,
    order_id          INTEGER NOT NULL
        REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id        INTEGER NOT NULL
        REFERENCES products(product_id) ON DELETE RESTRICT,
    quantity          INTEGER NOT NULL CHECK (quantity > 0),
    unit_price        NUMERIC(10,2) NOT NULL
);
 
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
 
CREATE TABLE addresses (
    address_id    SERIAL PRIMARY KEY,
    customer_id   INTEGER NOT NULL
        REFERENCES customers(customer_id) ON DELETE CASCADE,
    city          VARCHAR(200) NOT NULL,
    street        VARCHAR(500) NOT NULL,
    is_default    BOOLEAN NOT NULL DEFAULT FALSE
);
 
CREATE TABLE payments (
    payment_id    SERIAL PRIMARY KEY,
    order_id      INTEGER NOT NULL
        REFERENCES orders(order_id) ON DELETE RESTRICT,
    amount        NUMERIC(12,2) NOT NULL CHECK (amount > 0),
    paid_at       TIMESTAMP
);
 
CREATE TABLE shipments (
    shipment_id   SERIAL PRIMARY KEY,
    order_id      INTEGER NOT NULL UNIQUE
        REFERENCES orders(order_id) ON DELETE RESTRICT,
    carrier       VARCHAR(200) NOT NULL,
    shipped_at    TIMESTAMP
);

ON DELETE RESTRICT на orders.customer_id и на оплате — клиента с заказами и заказ с платежом не удаляют. ON DELETE CASCADE на адресах — адрес не живёт без клиента. shipments.order_id UNIQUE переносит логическую 1:1 в ограничение СУБД. DDL здесь — иллюстрация для согласования FK-политик; аналитик фиксирует правила и сценарии, финальные индексы согласует с DBA. Понимать, как логические правила превращаются в такие конструкции, — обязанность аналитика на ревью PDM.

3.3 Аналитическое моделирование — отдельный контекст

Kimball (звезда/снежинка), Data Vault, Medallion и Data Mesh решают задачи хранилищ и платформ данных, а не транзакционной LDM/PDM сервиса. Витрины строят из OLTP-LDM через ETL/ELT — они не заменяют операционную модель. Если проект включает DWH или lakehouse — это отдельный контур архитектуры (03.06.01 Архитектурные паттерны распределённых систем, 07.01 Базы данных и SQL на уровне аналитика). Не подменяйте ими логическую модель OLTP-системы.

Фрагмент звезды по заказам того же магазина. В центре факт строки заказа, вокруг измерения. line_amount уже посчитан при загрузке: отчёт не идёт JOIN-ом в OLTP за quantity × unit_price.

3.4 ИИ как вспомогательный инструмент

LLM могут черновить схемы из текста требований (исследования вроде SchemaAgent, arXiv:2503.23886). Роль аналитика не снимается: проверка соответствия требованиям, ключей, кардинальности и NFR — через 02.04.01 Верификация и валидация (V&V) и ревью по 08.04 Рецензирование и инспекции (техники Фейгана). Практические сценарии — 11.07 Практические кейсы применения LLM в анализе; промпты — 11.03 Промпт-инжиниринг для аналитика.

4. Типовые ошибки и ограничения

Ограничение статьи: здесь — пара LDM/PDM для операционных систем. Глубокая нормализация SQL, tuning и классификация NFR — в 07.01 Базы данных и SQL на уровне аналитика и 08.02 Нефункциональные требования (НФТ) — классификация и стандарты. Каталоги, lineage и governance метаданных — в 10.01.14 Шаблон глоссария и словаря данных и 03.03.03 Словарь данных (описание).

Связи с другими разделами базы

• 03.03.01 ER-диаграммы (сущность-связь) — концептуальный уровень (CDM), вход в LDM.
• 03.03.03 Словарь данных (описание) — атрибуты, домены и бизнес-определения LDM.
• 03.04.02 Диаграмма потоков данных (DFD) — хранилища DFD vs сущности LDM.
• 03.06.02 Основы Domain-Driven Design — границы агрегата и транзакции в распределённой модели.
• 10.01.03 Шаблон спецификации БД сервиса — фиксация LDM, PDM, миграций и правил.
• 10.01.14 Шаблон глоссария и словаря данных — связка терминов домена с элементами модели.
• 10.01.15 Шаблон матрицы трассировки — связь требований с сущностями и полями.
• 07.01 Базы данных и SQL на уровне аналитика — SQL, типы, проверка физической схемы.
• 07.03 Основы XML, JSON, YAML — согласование полей контракта с LDM.
• 07.06 CICD и конвейеры — влияние на требования — миграции и версионирование схемы.
• 08.02 Нефункциональные требования (НФТ) — классификация и стандарты — объёмы, latency, retention → PDM.
• 02.03.03 Спецификация требований (SRS, Use Case, User Story) — источник сущностей и сценариев доступа.
• 02.03.04 Трассировка требований (концепция) — трассируемость решений модели.
• 03.05.03 Спецификации контрактов (OpenAPI, AsyncAPI, Protobuf) — согласование LDM с контрактами API.
• 03.06.01 Архитектурные паттерны распределённых систем — аналитические слои и платформы данных.
• 10.02 Разбор кейса "Миграция legacy-системы" — reverse engineering LDM из legacy.

Источники и дополнительные материалы

Книги и стандарты

• DAMA International (2017). DAMA-DMBOK: Data Management Body of Knowledge. 2nd ed. Technics Publications.
• Симсион, Г., Витт, Г. (2005). Data Modeling Essentials. 3rd ed. Morgan Kaufmann.
• Элмасри, Р., Навате, Ш. (2015). Fundamentals of Database Systems. 7th ed. — ER-, логическое и физическое проектирование.
• Дейт, К. Дж. (2004). An Introduction to Database Systems. 8th ed. — нормализация.
• Kimball, R., Ross, M. (2013). The Data Warehouse Toolkit. 3rd ed. — dimensional modeling (контекст DWH, не OLTP-LDM).
• IIBA (2015). A Guide to the Business Analysis Body of Knowledge (BABOK v3). Задача 6.3 Specify and Model Requirements, техника Data Modeling — логическое моделирование в контексте спецификации требований.

Статьи и материалы

• Vertabelo. From Logical to Physical: A Practical Guide — примеры трансформации для разных СУБД.
• Kimball Group. Dimensional Modeling Techniques — обзор методик Kimball для витрин.
• Pure Storage (2025). Denormalized vs. Normalized Data — компромиссы нормализации и денормализации.
• Snowflake. What Is a Data Catalog? — каталоги метаданных (контекст governance, не ядро LDM/PDM).
• Wang et al. (2025). SchemaAgent: A Multi-Agents Framework for Generating Relational Database Schema. arXiv:2503.23886 — генерация логической схемы из требований; результаты требуют ревью аналитиком.

Документация СУБД

• PostgreSQL Documentation — разделы Data Types, DDL, Indexes.
• Oracle Database Documentation — Concepts, SQL Language Reference.