Структура базы данных для доски объявлений (BBS) на PHP

Раздел: Веб-разработка -> Базы данных для форума

Проектирование таблиц для BBS на PHP

Основное эффективное решение

Для доски объявлений оптимальна нормализованная схема с разделением на таблицы: users, categories, threads, posts. Это обеспечивает целостность данных, удобство запросов и масштабирование.

Как создать базовые таблицы для форума с категориями и темами?

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT
);

CREATE TABLE threads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  user_id INT NOT NULL,
  title VARCHAR(200) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_category (category_id),
  INDEX idx_user (user_id)
);

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  thread_id INT NOT NULL,
  user_id INT NOT NULL,
  parent_id INT DEFAULT NULL,
  content TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (thread_id) REFERENCES threads(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (parent_id) REFERENCES posts(id) ON DELETE SET NULL,
  INDEX idx_thread (thread_id),
  INDEX idx_parent (parent_id)
);

Bbs php table (таблицы для bbs (доски объявлений) на php)

Пояснение: таблица posts использует parent_id для вложенных ответов (adjacency list). Индексы ускоряют выборку по темам и родительским сообщениям.

Типичные ошибки:

  • Забывают про индексы на внешние ключи, что приводит к медленным запросам при росте данных.
  • Использование ON DELETE CASCADE на parent_id может привести к потере дочерних записей при удалении родительской. В примере использовано SET NULL, чтобы сохранить цепочку, или можно запретить удаление с проверкой.
  • Отсутствие уникального ключа на username или email вызывает дубликаты пользователей.

Как хранить иерархию сообщений без рекурсивных запросов?

Вариант nested sets (вложенные множества) позволяет получать все подсообщения одним SELECT. Схема:

CREATE TABLE posts_nested (
  id INT AUTO_INCREMENT PRIMARY KEY,
  thread_id INT NOT NULL,
  user_id INT NOT NULL,
  content TEXT,
  lft INT NOT NULL,
  rgt INT NOT NULL,
  FOREIGN KEY (thread_id) REFERENCES threads(id) ON DELETE CASCADE,
  INDEX idx_lft_rgt (lft, rgt)
);

Запрос для получения всех ответов на сообщение: SELECT * FROM posts_nested WHERE lft > :lft AND rgt < :rgt ORDER BY lft.

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

Какие ещё подходы существуют для организации сообщений?

Материализованный путь (materialized path) - хранение полного пути в строке, например '1/3/7/. Запрос на потомков: LIKE '1/3/%'.

CREATE TABLE posts_mpath (
  id INT AUTO_INCREMENT PRIMARY KEY,
  thread_id INT NOT NULL,
  user_id INT NOT NULL,
  content TEXT,
  path VARCHAR(255) NOT NULL,
  INDEX idx_path (path(255))
);

Вставка: новый path = parent_path + id + '/'.

Проблема: размер строки может расти, неэффективные LIKE запросы при большом количестве уровней. Индекс на VARCHAR работает только для префикса.

Что делать, если нужно быстро получать количество сообщений в теме?

Денормализация: добавить поле posts_count в таблицу threads и обновлять его триггерами или при каждом добавлении/удалении поста.

ALTER TABLE threads ADD COLUMN posts_count INT DEFAULT 0;
-- и триггер или код PHP для обновления

Проблема: риск рассинхронизации данных. Решение: периодически запускать скрипт для пересчёта.

Расширенные примеры работы с таблицами BBS

Как создать полнотекстовый поиск по сообщениям?

Добавление FULLTEXT индекса на поле content таблицы posts.

Пример 
ALTER TABLE posts ADD FULLTEXT INDEX ft_content (content);
-- Поиск:
SELECT * FROM posts WHERE MATCH(content) AGAINST('php mysql' IN BOOLEAN MODE);

Как вывести все сообщения темы в виде дерева с отступами (рекурсивная функция на PHP)?

Пример 
$stmt = $pdo->prepare('SELECT * FROM posts WHERE thread_id = ? ORDER BY created_at');
$stmt->execute([$threadId]);
$posts = $stmt->fetchAll(PDO::FETCH_ASSOC);
$tree = buildTree($posts, 0);
function buildTree(&$list, $parentId) {
    $branch = [];
    foreach ($list as &$item) {
        if ($item['parent_id'] == $parentId) {
            $children = buildTree($list, $item['id']);
            if ($children) $item['children'] = $children;
            $branch[] = $item;
        }
    }
    return $branch;
}
Пример результата (массив):
[
  {
    "id": 1,
    "content": "Первый пост",
    "children": [
      {
        "id": 2,
        "content": "Ответ",
        "children": []
      }
    ]
  }
]

Как пагинировать темы с учётом количества ответов?

Использование подзапроса для подсчёта постов в теме.

Пример 
SELECT t.*, (SELECT COUNT(*) FROM posts WHERE posts.thread_id = t.id) as post_count
FROM threads t
WHERE t.category_id = :category_id
ORDER BY t.created_at DESC
LIMIT :limit OFFSET :offset;
Результат: таблица строк с id, title, post_count.

Как реализовать вложенные множества на PHP для вставки нового сообщения?

Пример 
// Вставка нового корневого поста (первого в теме)
$pdo->exec('LOCK TABLES posts_nested WRITE;');
$maxRgt = $pdo->query("SELECT COALESCE(MAX(rgt), 0) FROM posts_nested WHERE thread_id = $threadId")->fetchColumn();
$pdo->prepare("INSERT INTO posts_nested (thread_id, user_id, content, lft, rgt) VALUES (?, ?, ?, ?, ?)")
    ->execute([$threadId, $userId, $content, $maxRgt + 1, $maxRgt + 2]);
$pdo->exec('UNLOCK TABLES;');

// Вставка вложенного ответа
$parent = $pdo->query("SELECT lft, rgt FROM posts_nested WHERE id = $parentId")->fetch();
$pdo->exec('LOCK TABLES posts_nested WRITE;');
$pdo->exec("UPDATE posts_nested SET rgt = rgt + 2 WHERE rgt >= ".$parent['rgt']." AND thread_id = $threadId");
$pdo->exec("UPDATE posts_nested SET lft = lft + 2 WHERE lft > ".$parent['rgt']." AND thread_id = $threadId");
$pdo->prepare("INSERT INTO posts_nested (thread_id, user_id, content, lft, rgt) VALUES (?, ?, ?, ?, ?)")
    ->execute([$threadId, $userId, $content, $parent['rgt'], $parent['rgt'] + 1]);
$pdo->exec('UNLOCK TABLES;');
После вставки дерево сохраняет корректные значения lft/rgt.

Как получить топ-10 пользователей по количеству сообщений?

Пример 
SELECT u.username, COUNT(p.id) as post_count
FROM users u
JOIN posts p ON u.id = p.user_id
GROUP BY u.id
ORDER BY post_count DESC
LIMIT 10;
Результат: список имён и количество сообщений.

Как избежать потери данных при удалении пользователя?

Использовать ON DELETE SET NULL на внешние ключи в таблице posts (user_id), а затем в коде PHP при выводе показывать 'Пользователь удалён'.

Пример 
ALTER TABLE posts MODIFY COLUMN user_id INT NULL;
-- Удалить старый внешний ключ и добавить новый:
ALTER TABLE posts DROP FOREIGN KEY fk_posts_user;
ALTER TABLE posts ADD CONSTRAINT fk_posts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL;

Таблицы для BBS (доски объявлений) на PHP - comments

En
Bbs php table (php)