Как лучше реализовать дерево категорий для интернет магазина в SQL?

Question

[Chvalov]

Как лучше реализовать список категорий с подкатегориями в MySQL БД для интернет магазина ?

Уровень вложения не более 4:
Пример: Бытовая техника -> Кухня -> Кофеварки -> Капсульные

Главное что нужно это скорость выборки с БД
Со временем под категории могут добавляться/удалятся, должно происходить без геморроя, какую схему посоветуете для использования ?

Adjacency List:

CREATE TABLE `category` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) UNSIGNED NOT NULL
  `name` varchar(255) NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX (`parent_id`)
);

Nested Set:

CREATE TABLE `category` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) UNSIGNED NOT NULL
  `name` varchar(255) NOT NULL,
  `left` int(10) UNSIGNED NOT NULL,
  `right` int(10) UNSIGNED NOT NULL,
  `level` int(10) UNSIGNED NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX (`parent_id`)
);

Также подскажите какую структуру лучше использовать для комментариев.

Answer 1

[xmoonlight]

CREATE TABLE `category` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) UNSIGNED NOT NULL,
  `path_id` int(10) UNSIGNED NOT NULL,
  `related` JSON,
  `name` varchar(255) NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX (`parent_id`)
);

Создаём отдельную таблицу category_path:
id | {path:[CATEGORY-ID-LEVEL-1, CATEGORY-ID-LEVEL-2, ...., CATEGORY-ID-PARENT, CATEGORY-ID-CURRENT]}

Сохраняем связанные/похожие категории (их ID) по-необходимости:
{related:[CATEGORY-ID-X, CATEGORY-ID-Y, ..., CATEGORY-ID-N]}

Это поможет за один-два запроса по ID:
1. Выводить "хлебные крошки"/breadcrumbs
2. Получить глубину вложенности категории за один запрос по ID
3. Выводить иерархию только до текущей категории (без "погружения" в нераскрытые ветки, что значительно будет экономить CPU сервера).
4. Сделать выборку похожих категорий для дальнейшего поиска и вывода похожих предложений.
5. Получить ID для всех подкатегорий (и, как следствие, их количество) ниже любого узла для подсчёта кол-ва предложений в нужной ветке (включая похожие категории, по-необходимости).

Answer 2

[Запорожченко Олег]

Посмотрите как работает руби гем https://github.com/stefankroes/ancestry

Он строкой хранит всех родителей "11/23/145", в итоге вместо кучи рекурсивных запросов используются LIKE запросы.

Comments

[Chvalov]

Для выборки удобно, но мне кажется что расширять и редактировать данную структуру не совсем удобно

[Одиночка Айс]

Тоже самое можно и на PHP сделать, может в руби такое по умолчанию сделано, я хз. А расширять и редактировать доаовольно таки просто, мы такую штуку на работе использовали.

Answer 3

[caracal7]

Конечно вложенные множества(Nested Sets)

И реализация очень простая и скорость не сравнима