Раздел 3 — Проектирование БД и ER-моделирование

Между фразой заказчика «нам нужен учёт заказов» и работающей таблицей в PostgreSQL лежат три этапа. Их разделяют, потому что на каждом решаются разные вопросы и участвуют разные люди.

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

Простой приём для первого прохода по тексту требований: существительные — кандидаты в сущности и атрибуты, глаголы — кандидаты в связи.

Возьмём фрагмент требований:

Разбор:

Приём грубый и даёт ложные срабатывания, но как черновик первого прохода работает. Дальше каждого кандидата проверяют вопросами.

ER-модель (Entity — Relationship, «сущность — связь») предложена Питером Ченом в 1976 году. В ней всего три строительных блока.

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

Составной атрибут раскладывать или нет — решение по ситуации:

-- Вариант А: адрес одной строкой
address varchar(300)   -- 'Москва, ул. Ленина, д. 5, кв. 12'

-- Вариант Б: разложен на части
city     varchar(100),
street   varchar(150),
building varchar(20),
flat     varchar(20)

Если по городу нужен отчёт или фильтр — только вариант Б. Если адрес нужен лишь для печати на конверте — достаточно А. Вопрос решается тем, будете ли вы искать по части значения.

Связь описывается двумя независимыми характеристиками, и путать их нельзя.

Формулировать связь нужно с обеих сторон и обязательно с числами. Правильная формулировка выглядит так:

Отсюда сразу читается схема: внешний ключ client_id в таблице заказов, объявлен NOT NULL, потому что заказ без клиента невозможен.

Сравните с другой связью:

Здесь внешний ключ manager_id допускает NULL. Разница в одном слове требований — разница в ограничении схемы.

Нотаций для ER-диаграмм несколько: Чена (ромбы и овалы), IDEF1X (используется в отечественных стандартах), UML, Мартина. На практике чаще всего применяют нотацию Мартина, известную как Crow's Foot — «вороньи лапки». Её понимают все инструменты моделирования.

Каждый конец линии несёт два символа: ближний обозначает модальность, дальний — кардинальность.

Знак «вороньей лапки» < — это сторона «много». Кружок o — «может быть ноль». Вертикальная черта | — «ровно один».

Связь «клиент — заказ» в этой нотации:

   Клиент                                    Заказ
┌───────────────┐                     ┌────────────────────┐
│ PK id         │                     │ PK id              │
│    ФИО        │──||───────────o<────│ FK client_id       │
│    телефон    │                     │ FK manager_id (0..1)│
│    email      │                     │    дата            │
└───────────────┘                     │    статус          │
                                      └────────────────────┘

Читается слева направо: один заказ оформлен ровно одним клиентом.
Читается справа налево: один клиент оформил ноль или более заказов.

Связь «многие-ко-многим» на концептуальном уровне рисуют напрямую, а на логическом раскрывают через связующую сущность:

Концептуальный уровень — связь М:М показана как есть:

   Заказ                                    Товар
┌──────────┐                            ┌──────────┐
│ PK id    │──|<──────────────────o<────│ PK id    │
└──────────┘   содержит                 └──────────┘


Логический уровень — появилась связующая сущность:

   Заказ                Позиция заказа              Товар
┌──────────┐          ┌────────────────┐       ┌──────────┐
│ PK id    │──||──o<──│ PK,FK order_id │       │ PK id    │
│   дата   │          │ PK,FK product_id│──||──│   название│
└──────────┘          │    количество  │  o<   │   цена   │
                      │    цена_покупки│       └──────────┘
                      └────────────────┘

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

Слабая сущность не существует самостоятельно и не имеет собственного ключа — она идентифицируется только через родительскую.

Примеры: позиция заказа, строка накладной, комментарий к посту, оценка в ведомости. Связь с родителем называется идентифицирующей.

Это механическая часть работы — набор правил, применяемых по порядку. Творчество закончилось на предыдущем этапе.

Применим правила к разобранному фрагменту про магазин:

-- Правило 1, 2: сущности «Категория», «Товар», «Клиент», «Менеджер», «Заказ»

CREATE TABLE categories (
    id    integer      PRIMARY KEY,
    name  varchar(100) NOT NULL UNIQUE
);

-- Правило 6: связь «товар относится к категории», 1:М, обязательная
CREATE TABLE products (
    id          integer       PRIMARY KEY,
    category_id integer       NOT NULL REFERENCES categories(id),
    name        varchar(200)  NOT NULL,
    price       numeric(12,2) NOT NULL CHECK (price >= 0)
);

-- Правило 6: клиент обязателен, менеджер — нет (модальность 0..1)
CREATE TABLE orders (
    id         integer     PRIMARY KEY,
    client_id  integer     NOT NULL REFERENCES clients(id),
    manager_id integer     REFERENCES managers(id),  -- NULL допустим
    created_at timestamptz NOT NULL DEFAULT now(),
    status     varchar(20)  NOT NULL
);

-- Правило 7 + 9: связь М:М со своими атрибутами = слабая сущность
CREATE TABLE order_items (
    order_id   integer       NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id integer       NOT NULL REFERENCES products(id),
    quantity   integer       NOT NULL CHECK (quantity > 0),
    price      numeric(12,2) NOT NULL,  -- цена на момент покупки
    PRIMARY KEY (order_id, product_id)
);

Обратите внимание на два разных ON DELETE в последней таблице. Заказ удаляется — позиции уходят вместе с ним (CASCADE). Товар удалить нельзя, пока он есть в заказах — по умолчанию NO ACTION, и это правильно: история покупок не должна разрушаться.

Две классические ошибки ER-моделирования выглядят на диаграмме безобидно, а проявляются только тогда, когда запрос начинает возвращать неправильные данные.

Ловушка разветвления (fan trap)

Возникает, когда от одной сущности расходятся две связи 1:М, и путь между «листьями» через центр кажется существующим, но неоднозначен.

Отдел ──||───o<── Сотрудник
  │
  ||
  │
  o<
Проект

Вопрос: «над каким проектом работает Иванов?»
Ответ через отдел получить нельзя: в отделе три проекта
и десять сотрудников, а кто над чем работает — неизвестно.
Связь «сотрудник — проект» просто не смоделирована.

Исправление: добавить связь напрямую, чаще всего М:М.

Сотрудник ──|<── Участие ──o<── Проект

Ловушка разрыва (chasm trap)

Возникает, когда связь существует, но необязательна, и потому часть экземпляров недостижима.

Отдел ──||───o<── Сотрудник ──o|───o<── Кабинет
                              ↑
                    сотрудник может не иметь кабинета

Вопрос: «какие кабинеты закреплены за отделом?»
Кабинеты, за которыми не закреплён ни один сотрудник,
в ответ не попадут — путь через сотрудника разорван.

Исправление: связать отдел и кабинет напрямую,
если такая связь существует в предметной области.

Текстовая нотация DBML удобна тем, что схема хранится в git и правится как код:

// dbdiagram.io — описание схемы текстом
Table categories {
  id   integer [pk]
  name varchar [not null, unique]
}

Table products {
  id          integer [pk]
  category_id integer [not null]
  name        varchar [not null]
  price       decimal
}

Ref: products.category_id > categories.id  // много к одному