Логическая модель базы данных: что это и как её построить на примере

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

Разбираем, чем она отличается от концептуальной и физической, строим модель каталога курсов за семь шагов, переводим её в SQL и смотрим на пять частых ошибок.

Статью написал:
Ваня Буявец, продюсер, основатель Checkroi
Ваня Буявец
Основатель Checkroi, продюсер, эксперт в выборе онлайн-курсов
Все 3308 статей автора Подписаться на Телеграм-канал
Одобрено экспертом:
Наташа Буявец, основатель Checkroi, эксперт по онлайн-курсам
Наташа Буявец
Основательница Checkroi, продюсер Youtube-каналов, эксперт по онлайн-курсам
Все 3962 экспертных мнения Подписаться на Телеграм-канал
Обложка: Логическая модель базы данных: что это и как её построить на примере

Логическая модель базы данных описывает, какие данные хранит система и как они связаны: сущности, их атрибуты, ключи и связи между таблицами. Типов конкретной СУБД, индексов и настроек хранения в ней ещё нет. По сути это чертёж, который можно отдать разработчику и получить одинаковую базу хоть в PostgreSQL, хоть в MySQL.

Строят её после разговора с заказчиком и до первого CREATE TABLE. Ниже разберём, чем логическая модель отличается от концептуальной и физической, из каких элементов состоит, и построим её по шагам на примере каталога курсов. Если термин «СУБД» пока звучит туманно, начните с разбора, что такое система управления базами данных.

Что такое логическая модель данных

Рой рисует на доске схему из связанных прямоугольников

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

В логической модели обычно есть:

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

Чего в ней нет: индексов, разбиения на разделы, конкретных типов вроде VARCHAR(255) или JSONB, имён табличных пространств. Всё это решают позже, на физическом уровне.

В русских учебниках встречаются ещё два названия. Инфологическая (информационно-логическая) модель примерно соответствует концептуальной: она описывает предметную область словами и схемами, без оглядки на базу. Даталогическая модель ближе к логической: это та же структура, но уже в терминах выбранной модели данных, чаще всего реляционной. С реляционными базами работают на языке SQL, и логическая модель потом почти один в один превращается в его команды.

Концептуальная, логическая и физическая модель: чем отличаются

Три листа чертежей от наброска до детальной схемы

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

Уровень На какой вопрос отвечает Что в нём есть Кто читает
Концептуальная Что существует в предметной области Крупные сущности и связи между ними, без атрибутов или с главными Заказчик, аналитик
Логическая Какие данные храним и как они связаны Все сущности, атрибуты, первичные и внешние ключи, кратность, нормализация Аналитик, архитектор, разработчик
Физическая Как это лежит в конкретной СУБД Таблицы, типы данных, индексы, ограничения, разделы, права Разработчик, администратор БД

Простой тест. Если в схеме можно поменять PostgreSQL на MySQL и ничего не исправлять, перед вами логическая модель. Если на схеме стоят SERIAL, BTREE и TABLESPACE, это уже физическая.

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

CheckroiCheckroiПодборка курсов по PostgreSQL157 курсов • 27 школСравните цены, школы, программу и найдите выгодные предложения по обучениюСравнить→ Канал основателя Checkroi Вани Буявца3 800 человек читают мой Телеграм-канал про нейросетиСобрал промпты для Claude Code и ChatGPT, разборы ИИ-инструментов и лайфхаки по продвижению бизнеса в одном местеПрисоединиться

Сущности, атрибуты, ключи и связи

Сущности и экземпляры

Сущностью в БД называют класс объектов, о которых система хранит данные: «Курс», «Школа», «Студент». Конкретный курс «SQL с нуля» будет экземпляром сущности, в таблице он станет одной строкой.

Сущности бывают сильными и слабыми. Сильная существует сама по себе: школа есть, даже если у неё нет курсов. Слабая без родителя теряет смысл: запись студента на курс не существует без студента и без курса.

Атрибуты и ключи

Атрибут описывает свойство сущности: название курса, цена, email студента. На логическом уровне атрибуту достаточно имени, смысла и признака обязательности. Тип записывают общими словами: «целое число», «строка», «дата».

Сущности между собой связывают ключи:

  • первичный ключ (PK) однозначно определяет строку. В документации PostgreSQL сказано, что его значения должны быть «одновременно уникальными и отличными от NULL»;
  • внешний ключ (FK) ссылается на первичный ключ другой сущности. По той же документации он задаёт, что значения столбца должны соответствовать значениям в некоторой строке другой таблицы, и это называется ссылочной целостностью;
  • естественный ключ берут из самих данных, например email или ИНН. Суррогатный придумывают специально: student_id = 500. Email может смениться, суррогатный номер не меняется, поэтому первичным чаще делают суррогатный, а на email вешают уникальность.

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

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

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

Тип связи Пример Как реализуется в таблицах
Один к одному (1:1) Студент и его паспортные данные Внешний ключ с уникальностью или общий первичный ключ
Один ко многим (1:N) Школа и её курсы Внешний ключ на стороне «многих»: course.school_id
Многие ко многим (M:N) Курсы и категории Отдельная связующая сущность с двумя внешними ключами

В нотации «вороньей лапки» (Crow’s Foot) кратность рисуют значками на концах линии. В синтаксисе Mermaid, который удобно хранить прямо в репозитории, значки такие:

  • |o: ноль или один;
  • ||: ровно один;
  • }o: ноль или больше;
  • }|: один или больше.

Полная таблица есть в документации Mermaid по ER-диаграммам.

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

CheckroiCheckroiПодборка курсов по SQL430 курсов • 41 школаСравните цены, школы, программу и найдите выгодные предложения по обучениюСравнить→

Как построить логическую модель базы данных: пример каталога курсов

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

  1. Запишите требования предложениями. «Школа ведёт несколько курсов. Курс принадлежит одной школе. Курс может быть в нескольких категориях. Студент может записаться на несколько курсов, у записи есть дата».
  2. Выпишите существительные. Из предложений получаются кандидаты в сущности: школа, курс, категория, студент, запись. «Дата» уходит в атрибуты записи.
  3. Назначьте атрибуты. Школа: название. Курс: название, цена. Категория: название. Студент: email. Запись: дата записи.
  4. Выберите первичные ключи. Каждой сильной сущности даём суррогатный номер: school_id, course_id, category_id, student_id. Названию школы и email студента ставим уникальность.
  5. Проставьте связи и кратность. Школа и курс: один ко многим. Курс и категория: многие ко многим. Студент и курс: тоже многие ко многим.
  6. Разверните связи многие ко многим. Между курсом и категорией появляется сущность course_category, между студентом и курсом появляется enrollment. Её первичный ключ составной: пара внешних ключей. У записи есть свой атрибут, дата, поэтому запись становится полноценной сущностью со своими данными.
  7. Проверьте нормальные формы. В каждой ячейке одно значение, каждый атрибут зависит от всего ключа и только от него. Название школы не дублируется в таблице курсов, его достаём через school_id. Что такое первая, вторая и третья нормальные формы, разобрано в статье о нормализации данных.

Получилось шесть сущностей:

Сущность Атрибуты Первичный ключ Внешние ключи
school (школа) school_id, name school_id —
course (курс) course_id, school_id, title, price course_id school_id → school
category (категория) category_id, name category_id —
course_category course_id, category_id course_id + category_id course_id → course, category_id → category
student (студент) student_id, email student_id —
enrollment (запись) student_id, course_id, enrolled_on student_id + course_id student_id → student, course_id → course

Ту же логическую модель удобно хранить текстом. Код ниже вставляется в Mermaid Live или в любой Markdown-файл с поддержкой Mermaid, и редактор рисует ER-диаграмму:

erDiagram
    SCHOOL ||..o{ COURSE : offers
    COURSE ||--o{ COURSE_CATEGORY : tagged
    CATEGORY ||--o{ COURSE_CATEGORY : groups
    STUDENT ||--o{ ENROLLMENT : makes
    COURSE ||--o{ ENROLLMENT : receives
    SCHOOL {
        int school_id PK
        string name UK
    }
    COURSE {
        int course_id PK
        int school_id FK
        string title
        int price
    }
    CATEGORY {
        int category_id PK
        string name UK
    }
    COURSE_CATEGORY {
        int course_id PK, FK
        int category_id PK, FK
    }
    STUDENT {
        int student_id PK
        string email UK
    }
    ENROLLMENT {
        int student_id PK, FK
        int course_id PK, FK
        date enrolled_on
    }

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

Обратите внимание: типы здесь общие (int, string, date). Конкретная СУБД ещё не выбрана, и это правильно. Отдельный разбор нотаций ER-диаграмм, от Чена до IDEF1X, заслуживает своей статьи. Для логической модели хватает «вороньей лапки».

Переход к физической модели: SQL на примере

Когда модель согласована, её переводят в DDL-команды под конкретную СУБД. Что такое DDL и чем он отличается от DML, есть в разборе групп команд SQL. Ниже та же схема для SQLite:

CheckroiCheckroiПодборка курсов по SQLite24 курса • 10 школСравните цены, школы, программу и найдите выгодные предложения по обучениюСравнить→
PRAGMA foreign_keys = ON;

CREATE TABLE school (
  school_id INTEGER PRIMARY KEY,
  name      TEXT NOT NULL UNIQUE
);

CREATE TABLE course (
  course_id INTEGER PRIMARY KEY,
  school_id INTEGER NOT NULL REFERENCES school(school_id),
  title     TEXT NOT NULL,
  price     INTEGER CHECK (price >= 0)
);

CREATE TABLE category (
  category_id INTEGER PRIMARY KEY,
  name        TEXT NOT NULL UNIQUE
);

CREATE TABLE course_category (
  course_id   INTEGER REFERENCES course(course_id),
  category_id INTEGER REFERENCES category(category_id),
  PRIMARY KEY (course_id, category_id)
);

CREATE TABLE student (
  student_id INTEGER PRIMARY KEY,
  email      TEXT NOT NULL UNIQUE
);

CREATE TABLE enrollment (
  student_id  INTEGER REFERENCES student(student_id),
  course_id   INTEGER REFERENCES course(course_id),
  enrolled_on TEXT NOT NULL,
  PRIMARY KEY (student_id, course_id)
);

Добавим школу, курс, две категории и запросим курс вместе со школой и категориями. Как работают такие соединения, рассказано в статье про JOIN в SQL.

SELECT c.title, s.name AS school, GROUP_CONCAT(cat.name, ', ') AS categories
FROM course c
JOIN school s ON s.school_id = c.school_id
JOIN course_category cc ON cc.course_id = c.course_id
JOIN category cat ON cat.category_id = cc.category_id
GROUP BY c.course_id;

Вывод sqlite3 3.51 в режиме -header -column:

title       school   categories
----------  -------  ----------------------
SQL с нуля  Школа А  Базы данных, Аналитика

Где физическая модель расходится с логической

На этом шаге всплывают особенности СУБД, которых в логической модели нет.

  • SQLite не проверяет внешние ключи по умолчанию. В документации SQLite сказано, что ограничения внешних ключей отключены по умолчанию и включаются отдельно для каждого соединения командой PRAGMA foreign_keys = ON.
  • PostgreSQL не создаёт индекс по внешнему ключу сам. Индекс по первичному ключу появляется автоматически, а по ссылающимся столбцам, как сказано в документации PostgreSQL, «объявление внешнего ключа не создаёт автоматически индекс». Его добавляют руками, если по этому столбцу часто ищут или удаляют родителя.
  • Типы уточняются. «Строка» превращается в TEXT в SQLite или varchar(n) в MySQL, «дата» в DATE или текст в формате ISO. Какие типы есть в разных СУБД, сведено в таблице типов данных SQL.

Вот что происходит с курсом, у которого нет школы. Откроем базу заново, без PRAGMA, и выполним скрипт:

INSERT INTO course VALUES (12, 99, 'Курс без школы', 1000);
SELECT course_id, school_id, title FROM course WHERE course_id = 12;

SQLite молча сохраняет строку со ссылкой на несуществующую школу 99:

12|99|Курс без школы

Теперь включим проверку первой строкой скрипта:

PRAGMA foreign_keys = ON;
INSERT INTO course VALUES (13, 99, 'Курс без школы', 1000);

Вставка падает на второй строке:

Runtime error near line 2: FOREIGN KEY constraint failed (19)

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

Runtime error near line 1: UNIQUE constraint failed: enrollment.student_id, enrollment.course_id (19)

Логическая модель здесь не изменилась, она с самого начала требовала у каждого курса школу. Изменилось только то, следит ли за этим правилом СУБД. Поэтому физическую модель проверяют тестовой вставкой «плохих» данных, а не только чтением схемы.

Создать такие таблицы в PostgreSQL можно и без SQL, через графический мастер: пошагово это показано в инструкции по созданию таблицы в pgAdmin. А общие команды для MySQL, PostgreSQL и SQL Server собраны в статье о создании базы данных в SQL.

Частые ошибки при проектировании

Рой радуется, что нашёл ошибку в схеме до запуска

Почти все эти ошибки видны на схеме, ещё до первого CREATE TABLE. Позже каждая обойдётся миграцией с переносом данных.

  1. Список в одном поле. Симптом: в таблице курсов поле categories со значением Базы данных, Аналитика. Причина: связь многие ко многим не развернули. Решение: отдельная сущность course_category, как в примере выше.
  2. Вычисляемое значение вместо исходного. Симптом: у студента хранится возраст, и через год он неверен. Причина: в модель попало производное значение. Решение: храните дату рождения, возраст считайте запросом.
  3. Дубли атрибутов в разных сущностях. Симптом: название школы лежит и в school, и в course, и однажды они расходятся. Причина: модель не довели до третьей нормальной формы. Решение: оставьте атрибут в одной сущности, а в другой оставьте внешний ключ.
  4. Физика раньше времени. Симптом: на логической схеме уже стоят VARCHAR(255) и индексы, а СУБД ещё не выбрана. Причина: уровни смешали. Решение: на логическом уровне оставьте общие типы и правила, конкретику переносите в физическую модель.
  5. Связи «на словах». Симптом: в таблице есть school_id, но внешнего ключа нет, «проверим в коде». Причина: правило из модели не перенесли в базу. Решение: объявите внешний ключ, иначе база примет курс без школы, как SQLite выше.

Ещё одна мелочь экономит часы споров в команде: договоритесь об именах заранее. Единственное или множественное число в названиях таблиц, snake_case или camelCase, id или course_id. Выбрать можно любой вариант, лишь бы он был одинаковым во всей модели.

Ваня БуявецКанал основателя Checkroi Вани БуявцаЗабирайте промпты и обучение по нейросетям в моём Телеграм-каналеБольше 3 800 человек уже применяют Claude Code, ChatGPT и другие нейросети в работе, учёбе, бизнесе и жизниПерейти в канал

В какой программе рисовать модель

Рисовать модель можно хоть на бумаге, но в редакторе схему проще менять и показывать команде, а часть инструментов ещё и выгружает её в SQL.

Инструмент Что умеет Когда удобен
Mermaid Live Рисует ER-диаграмму из текстового описания Модель хранится рядом с кодом и правится через Git
drawDB Редактор схем БД в браузере, экспорт в SQL Быстро набросать модель без установки программ
draw.io Универсальный редактор диаграмм с фигурами для ER Схема нужна в документации или презентации
pgModeler Моделирование под PostgreSQL с генерацией DDL Проект точно будет на PostgreSQL
DBeaver ER-диаграммы по существующей базе Нужно восстановить модель уже работающей базы

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

Сама идея описывать данные через сущности и связи восходит к статье Питера Чена «The Entity-Relationship Model — Toward a Unified View of Data» 1976 года. С тех пор нотации поменялись, а принцип остался прежним.

Где освоить проектирование баз данных и SQL

Логические модели строят системные аналитики, бэкенд-разработчики и архитекторы баз данных. Рядом с этим навыком всегда идут SQL, оптимизация запросов и администрирование. Если хочется пройти этот путь с наставником и практикой на настоящих базах, вот курсы по SQL из нашего каталога:

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

КурсШколаСтоимость со скидкойВ рассрочкуДлитель­ностьОбзор курса от Checkroi
Нейросети для рабочих задач
Перейти на сайт курса
SkillboxSkillbox31 290 ₽ - 1 месяцОбзор курса
SQL для анализа данных
Перейти на сайт курса
SkillboxSkillbox50 865 ₽6203 ₽/мес.2 месяцаОбзор курса
SQL-разработчик: тариф PRO
Перейти на сайт курса
Академия ЭдюсонЭдюсон89 900 ₽7491 ₽/мес.2 месяцаОбзор курса
SQL-разработчик: тариф Базовый
Перейти на сайт курса
Академия ЭдюсонЭдюсон79 900 ₽3329 ₽/мес.3 месяцаОбзор курса
Data-Driven подход в разработке продукта: SQL и Yandex DataLens
Перейти на сайт курса
ProductStarProductStar29 990 ₽2999 ₽/мес.1 месяцОбзор курса

Больше программ — в полном каталоге курсов по SQL

Все курсы по теме собраны в разделе курсов по SQL, там их можно сравнить по цене, длительности и формату.

Часто задаваемые вопросы

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

Это схема, на которой видно, какие данные хранит система и как они связаны: сущности, их атрибуты, первичные и внешние ключи, связи с кратностью. Типов конкретной СУБД и индексов в ней ещё нет, поэтому одну и ту же модель можно реализовать в PostgreSQL, MySQL или SQLite.

Чем концептуальная модель отличается от логической?

Концептуальная показывает крупные сущности предметной области и связи между ними, часто без атрибутов. Логическая добавляет все атрибуты, первичные и внешние ключи, кратность связей и разворачивает связи «многие ко многим» в отдельные сущности.

Чем логическая модель отличается от физической?

Логическая не зависит от СУБД: в ней общие типы вроде «строка» и «дата». Физическая описывает реализацию в конкретной СУБД: типы данных, индексы, ограничения, разделы. Если схему можно перенести с PostgreSQL на MySQL без правок, это логическая модель.

Что такое сущность и атрибут в базе данных?

Сущностью называют класс объектов, о которых хранятся данные, например «Курс» или «Студент». Атрибут описывает свойство сущности: название курса, email студента. Конкретный курс будет экземпляром сущности, в таблице это одна строка.

Что такое даталогическая и инфологическая модель?

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

В какой программе построить логическую модель базы данных?

Для быстрого наброска подойдут drawDB и draw.io в браузере, для модели рядом с кодом удобен Mermaid. Под PostgreSQL есть pgModeler с генерацией DDL, а DBeaver строит ER-диаграмму по уже существующей базе.

Читайте Checkroi первым в Google
Добавьте Checkroi в избранные источники — и наши разборы курсов и обзоры школ будут показываться выше в вашей выдаче Google.
Оставить комментарий
0 комментариев
Форма комментария

Оставьте комментарий

Напишите, что думаете. Нам важно ваше мнение!