How to implement the sales report with the changing hierarchy?

Good afternoon.

The task of the next:

It is necessary to develop a system to track sales territories. Every day the people of their regions fill data, and the responsible officer collects these data and provides a report in the form of a hierarchy of leadership.

At the moment, everything is implemented as a Nested set and everything is operating quite successfully. But there was a problem getting data for previous periods. Because the structure changes frequently, and the report collects data based on the current structure.

Thought to make a version of the structure, that is, if the structure has changed, the old structure is preserved with the date on which it was relevant. Ie when loading old data will be downloaded old structure. But I think this is not an option, because the database would be a mess every time you change the structure you will create 100500 fields to reflect the new structure.

Maybe someone had to do something like that, I will appreciate any advice.

Thank you.
March 19th 20 at 08:46
2 answers
March 19th 20 at 08:48
Take Mongo
And in PostgreSQL it can not be implemented? Or question in simplicity? - elian.Huds commented on March 19th 20 at 08:51
@elian.Huds, JSON?! - wilmer_Kautzer41 commented on March 19th 20 at 08:54
@elian.Hudscan be, as json if data are poor and do not filter, then everything will be OK, otherwise not OK. - Armand commented on March 19th 20 at 08:57
a lot of data and need to filter) sort, etc.) and sample specific branches to do) - elian.Huds commented on March 19th 20 at 09:00
@elian.Huds, JSQuery does not solve the problems? https://postgrespro.ru/docs/postgrespro/9.5/jsquery - lamont_Auer25 commented on March 19th 20 at 09:03
@elian.Huds,
@wilmer_Kautzer41,
@Armand,
it is possible, as json if data are poor and do not filter, then everything will be OK, otherwise not OK.

In PostgreSQL JSONB data type, not to be confused with JSON.
You can filter, where the indices themselves are created.
"Smart" indexing JSONB - Gerda commented on March 19th 20 at 09:06
March 19th 20 at 08:50
As an expert in Monge says - Monga is unnecessary.

The report, as a rule, the thing is relatively static. In the normal form is generated as a file, usually Excel, etc.
If your report is generated on the fly, then you need to change the approach to writing code and to provide backward compatibility. It is realized by proper choice of data structures and migrations (transformation of old data into the new, with compatibility support).

There is an alternative with the snapshots, ie, just made a copy of the generated HTML page with all embedded data and scripts. The approach is not very beautiful, but implemented faster than the others.

By the way, nothing prevents you from doing snapshots for Nested Sets and store them in a separate table in the form of a forest of trees.

Find more questions by tags Doctrine ORMPostgreSQLMySQLPHP