How to implement the sales report with the changing hierarchy?
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.
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.