Проектирование схемы и создание таблиц

Проектирование схемы и создание таблиц

В предыдущем уроке вы сформулировали задачу и выделили сущности. Теперь переводим концептуальную модель в SQL. Это центральный урок проекта: полная DDL-схема блог-платформы с объяснением каждого решения.

Порядок создания таблиц

Помните правило: родительская таблица должна существовать до дочерней. Наш порядок:

  1. users — не зависит ни от кого
  2. categories — не зависит ни от кого
  3. tags — не зависит ни от кого
  4. posts — зависит от users и categories
  5. post_tags — зависит от posts и tags
  6. comments — зависит от posts и users
  7. likes — зависит от posts и users

Таблица пользователей

CREATE TABLE users (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username   VARCHAR(50)  UNIQUE NOT NULL,
    email      VARCHAR(255) UNIQUE NOT NULL,
    bio        TEXT,
    avatar_url TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE users IS 'Зарегистрированные пользователи: авторы и читатели';
COMMENT ON COLUMN users.bio IS 'Краткая биография, необязательна';

BIGINT GENERATED ALWAYS AS IDENTITY — современный SQL-стандарт вместо SERIAL. Пользователей может быть много — BIGINT (8 байт) не переполнится. updated_at обновляется при каждом изменении через триггер или в приложении.

Таблица категорий и тегов

CREATE TABLE categories (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(100) UNIQUE NOT NULL,
    slug       VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE tags (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL
);

slug — URL-безопасная версия имени: technology, machine-learning. Уникальный, потому что URL должен быть однозначным. Для tags нет created_at — теги обычно долгоживущие объекты, время создания неважно.

Таблица постов

CREATE TABLE posts (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    author_id    BIGINT       NOT NULL REFERENCES users(id)      ON DELETE CASCADE,
    category_id  INTEGER      NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
    title        TEXT         NOT NULL,
    body         TEXT         NOT NULL,
    slug         VARCHAR(200) UNIQUE NOT NULL,
    published    BOOLEAN      NOT NULL DEFAULT FALSE,
    published_at TIMESTAMPTZ,    -- NULL пока черновик
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    CONSTRAINT chk_published_at CHECK (
        (published = FALSE AND published_at IS NULL) OR
        (published = TRUE  AND published_at IS NOT NULL)
    )
);

COMMENT ON COLUMN posts.published_at IS 'NULL для черновиков; заполняется при публикации';

Ключевые решения:

  • ON DELETE CASCADE для author_id — удаление автора удаляет его посты
  • ON DELETE RESTRICT для category_id — нельзя удалить категорию с постами
  • CHECK гарантирует консистентность: опубликованный пост имеет published_at, черновик — нет
  • slug — уникальный URL-путь поста: /posts/why-sql-matters

Промежуточная таблица постов и тегов

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  INTEGER NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

Составной PRIMARY KEY предотвращает дубли. Оба CASCADE: удаление поста удаляет его теги, удаление тега удаляет связи (не сами посты).

Таблица комментариев

CREATE TABLE comments (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    post_id    BIGINT  NOT NULL REFERENCES posts(id)  ON DELETE CASCADE,
    author_id  BIGINT  NOT NULL REFERENCES users(id)  ON DELETE CASCADE,
    content    TEXT    NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT chk_comment_content CHECK (length(trim(content)) > 0)
);

CHECK (length(trim(content)) > 0) запрещает пустые комментарии (пробелы обрезаются перед проверкой). Оба FK с CASCADE — удаление поста или пользователя удаляет комментарии.

Таблица лайков

CREATE TABLE likes (
    post_id    BIGINT NOT NULL REFERENCES posts(id)  ON DELETE CASCADE,
    user_id    BIGINT NOT NULL REFERENCES users(id)  ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (post_id, user_id)
);

Составной PRIMARY KEY — гарантия: один пользователь не лайкнет пост дважды. Нет суррогатного id — в промежуточных таблицах он обычно не нужен.

Индексы

-- FK без автоматических индексов (все FK-колонки)
CREATE INDEX idx_posts_author_id     ON posts(author_id);
CREATE INDEX idx_posts_category_id   ON posts(category_id);
CREATE INDEX idx_comments_post_id    ON comments(post_id);
CREATE INDEX idx_comments_author_id  ON comments(author_id);
CREATE INDEX idx_likes_post_id       ON likes(post_id);
CREATE INDEX idx_post_tags_tag_id    ON post_tags(tag_id);

-- Составной: часто ищем опубликованные посты в хронологии
CREATE INDEX idx_posts_published_at ON posts(published, published_at DESC)
    WHERE published = TRUE;

-- Полнотекстовый поиск по заголовкам
CREATE INDEX idx_posts_title_gin ON posts USING GIN(to_tsvector('russian', title));

Частичный индекс WHERE published = TRUE — меньше размера и быстрее, чем полный, если черновиков много.

Тонкости выбора типов данных

Разберём выбор типов для каждой таблицы подробнее.

id колонки: BIGINT GENERATED AS IDENTITY vs SERIAL: BIGINT (8 байт) против INTEGER (4 байта) — для users и posts используем BIGINT, потому что платформа может вырасти. Для categories и tags — SERIAL (INTEGER): их несколько сотен максимум.

VARCHAR(n) vs TEXT:

  • username VARCHAR(50) — бизнес-ограничение: имя пользователя не может быть длиннее 50 символов
  • title TEXT — нет естественного ограничения на заголовок поста
  • slug VARCHAR(200) — URL-сегмент, ограничен практически; TEXT тоже допустим

Почему NOT NULL DEFAULT NOW() для created_at, а не просто DEFAULT NOW(): NOT NULL — явная гарантия, что запись никогда не создаётся без временной метки. DEFAULT NOW() удобство — но если не указать NOT NULL, приложение теоретически может вставить NULL. Комбинация — лучшая практика.

published_at TIMESTAMPTZ без NOT NULL: Поле намеренно nullable — NULL означает «не опубликовано». CHECK ограничение гарантирует, что NULL здесь — не ошибка, а семантика черновика.

Управление публикацией: паттерн «publish action»

Обратите внимание на дизайн публикации:

-- При публикации поста приложение выполняет:
UPDATE posts
SET published = TRUE,
    published_at = NOW(),
    updated_at = NOW()
WHERE id = 42 AND published = FALSE;  -- идемпотентно

-- При снятии с публикации:
UPDATE posts
SET published = FALSE,
    published_at = NULL,
    updated_at = NOW()
WHERE id = 42;

CHECK-ограничение (published = TRUE AND published_at IS NOT NULL) OR (published = FALSE AND published_at IS NULL) гарантирует, что эти два поля всегда согласованы. Если приложение случайно установит published = TRUE без published_at — база откажет.

Это пример использования CHECK для бизнес-правила, которое нельзя выразить одним ограничением на колонке.

Проверка схемы

После создания таблиц — убедиться, что всё корректно:

-- Посмотреть структуру таблицы posts
\d+ posts

-- Посмотреть все FK
SELECT
    tc.table_name, kcu.column_name,
    ccu.table_name AS references_table,
    rc.delete_rule
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON kcu.constraint_name = tc.constraint_name
JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name
JOIN information_schema.referential_constraints rc ON rc.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
ORDER BY tc.table_name;

-- Проверить все индексы
SELECT tablename, indexname FROM pg_indexes WHERE schemaname = 'public' ORDER BY tablename;

Полный скрипт создания схемы

Собираем всё в один файл миграции — стандартный подход в реальных проектах:

-- V1__create_blog_schema.sql

CREATE TABLE users (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username   VARCHAR(50)  UNIQUE NOT NULL,
    email      VARCHAR(255) UNIQUE NOT NULL,
    bio        TEXT,
    avatar_url TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE categories (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE tags (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE posts (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    author_id    BIGINT       NOT NULL REFERENCES users(id)      ON DELETE CASCADE,
    category_id  INTEGER      NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
    title        TEXT         NOT NULL,
    body         TEXT         NOT NULL,
    slug         VARCHAR(200) UNIQUE NOT NULL,
    published    BOOLEAN      NOT NULL DEFAULT FALSE,
    published_at TIMESTAMPTZ,
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    CONSTRAINT chk_published_at CHECK (
        (published = FALSE AND published_at IS NULL) OR
        (published = TRUE  AND published_at IS NOT NULL)
    )
);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  INTEGER NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

CREATE TABLE comments (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    post_id    BIGINT NOT NULL REFERENCES posts(id)  ON DELETE CASCADE,
    author_id  BIGINT NOT NULL REFERENCES users(id)  ON DELETE CASCADE,
    content    TEXT   NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT chk_comment_content CHECK (length(trim(content)) > 0)
);

CREATE TABLE likes (
    post_id    BIGINT NOT NULL REFERENCES posts(id)  ON DELETE CASCADE,
    user_id    BIGINT NOT NULL REFERENCES users(id)  ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (post_id, user_id)
);

-- Индексы на FK
CREATE INDEX idx_posts_author_id    ON posts(author_id);
CREATE INDEX idx_posts_category_id  ON posts(category_id);
CREATE INDEX idx_comments_post_id   ON comments(post_id);
CREATE INDEX idx_comments_author_id ON comments(author_id);
CREATE INDEX idx_likes_post_id      ON likes(post_id);
CREATE INDEX idx_post_tags_tag_id   ON post_tags(tag_id);

-- Дополнительные индексы
CREATE INDEX idx_posts_published_at ON posts(published_at DESC)
    WHERE published = TRUE;

Этот скрипт можно запустить в psql командой \i V1__create_blog_schema.sql или через любой инструмент миграций.

Итоговая схема

Семь таблиц, 6 связей, 12 индексов, 2 CHECK-ограничения. Схема обеспечивает:

  • Ссылочную целостность через FK и правильные ON DELETE
  • Бизнес-правила через CHECK
  • Производительность через индексы
  • Документацию через COMMENT ON

Это законченная, production-ориентированная схема, готовая к работе с реальными данными.

Что дальше

Схема создана. Следующий урок — заполнение тестовыми данными и выполнение базовых запросов: проверим, что схема работает так, как задумано.

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

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

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