I'm using a closure table (Postgres through Django) to roll up stats through a hierarchical tree. When a node that is 10 layers deep has a value that increases, each parent's sum total of that value can also be easily calculated at any given time, thanks to the closure table.
Let's say our parent node has 4 children, each of which have 4 children, each of which have 4, etc to the point of 5 layers of depth.
Each node has a
node score table in which we update their score with new entries that can change a couple times per day, and a calculated column called
family score that is the sum of its sum of that value for all descendent nodes.
So here's my challenge: In this example, the very top parent has 1363 children whose changing scores influence it's own
family score calculated value, along with the
family score of every parent between that one and the one who's
node score just changed.
I need a way to be able to map any node's
family score over time. I've come up with two solution, neither of which I think are likely to scale well:
family scoretable, and store a new static value for each ancestor node any time a node's score changes. Thus each write is multiplied by the depth of the node whose score is changing. The structure could easily get to 150-300 in depth.
When loading the data for the yearly score chart, first get an array of timestamps for each
node scorerow within the date range specified and with the given parent specified, and then for each timestamp, find the latest
node scorerow for every descendent and sum the values.
Option 1 seems like its less likely to break, but I'm guessing there's a best practice for this type of thing? Searching hasn't produce anything that quite addresses this use case. Any insights would be massively appreciated!!