### Проектирование схемы БД
Поставленную задачу можно решить 3 основными подходами: Closure Table, Nested Sets, Adjacency List.
Я выбрал Closure Table (таблица замыканий), так как он обеспечивает простые и
быстрые запросы к поддеревьям категорий независимо от глубины вложенности.
В любом запросе к дереву я могу обойтись обычным JOIN по таблице связей
category_closure, без рекурсивных CTE и сложной логики в SQL. Это даёт
предсказуемую производительность на больших иерархиях и хорошо масштабируется
при росте количества уровней и категорий. Дополнительно, Closure Table
позволяет так же просто получать не только потомков, но и всех предков
узла (например, для хлебных крошек) через тот же механизм. Структура
данных при этом остаётся реляционной и хорошо индексируемой: по предку
`ancestor_id` и по потомку `descendant_id`. Операции изменения структуры
(добавление, перемещение, удаление узлов) сложнее, чем при простом `parent_id`,
но их можно инкапсулировать в функции/процедуры и вызывать как единый API.
В реальном каталоге товаров такие изменения происходят значительно реже, чем
чтение каталога и выборка товаров по разделам, поэтому увеличение стоимости
изменений логично обменять на ускорение чтения.
Таким образом, Closure Table лучше всего соответствует требованиям:
- произвольная глубина дерева,
- быстрый доступ к поддеревьям
- приемлемая стоимость редких операций изменения структуры категорий.
---
#### Диаграмма БД
---
#### Все товары в категории Бытовая техника (id = 1):
```sql
SELECT
p.*
FROM
product p
inner join
category_closure cc ON
cc.descendant_id = p.category_id
WHERE
cc.ancestor_id = 1
ORDER BY
p.id
;
```
``` text
id|category_id|name |quantity|price |
--+-----------+------------------------------+--------+--------+
1| 3|Стиральная машина LG 6kg | 10.000|35000.00|
2| 3|Стиральная машина Samsung 7kg | 5.000|42000.00|
3| 6|Холодильник однокамерный Beko | 7.000|28000.00|
4| 7|Холодильник двухкамерный Bosch| 3.000|55000.00|
5| 5|Телевизор Samsung 43" | 12.000|32000.00|
6| 5|Телевизор LG 55" | 4.000|58000.00|
```
#### "Хлебные крошки" для категории Двухкамерные холодильники
```
SELECT
c.*
FROM
category_closure cc
inner join
category c ON
c.id = cc.ancestor_id
WHERE
cc.descendant_id = 7
ORDER BY
cc.depth desc
;
```
```
id|parent_id|name |
--+---------+---------------+
1| |Бытовая техника|
4| 1|Холодильники |
7| 4|Двухкамерные |
```
#### Пример получения заказа
```
SELECT
p.name as "Наименование"
, c."name" as "Категория"
, coi.quantity as "Кол-во"
, coi.price_at_time as "Цена"
, coi.quantity * coi.price_at_time as "Сумма"
, sum(coi.quantity * coi.price_at_time) over() as "Итого"
FROM
customer_order_item coi
inner join
product p on
p.id = coi.product_id
inner join
category c on
c.id = p.category_id
WHERE
coi.order_id = 3
;
```
```
Наименование |Категория |Кол-во|Цена |Сумма |Итого |
-----------------------------+-----------------+------+--------+------------+------------+
Стиральная машина Samsung 7kg|Стиральные машины| 1.000|42000.00| 42000.00000|186000.00000|
Моноблок HP 24" |Моноблоки | 2.000|72000.00|144000.00000|186000.00000|
```
### Примеры данных в таблицах
#### Покупатели
---
#### Заказы
---
#### Позиции заказа
---
#### Номенклатура (товары)
---
#### Категории
---
#### Closure Table для категорий
---