Which approach to choose for a hierarchical table?

Question

PostgreSQL

Good afternoon experts.
Given - a large number of outlets scattered across the regions. Every morning, the manager in each region collects sales data from all outlets in the region over the past day. The head of the company sits in the central office and wants to see all sales broken down by region. That is, there should be a table with a list of regions, the regions are revealed, we see a list of outlets with sales of each point. Accordingly, all points should be summed up per region and display the total figure for the region. There are only 2 levels in this problem, but it is necessary to provide for the possibility of increasing the number of levels (no more than 10).
What is the actual question? How is it more correct, from the point of view of design, to build a database for this task. And how to more competently and of course quickly unload data from the database and display it in the form of a table.
There was an idea to load data at the time of region disclosure. The initial load will be faster, but each time you expand / collapse, you will need to additionally access the database.
I thought about the child-parent approach with a recursive query to the database. But there are still no ideas how to correctly display data on the page then, because direct enumeration of the array can break the hierarchy.
I will be glad to any theories, and the ready decision will be ideal.
Thanks

Answer 1

NestedSet will cover the task completely, there are a lot of libraries for working with this set, and this task probably implies the growth of a tree both in depth and in breadth.

Answer 2

https://en.wikipedia.org/wiki/Nested_set_model
In general, it was sucked up more than once, search for help.

Answer 3

Greetings. I would think something in this direction.

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

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

When rendering the first level (with the display of regions), we take the entries from "regions" and form a list with id. We display and then make a request for data from sales_point by region id (we don’t wait for the list to open). I think I would do something like this.

Answer 4

> I will be glad to any theories
Several options:
Links:

• Article on habré
  
• Wikipedia