Какой подход выбрать для иерархической таблицы?

Question

[drboboev]

Добрый день, знатоки.

Дано - большое множество торговых точек, раскиданных по регионам. Каждое утро менеджер в каждом регионе собирает данные по продажам всех точек региона за прошедший день. В центральном офисе компании сидит начальник, который хочет видеть все продажи с разбивкой по регионам. То есть, должна быть таблица со списком регионов, регионы раскрываются, видим список торговых точек с продажами каждой точки. Соответственно, все точки должны суммироваться на регион и отображать общую цифру за регион. В данной задаче только 2 уровня, но нужно предусмотреть возможность увеличения количества уровней (не более 10).

В чем собственно вопрос? Как более правильно, с точки зрения проектировки, построить базу данных для этой задачи. И как более грамотно и конечно же быстро выгружать данные из БД и выводить на экран в виде таблицы.

Была идея подгружать данные в момент раскрытия региона. Изначальная загрузка будет быстрее, но при каждом раскрытии/сворачивании нужно будет дополнительно обращаться к БД.

Думал о подходе потомок-родитель с рекурсивным запросом к БД. Но как потом правильно выводить данные на страницу пока идей нет, потому что прямой перебор массива может нарушить иерархию.

Буду рад любым теориям, а идеальным будет готовое решение.

Спасибо

Comments

[Иван]

потомок-родитель с рекурсивным запросом к БД

Для вас в аду есть теплое местечко.

Answer 1

[Евгений Бухарев]

NestedSet покроет задачу полностью, есть масса библиотек для работы с данным множеством, также данная задача наверняка подразумевает под собой рост дерева как в глубину так и в ширь.

Comments

[drboboev]

evgenybuckharev да, рост дерева будет, как и его сокращение. ну т.е. изменение данных будет каждый месяц, а насколько я знаю в Nested Set этот процесс занимает много времени.

Answer 2

[Иван]

https://en.wikipedia.org/wiki/Nested_set_model
А вообще обсасывалось не единожды, поиск в помощь.

Answer 3

[RaulDuke]

Приветствую. Я бы думал что-то в эту сторону.

regions: {
  {
    id: 111,
    name: 'ЦФО'
  },
  {
    id: 222,
    name: 'Юг'
  }
},

sales_point: {
  {
    id: 5555,
    name: 'ИП Иванов',
    region: 111
  },
  {
    id: 6666,
    name: 'ИП Сидоров',
    region: 222
  },
  {
    id: 7777,
    name: 'ООО Рога',
    region: 222
  }
}

При рендере первого уровня (с отображением регионов) мы берем записи из "regions" и формируем список с id. Отображаем и следом делаем запрос на данные из sales_point по id регионов (не дожидаемся по раскроют список). Думаю, что делал бы примерно так.

Comments

[Павел Волынцев]

Где тут минус ответу поставить?

Это parent-child да ещё и сущности в разных таблицах - очень неэффективный алгоритм. Применять можно только если всё дерево организаций можно уместить в памяти. Скажем - не более 1000 точек. Но если в SQL надо будет какую-то аналитику соорудить, то будет много JOIN

[freeExec]

Павел Волынцев: Ну а что это за аналитика без JOIN. Или вы сторонник помойки - держим всё в одной таблице.

[Павел Волынцев]

freeExec: организационная структура - не помойка, а узлы одного дерева. Вот организации, контракты, продажи и бухгалтерские проводки в одной таблице держать - это помойка.

[RaulDuke]

Павел Волынцев: частично соглашусь, а именно в том что надо смотреть на объемы данных и возможное увеличение уровней, но в этой ситуации я не уверен насчет Nested Set

[Klein Maximus]

Павел Волынцев:
мухи отдельно, котлеты отдельно
регионы и точки продаж - разные сущности и хранить их нужно в разных таблицах.

[drboboev]

Klein Maximus, RaulDuke, Павел Волынцев, freeExec спасибо за ответы. В Nested Set не очень понятно как правильно строить дерево. А в дальнейшем как правильно перестраивать его. Ну про построение дерева есть множество обсуждений на форумах да и документации полно. А вот про добавление/изменение/удаление дерева, то есть узлов, везде написано что при частом изменении это очень ресурсоёмкий процесс.

По поводу parent-child. Модель очень проста и понятна. Но есть проблема с выборкой данных. Средствами PostgreSQL написал запрос, выбирающий дерево одним запросом. При этом подтягивает продажи из таблицы sales и планы из таблицы plans. Сам запрос выполняется конечно не супербыстро, но в принципе приемлемо. Главная проблема - вывод на страницу. Я не знаю как наиболее быстро вывести результат на страницу.

Есть идеи?

[Klein Maximus]

drboboev:
Nested set хорош, когда нужно много и часто выбирать все дерево с потомками или вложенное поддерево (опять же с вложенными элементами).

Если у Вас вставка и изменение структуры происходят намного реже, чем выборка (например, структура разделов каталога товаров в интернет-магазине меняется крайне редко по сравнению с выборкой), то Nested Set лучший выбор.

Parent-child позволяют за один запрос переносить все поддерево, но для выборки иерархии может потребоваться много запросов.

Выбор зависит от контекста использования.

[drboboev]

Klein Maximus:
Мне, по сути, нужно найти способ быстрого вывода выбранных данных на экран. Но задача - не банальный вывод дерева, что и в parent-child одним рекурсивным запросом делается быстро. Задача - выбрать дерево, при этом посчитать продажи на точку, просумировать на родителя, и добавить туда еще и планы на выбираемый месяц по каждом точке и на каждом родителе. При росте дерева запрос утяжеляется, причем сильно.

[Klein Maximus]

Вообще говоря, само "дерево" необязательно каждый раз выбирать из базы - его можно и закешировать.

Answer 4

[Павел Волынцев]

> Буду рад любым теориям
Несколько вариантов:

• Nested Sets
  
• Parent-Child
  
• Materialized Paths
  
• Иерархические БД
  
• Специальный синтаксис SQL-1999 для WITH recursive
  
• Специальный синтаксис Oracle для START WITH ... CONNECT BY
  

Ссылки:

• Статья на хабре
  
• Википедия