Модель данных учебного приложения

Каталог учебного сайта сначала можно хранить в одном JSON-файле: название курса, тема и список уроков лежат рядом. Когда редактор меняет тему, добавляет главы и готовит публикацию, появляются вопросы согласованности. Можно ли сослаться на отсутствующий курс? Где проверить, что два урока не занимают одинаковое место? В этой серии перенесём небольшой каталог в PostgreSQL и будем объяснять каждое решение на одних данных.

Учебный пример вдохновлён расширением ProfessorWeb, но не является копией его рабочей базы. У статического сайта статьи по-прежнему могут храниться в Markdown. Здесь рассматриваем отдельное веб-приложение: редакторский каталог курсов, которому нужны связанные записи и запросы. Условные цены и даты придуманы для обучения, они ничего не говорят о коммерческих условиях сайта.

В серии зафиксирован PostgreSQL 17.11. Исходники находятся в архиве каталога, папка catalog-db/lesson-01. SQL и программы пока не запускались: далее приводим ожидаемые результаты. Каждый снимок самостоятельный; его reset.sql удаляет только учебную схему catalog, но перед использованием требует отдельной локальной базы по README.

От карточки к сущностям

Представьте карточку «JavaScript в браузере». Она имеет название, публичный код js-browser, тему «Фронтенд» и запланированные двадцать уроков. В начальном JSON тема может повторяться внутри каждой карточки. Это удобно читать, однако при переименовании раздела придётся изменить несколько объектов. Одновременно один объект может получить новое название, а другой сохранить старое. База не должна угадывать, какое из них правильное.

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

Сущность — это объект предметной области, а строка таблицы — его конкретное представление в базе. Слово «курс» обозначает тип объектов, строка с id = 2 обозначает один курс. Идентификатор помогает отличить его от другого курса с похожим названием. Название допускает редакторское изменение, поэтому связывать таблицы только по нему неудобно. После исправления опечатки уроки всё ещё должны принадлежать тому же объекту.

Пока зафиксируем отношения в текстовом виде:

topics: одна тема → несколько courses
courses: один курс → несколько lessons
lessons: одна позиция внутри конкретного курса

Слово «несколько» здесь включает ноль. Тема «Дизайн» существует, хотя в неё пока не добавлены курсы. Курс может появиться раньше своих уроков. Если модель требует обязательного наличия хотя бы одного дочернего объекта, это отдельное правило, которое нельзя вывести только из стрелки на схеме.

Обратите внимание на два количества. planned_lessons означает размер запланированной серии. Число фактически записанных строк lessons означает уже заведённые главы. В исходных данных у каждого курса только два учебных урока, хотя план содержит десять, двенадцать или двадцать глав. Если интерфейс подпишет план как «готово», ошибка возникнет на уровне смысла, даже когда SQL выполнен правильно.

Ключи и связи

В таблицах используется числовой id и отдельный текстовый slug. Первый служит внутренним ключом; второй удобен для публичной ссылки или поиска редактором. Они решают разные задачи. Само наличие slug не сохраняет старые адреса сайта: для адресов нужен отдельный договор маршрутов и при необходимости перенаправлений. Поэтому прежние URL ProfessorWeb не заменяются автоматически идентификаторами учебного каталога.

Дочерняя запись хранит ключ родителя, а не копию его названия. Курс JavaScript содержит topic_id = 1; первая тема соответствует фронтенду. Первые строки исходного набора выглядят так:

topics:  1 frontend «Фронтенд»
courses: 2 js-browser, topic_id=1, planned_lessons=20
lessons: course_id=2, position=1, «Модель интерфейса»

Позиция урока не заменяет его идентификатор. В другом курсе также есть первая глава. Пара (course_id, position) должна быть уникальна, но отдельное значение position = 1 повторяется совершенно законно. Это различие помогает выбрать ограничение позже: запрещать одинаковую позицию во всей библиотеке было бы слишком сильным правилом.

В полном снимке ключи уже оформлены ограничениями. Начальные главы будут раскрывать готовую схему по частям, а не заставлять вас вручную достраивать её в единственном порядке. Благодаря этому можно открыть нужный урок отдельно и получить тот же набор. Цена такого удобства — необходимость прочитать файл восстановления и понять, что он создаёт таблицы сразу с итоговыми правилами.

Наблюдаем модель через запрос

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

SELECT c.slug, l.position, l.title
FROM catalog.courses AS c
JOIN catalog.lessons AS l ON l.course_id = c.id
WHERE c.slug = 'js-browser'
ORDER BY l.position;

Ожидаются позиции один и два, с названиями «Модель интерфейса» и «События». Условие соединения связывает строки по ключу. Условие отбора выбирает курс по публичному коду. Сортировка задаёт порядок обучения; без неё наличие правильных строк ещё не означает правильного оглавления. Подробный разбор соединений будет в существующем уроке 10.

Теперь мысленно изменим название темы на «Клиентская разработка». Если название хранится только в topics, запрос карточек через связь увидит новое имя для всех связанных курсов. Их идентификаторы и принадлежность сохранятся. Если вместо этого перенести курс в другую тему, меняется его topic_id; оба действия похожи в интерфейсе, но имеют разный смысл в данных.

Урок также может быть запланирован без текста: body допускает отсутствующее значение. При этом название, курс и позиция остаются обязательными. Такой объект нужен редактору как заготовка, но не обязан появляться на публичном сайте. Мы намеренно не приравниваем наличие строки к готовности публикации. В каталоге курса есть статус, а правило вывода должно использовать его явно.

Завершая модель, спросите себя, какое изменение должно произойти при удалении родителя. Удаление курса убирает его учебные уроки, поскольку в этом примере вне курса они не существуют. Удаление темы с курсами запрещается, чтобы случайно не потерять целую ветку. Это выбранная политика учебного приложения. Архивирование материалов, восстановление и сохранение публичных адресов реального сайта потребуют более подробного решения, которое нельзя заменить одним каскадом.

Граница учебного каталога

Строка урока в этой базе не равна файлу Markdown на диске. Пока body представляет небольшой учебный текст, чтобы показать отсутствие значения и связь с родителем. Если редакторское приложение будет собирать статический сайт, между базой и публикацией появится отдельный экспорт. Он должен определить адрес, шаблон и набор опубликованных материалов, а не просто выгрузить все строки без различия состояний.

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

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

В старом примере ASP.NET Gamestore также начинается работа с модели. Здесь сохраняем этот порядок объяснения, но не переносим классы и хранилище автоматически в SQL. Полученная модель уже позволяет отличить объект, его ключ, положение в оглавлении и состояние публикации. Дальше подключимся именно к учебной базе, в которой эти правила будут существовать.

Оглавление курса.