Постановка задачи: блог с авторами и комментариями

Постановка задачи: блог с авторами и комментариями

Добро пожаловать в финальный модуль. Здесь вы применяете весь материал курса: DDL для создания схемы, DML для работы с данными, DQL для сложных запросов. Четыре урока — один полноценный проект от постановки задачи до аналитических отчётов.

Проект: платформа для блогов

Задача — спроектировать базу данных для блог-платформы. Пользователи регистрируются, пишут посты, комментируют чужие записи, ставят лайки. Редакторы могут публиковать и снимать с публикации посты. Нужна аналитика: топ авторов, самые популярные посты, активность за период.

Функциональные требования:

  1. Пользователи могут регистрироваться и иметь профили
  2. Авторы пишут посты с заголовком, телом, категорией и тегами
  3. Посты могут быть черновиками или опубликованными
  4. Читатели оставляют комментарии к постам
  5. Читатели могут лайкнуть пост (один раз)
  6. Аналитика: количество постов и комментариев по авторам, лайки постов

Нефункциональные требования:

  • Все временные метки с часовым поясом
  • Удаление автора удаляет его посты (каскад)
  • Удаление поста удаляет комментарии и лайки
  • Нельзя удалить категорию с постами

Выделение сущностей

Из требований выделяем сущности — будущие таблицы:

СущностьАтрибутыОткуда
usersid, username, email, bio, created_at«Пользователи регистрируются»
categoriesid, name, slug«С категорией»
tagsid, name, slug«С тегами»
postsid, author_id, category_id, title, body, published, created_at«Авторы пишут посты»
post_tagspost_id, tag_idM:N между posts и tags
commentsid, post_id, author_id, content, created_at«Комментируют»
likespost_id, user_id, created_at«Лайкают пост»

Определение связей

users ──────────< posts >──────── categories
  |                 |
  |                 |──────< comments >────── users
  |                 |
  |                 |──────< likes >────── users
  |
  users >──── post_tags <──── posts
              (tags)
  • users → posts: 1:N (автор пишет много постов)
  • categories → posts: 1:N (категория содержит много постов)
  • posts → comments: 1:N (пост имеет много комментариев)
  • users → comments: 1:N (пользователь пишет много комментариев)
  • posts ↔ tags: M:N через post_tags
  • posts ↔ users (лайки): M:N через likes

От требований к сущностям: как читать задачу

Умение «прочитать» требования и выделить сущности — ключевой навык. Несколько техник:

Ищите существительные: «пользователи регистрируются», «авторы пишут посты», «посты имеют категории» — users, posts, categories — это существительные и кандидаты на сущности.

Ищите глаголы: «пишут», «комментируют», «лайкают» — это связи между сущностями. «Пользователь лайкает пост» — связь M:N через likes.

Ищите «один vs много»: «у поста одна категория» → posts.category_id (FK); «у поста много тегов» → промежуточная таблица post_tags.

Ищите временные данные: «аналитика за период», «опубликован» vs «черновик» → поля created_at, published_at, published.

Применим к нашей задаче:

«Пользователи регистрируются» → таблица users
«Авторы пишут посты» → таблица posts, users.id → posts.author_id (1:N)
«Посты с категорией» → таблица categories, categories.id → posts.category_id (1:N)
«Посты с тегами» → таблица tags, M:N через post_tags
«Читатели комментируют» → таблица comments, FK на posts и users
«Читатели лайкают» → таблица likes, FK на posts и users, уникальная пара
«Один раз» (лайк) → UNIQUE (post_id, user_id) в likes

Поведение при удалении

СобытиеЧто происходит
Удаление usersCASCADE → posts, comments, likes
Удаление postsCASCADE → comments, likes, post_tags
Удаление categoriesRESTRICT → нельзя, если есть посты
Удаление tagsCASCADE → post_tags

Уточнение ограничений

Пройдёмся по каждой сущности и определим ограничения:

users:

  • username: уникальный, обязательный, до 50 символов
  • email: уникальный, обязательный
  • bio: необязательный текст
  • created_at: обязательный, по умолчанию NOW()

categories:

  • name: уникальный, обязательный
  • slug: уникальный URL-фрагмент, например technology, lifestyle

posts:

  • title: обязательный
  • body: обязательный
  • published: булево, по умолчанию FALSE (черновик)
  • published_at: дата публикации (NULL пока черновик)
  • author_id: NOT NULL, ссылка на users
  • category_id: NOT NULL, ссылка на categories

comments:

  • content: обязательный, не пустая строка
  • author_id и post_id: NOT NULL

likes:

  • (post_id, user_id): составной уникальный ключ — один лайк от пользователя на пост

Какие запросы должны быть быстрыми

Определяем индексы на основе ожидаемых запросов:

- Все опубликованные посты: WHERE published = TRUE ORDER BY created_at
- Посты автора: WHERE author_id = ?
- Посты категории: WHERE category_id = ?
- Комментарии к посту: WHERE post_id = ?
- Лайки поста: COUNT(*) WHERE post_id = ?
- Посты с тегом: JOIN post_tags WHERE tag_id = ?

На основе этого анализа:

  • Индекс на posts(author_id)
  • Индекс на posts(category_id)
  • Индекс на posts(published, created_at) — составной для сортировки опубликованных
  • Индекс на comments(post_id)
  • Индекс на post_tags(tag_id)
  • Индекс на likes(post_id)

Все FK + дополнительные индексы для частых запросов.

Схема данных (концептуально)

[categories] ──1:N──< [posts] >──M:N─── [tags]
                         |
              ──1:N──< [comments]
                         |
              ──M:N──< [likes]
                         |
                      [users] (авторы и читатели)

Интересный момент: users выступает в двух ролях — автор постов и комментариев (author_id), а также читатель, ставящий лайки (user_id в likes). В одной таблице — это правильное решение для системы, где пользователь и автор — одно лицо.

Часто возникающие вопросы при анализе требований

Вопрос 1: Хранить автора в posts или в comments?

И там, и там — это разные люди. Автор поста (author_id в posts) и автор комментария (author_id в comments) — оба ссылаются на users, но это разные роли. В нашей системе пользователь может быть и автором, и читателем — одна таблица users обслуживает обе роли.

Вопрос 2: Хранить slug в categories и tags?

slug — URL-безопасная версия названия: technology, machine-learning. Отдельная колонка (уникальная, NOT NULL) позволяет строить URL вида /category/technology без обращения к id. Это стандартный паттерн для SEO-дружественных приложений.

Вопрос 3: Нужна ли published_at отдельно от created_at?

Да. Пост создаётся как черновик (created_at — время создания), потом публикуется (published_at — время публикации). Это разные события. Для хронологии читателей нужен published_at.

Вопрос 4: Мягкое удаление (soft delete) или жёсткое?

Для образовательного проекта — жёсткое удаление с CASCADE. В production-системах часто используют мягкое удаление: добавляют deleted_at TIMESTAMPTZ, и вместо DELETE устанавливают дату удаления. Данные остаются в базе, но фильтруются из всех запросов условием WHERE deleted_at IS NULL.

Жёсткое удаление: DELETE FROM posts WHERE id = 1
Мягкое удаление:  UPDATE posts SET deleted_at = NOW() WHERE id = 1
                  -- Все запросы добавляют: AND deleted_at IS NULL

Для нашего проекта выбираем жёсткое — проще и нагляднее для обучения.

Что проверит хорошая схема

Хорошая схема должна не допускать:

  • Поста без автора (author_id NOT NULL)
  • Поста без категории (category_id NOT NULL)
  • Двух лайков от одного пользователя на один пост (UNIQUE (post_id, user_id))
  • Пустого комментария (CHECK (length(content) > 0))
  • Удаления категории при наличии постов (ON DELETE RESTRICT)

Эти ограничения — первый барьер целостности данных. Второй барьер — валидация в приложении.

Документирование задачи перед реализацией

Прежде чем писать первый CREATE TABLE, полезно задокументировать решения:

Список таблиц с назначением:

users          — зарегистрированные пользователи (авторы и читатели)
categories     — рубрики блога (технологии, стиль жизни, ...)
tags           — теги постов (sql, postgresql, ...)
posts          — статьи, с автором и категорией
post_tags      — связь постов с тегами (M:N)
comments       — комментарии к постам
likes          — лайки постов (один пользователь — один лайк)

Открытые вопросы (то, что нужно уточнить у стейкхолдера):

  • Могут ли пользователи комментировать без авторизации (нет, по условию)
  • Нужна ли история редактирования поста? (упрощаем: нет)
  • Можно ли лайкнуть собственный пост? (упрощаем: да, не запрещаем)
  • Нужны ли уведомления? (выходит за рамки схемы в этом проекте)

Документирование открытых вопросов — признак профессионального подхода. Лучше уточнить до реализации, чем переделывать схему после.

Что дальше

Задача сформулирована, сущности выделены, связи определены, ограничения описаны. Следующий урок — реализация: полная DDL-схема с CREATE TABLE, индексами и ограничениями.

Попробуйте интерактивную версию

Практические задачи, квизы и AI-наставник — бесплатный старт без карты

Перейти к практике