ИТ.03 - 11 - Модели БД и способы их представления. ER-диаграммы

Введение

На предыдущих занятиях мы работали только с одной таблицей, но в реальных системах данные всегда разбиваются на несколько связанных сущностей. Чтобы эти связи были продуманными, структуру базы данных сначала проектируют в виде модели.

В этой теме разберём:

• что такое модель данных и зачем она нужна;
• уровни моделей (концептуальный, логический, физический);
• элементы ER-модели: сущности, атрибуты, связи и их кратности;
• как построить простую ER-диаграмму в нотации Mermaid и как она помогает при создании связанных таблиц.

Зачем нужны модели данных

Модель данных описывает, какую информацию мы храним и как объекты связаны между собой. Она:

• даёт всем участникам проекта единое понимание предметной области;
• служит основой для создания таблиц и ограничений в СУБД;
• помогает выявить лишние связи и дубли ещё до написания SQL-кода;
• показывает зоны, в которых придётся выполнять JOIN, INSERT в несколько таблиц и т.д.

Уровни моделей

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

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

```mermaid
flowchart LR
  %% Сущности
  customer["Клиент"]
  order["Заказ"]
  product["Товар"]
  order_item{"Позиция заказа"}

  %% Атрибуты
  customer_name([ФИО])
  customer_contact([Контакт])
  order_status([Статус])
  created_at([Дата создания])
  product_price([Цена])
  in_stock([В наличии])
  quantity([Количество])

  %% Связи
  customer --- order
  order --- order_item --- product

  customer -.- customer_name
  customer -.- customer_contact
  order -.- order_status
  order -.- created_at
  product -.- product_price
  product -.- in_stock
  order_item -.- quantity
```

На концептуальной диаграмме фигурируют только ключевые объекты предметной области: покупатель оформляет заказ, заказ состоит из позиций, каждая позиция указывает на конкретный товар. Прямоугольники обозначают сущности (Клиент, Заказ, Товар), овалы — важные характеристики. Сущности соединяем сплошными линиями, а сущности и их атрибуты — пунктирными, чтобы легко отличать связи объектов от перечисления свойств. Сущность Позиция заказа рисуем ромбом, чтобы подчеркнуть её связующую роль между заказом и товаром: она помогает явно зафиксировать, какие товары входят в конкретный заказ.

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

Важно помнить, что концептуальный уровень не требует полной детализации. Мы фиксируем только те элементы, которые понятны всем участникам проекта и помогают обсудить предметную область. Технические особенности (статусы, роли пользователей, дополнительные справочники) появляются на следующих этапах, когда структура базовых сущностей согласована.

Логический уровень

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

```mermaid
erDiagram
  customer["Клиент"] {
    INTEGER id PK
    STRING full_name
    STRING phone
    STRING email
  }
  employee["Сотрудник"] {
    INTEGER id PK
    STRING full_name
    STRING role
  }
  product["Товар"] {
    INTEGER id PK
    STRING title
    DECIMAL price
    BOOL   is_active
  }
  order["Заказ"] {
    INTEGER id PK
    INTEGER customer_id FK
    INTEGER employee_id FK
    DATE created_at
    STRING status
  }
  order_item["Позиция заказа"] {
    INTEGER id PK
    INTEGER order_id FK
    INTEGER product_id FK
    INTEGER quantity
    DECIMAL unit_price
  }

  customer ||--o{ order : ""
  employee ||--o{ order : ""
  order ||--o{ order_item : ""
  product ||--o{ order_item : ""
```

Здесь уже фиксируем, какие атрибуты обязательны, где нужны первичные и внешние ключи, а также кратности связей. Например, orders.employee_id указывает, кто оформил заказ, order_items хранит цену и количество на момент покупки, а products.is_active помогает отключать позиции из каталога. Диаграмма логического уровня служит прямой подсказкой для структуры таблиц, но пока не привязана к конкретной СУБД или типам данных.

Физический уровень

Когда структура согласована, переносим её в SQL: выбираем конкретные типы данных, добавляем индексацию, каскадные правила, комментарии.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL,
  phone TEXT,
  email TEXT
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  employee_id INTEGER NOT NULL REFERENCES employees(id),
  status TEXT NOT NULL DEFAULT 'new' CHECK (status IN ('new', 'confirmed', 'shipped', 'completed', 'cancelled')),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL,
  role TEXT CHECK (role IN ('admin', 'manager'))
);

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  price REAL NOT NULL,
  is_active INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE order_items (
  id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price REAL NOT NULL
);

На физическом уровне мы учитываем особенности конкретной СУБД: выбираем типы (TEXT, REAL, INTEGER), добавляем ограничения (NOT NULL, CHECK, REFERENCES) и значения по умолчанию. Например, у заказов статус ограничен перечнем допустимых значений — это позволяет валидацией на уровне базы отсечь опечатки и гарантировать корректный бизнес-процесс. Дополнительно можно добавить индексы и указать действия ON DELETE / ON UPDATE для внешних ключей: они определяют, что происходит с дочерними строками при изменении или удалении родительской записи. В следующей лекции разберём эти режимы подробнее и покажем, как выбирать подходящий вариант для конкретной модели.

ER-диаграмма и нотации

ER-диаграмма (Entity-Relationship Diagram) — графическое описание модели. На ней показывают сущности, атрибуты и связи между ними. Чтобы диаграммы «читались» одинаково разными специалистами, используются нотации — наборы правил, которые описывают, как изображать сущности, атрибуты и кратности.

Различные нотации используются в корпоративных методологиях или CASE-средствах, часто добавляют собственные символы для первичных/внешних ключей, жизненных циклов сущности, наследования и других аспектов работы с данными.

Перечислим самые известные нотации и приведём примеры.

Нотация Питера Чена

Классический вариант ER-диаграмм: сущности отображаются прямоугольниками, атрибуты — овалами, связи — ромбами. Хороша для обсуждения предметной области без технических деталей, когда нужно визуально показать, какие объекты существуют и как они взаимодействуют.

Нотация Crow's Foot

Самая популярная нотация в современных инструментах: сущности рисуются прямоугольниками с атрибутами, а связи показываются линиями с «вороньими лапками», которые наглядно отображают кратности (1 — 1, 1 — N, M — N). Подходит, когда важно быстро считывать тип связи.

Нотация IDEF1X

Стандарт Министерства обороны США: сущности оформляются прямоугольниками с явной пометкой PK и FK, связи делятся на определяющие (сплошные) и не определяющие (пунктирные). Используется для строгой документации корпоративных схем, где важно явно фиксировать ключи и зависимости.

Нотация UML

Часть универсального языка моделирования UML: ER-схема представляется диаграммой классов, где перечисляются атрибуты с типами, методы (по необходимости) и ассоциации с указанием множественности. Удобна, если команда уже описывает систему на UML и хочет использовать единый визуальный язык.

Нотация Баркера (Barker's Notation)

Разработана Ричардом Баркером и используется в Oracle Designer и других CASE-инструментах. Сущности делятся на обязательные и необязательные атрибуты, связи похожи на «вороньи лапки», но снабжены символами (O, P), которые показывают обязательность участия. Позволяет зафиксировать бизнес-правила прямо на диаграмме.

Нотация Бахмана (Bachman)

Предложена Чарльзом Бахманом и до сих пор встречается в крупных корпоративных системах. Связи изображаются линиями со стрелками, которые задают направление навигации между сущностями, а прямоугольные блоки могут объединять связанные объекты. Несмотря на «винтажный» стиль, помогает документировать наследие старых систем.

Нотация Mermaid

Мы будем использовать нотацию Mermaid (подвид Crow's Foot на языке Mermaid). Диаграммы легко создать на сайте mermaid.live, где можно вводить текстовую запись, а система автоматически построит граф. Преимущества такой записи:

• диаграмма хранится как текст, её легко версионировать;
• не нужно привязываться к конкретной СУБД;
• одну и ту же диаграмму можно встроить в документацию, репозиторий или показать в браузере.

Сущности и атрибуты

• Сущность (Entity) — объект предметной области, о котором нужно хранить данные (например, Студент, Курс, Заказ).
• Атрибут (Attribute) — характеристика сущности (ФИО студента, номер курса, цена заказа).

Пример описания сущности:

```mermaid
erDiagram
  student["Студент"] {
    INTEGER   id PK               "Первичный ключ"
    STRING    first_name          "Имя"
    STRING    last_name           "Фамилия"
    INTEGER   group_id FK         "Группа"
    ENUM      gender              "Пол (м/ж)"
    BOOL      is_active           "Учится / отчислен"
    DATE      date_of_birth       "Дата рождения"
    DATE      date_of_assignment  "Дата зачисления"
    DATE      date_of_graduation  "Дата окончания"
  }
```

На диаграмме мы указываем только логические типы полей — они описывают назначение атрибута без привязки к конкретной СУБД. Чтобы было понятно, что означают подписи в примере, собрали самые часто встречающиеся обозначения в краткую памятку.

Связи и кратности

Связь (Relationship) показывает, как сущности взаимодействуют. Для каждой связи важно задать кратности (cardinality) — сколько экземпляров сущности участвует.

В нотации Mermaid кратности обозначаются символами:

```mermaid
erDiagram
  student["Студент"] {
    INTEGER   id PK         "Первичный ключ"
    STRING    first_name    "Имя"
    STRING    last_name     "Фамилия"
    INTEGER   group_id FK   "Группа"
  }
  group["Группа"] {
    INTEGER   id PK         "Первичный ключ"
    STRING    title         "Название группы"
  }

  group ||--o{ student : "содержит"
```

Определяющие и не определяющие связи

В ER-моделях выделяют два типа связей:

• Определяющая (identifying) — дочерняя сущность не существует без родительской. Обычно её первичный ключ включает ключ родителя. На диаграмме связь рисуют сплошной линией.
• Не определяющая (non-identifying) — сущность существует сама по себе, а внешний ключ добавляется только для связи. На диаграмме это пунктирная линия.

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

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

Пример: разбираем ER-диаграмму интернет-магазина

Чтобы закрепить теорию про сущности, связи и кратности, рассмотрим знакомый кейс. Возьмём упрощённый интернет-магазин: покупатель оформляет заказы, заказ состоит из строк с товарами. Ниже — фрагмент логической диаграммы в нотации Mermaid.

```mermaid
erDiagram
  customer["Покупатель"] {
    INTEGER id PK                "Первичный ключ"
    STRING full_name             "ФИО"
    STRING email                 "Электронная почта"
  }

  order["Заказ"] {
    INTEGER id PK                "Первичный ключ"
    INTEGER customer_id FK       "Покупатель"
    DATE created_at              "Дата создания"
    STRING status                "Статус"
  }

  order_item["Детали заказа"] {
    INTEGER id PK                "Первичный ключ"
    INTEGER order_id FK          "Заказ"
    INTEGER product_id FK        "Товар"
    INTEGER quantity             "Количество"
    DECIMAL unit_price           "Цена"
  }

  product["Товар"] {
    INTEGER id PK                "Первичный ключ"
    STRING title                 "Название"
    DECIMAL price                "Текущая цена"
  }

  customer ||--o{ order : "оформляет"
  order ||--o{ order_item : "содержит"
  product ||--o{ order_item : "используется"
```

• Покупатель связан с Заказом как 1 — N: один человек может оформить много заказов, у каждого заказа фиксируется владелец через внешний ключ customer_id.
• Товар участвует в заказе через сущность Детали заказа. Так мы реализуем связь M — N и можем хранить количество и цену позиции, не дублируя данные товара целиком.
• Атрибуты внутри сущностей подсказывают, какие данные обязательно собирать на бизнес-уровне (email покупателя, статус заказа, цена позиции и т.д.) и какие ключи понадобятся.

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

Как перейти от ER-диаграммы к таблицам

1. Таблица = сущность. Для каждого прямоугольника на диаграмме создаём таблицу. Колонки берём из атрибутов.
2. Первичные ключи. Поле, помеченное как PK, становится первичным ключом (PRIMARY KEY). Если ключ не указан, его нужно выбрать (обычно автоинкрементное id).
3. Внешние ключи. Для каждого ребра 1 — N добавляем в таблицу «многие» колонку со ссылкой на «один»: например, orders.customer_id REFERENCES customers(id).
4. Связи M — N. Разрываем на две связи 1 — N через промежуточную таблицу (как order_items). В неё переносим дополнительные атрибуты (количество, цены).
5. Типы данных и ограничения. Для каждого поля выбираем подходящий тип SQLite/MySQL, добавляем NOT NULL, UNIQUE, значения по умолчанию и т.д.
6. Дополнительные параметры. На физическом уровне можно задать действия при удалении/обновлении (ON DELETE/UPDATE) и заранее продумать, какие поля потребуется ускорять каждым типом запросов (к этому вернёмся позже).

Как называть таблицы: единственное или множественное число

В реальных проектах часто спорят, как именовать таблицы: user или users, order или orders. Универсального стандарта нет, но полезно понимать аргументы сторон и придерживаться единого подхода внутри проекта.

• Единственное число (user, order). Такой стиль пришёл из ООП: таблицу воспринимают как «класс», а строки — как экземпляры. Плюсы — красиво сочетается с ORM, где модели называются в единственном числе. Минус — при чтении SQL иногда сложно сразу увидеть, что речь о наборе сущностей.
• Множественное число (users, orders). Более распространённый вариант в современной разработке: таблица хранит множество записей, а каждая строка описывает один объект. Плюсы — легче читать запросы (SELECT * FROM orders звучит естественно), а названия уже отражают множественность данных.

Какой бы стиль ни выбрали, главное — следовать ему последовательно. Смешение user и orders в одной схеме приводит к недопониманию, особенно если команды меняются или проект развивается годами. Перед стартом проекта обычно обговаривают схему именования и закрепляют её в гайдлайнах.

Инструменты для построения ERD

Mermaid.live

mermaid.live — текстовый редактор диаграмм. Пишем код на языке Mermaid (как в примерах лекции) и сразу видим визуализацию. Плюсы: диаграммы легко хранить в текстовом виде, есть подсветка синтаксиса, можно экспортировать в PNG/SVG.

drawDB

drawDB — графический конструктор, в котором сущности и связи рисуются мышкой. Работает в браузере и офлайн (есть версии для сборки при помощи Docker), поддерживает выгрузку диаграммы сразу в SQL (SQLite, MySQL и др.), а также импорт/экспорт проектов.

Для упражнений ниже можно выбрать любой инструмент: если нравится писать текстом — используйте Mermaid; если удобнее графически — drawDB.

Самопроверка

Практические задания

Задание 1. Концептуальная диаграмма

```mermaid
flowchart LR
  teacher[Преподаватель]
  course[Курс]
  enrollment[Запись на курс]
  student[Студент]

  teacher --- course
  course --- enrollment
  student --- enrollment
```

Задание 2. Логическая диаграмма

```mermaid
erDiagram
  teacher["Преподаватель"] {
    INTEGER id PK             "Первичный ключ"
    STRING full_name          "ФИО"
    STRING email              "Контакт"
  }

  course["Курс"] {
    INTEGER id PK             "Первичный ключ"
    STRING title              "Название"
    INTEGER teacher_id FK     "Ведущий преподаватель"
  }

  student["Студент"] {
    INTEGER id PK             "Первичный ключ"
    STRING full_name          "ФИО"
    STRING group_code         "Группа"
  }

  enrollment["Запись на курс"] {
    INTEGER id PK             "Первичный ключ"
    INTEGER student_id FK     "Студент"
    INTEGER course_id FK      "Курс"
    DATE enrolled_at          "Дата записи"
  }

  teacher ||--o{ course : "ведёт"
  course ||--o{ enrollment : "имеет записи"
  student ||--o{ enrollment : "посещает"
```

Задание 3. Физическая реализация и проверка

┌────┬─────┬──────────┬────────────┬────┬───────────┬───────────┬───────┐
│ id │ seq │  table   │    from    │ to │ on_update │ on_delete │ match │
├────┼─────┼──────────┼────────────┼────┼───────────┼───────────┼───────┤
│ 0  │ 0   │ teachers │ teacher_id │ id │ NO ACTION │ NO ACTION │ NONE  │
└────┴─────┴──────────┴────────────┴────┴───────────┴───────────┴───────┘
┌────┬─────┬──────────┬────────────┬────┬───────────┬───────────┬───────┐
│ id │ seq │  table   │    from    │ to │ on_update │ on_delete │ match │
├────┼─────┼──────────┼────────────┼────┼───────────┼───────────┼───────┤
│ 0  │ 0   │ courses  │ course_id  │ id │ NO ACTION │ NO ACTION │ NONE  │
│ 1  │ 0   │ students │ student_id │ id │ NO ACTION │ NO ACTION │ NONE  │
└────┴─────┴──────────┴────────────┴────┴───────────┴───────────┴───────┘

-- Ваш код можете писать тут

CREATE TABLE teachers (
  id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL,
  email TEXT
);

CREATE TABLE courses (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  teacher_id INTEGER NOT NULL REFERENCES teachers(id)
);

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL,
  group_code TEXT
);

CREATE TABLE enrollments (
  id INTEGER PRIMARY KEY,
  student_id INTEGER NOT NULL REFERENCES students(id),
  course_id INTEGER NOT NULL REFERENCES courses(id),
  enrolled_at TEXT DEFAULT (date('now'))
);

PRAGMA foreign_key_list('courses');
PRAGMA foreign_key_list('enrollments');