I'm designing a new system and I'm wondering what kind of DB should I use for my needs.
I have a tree structure of nodes, where each node has historic values. For each historic value it should hold 2 values:
The node's own value at that historic moment
The sum of all values of his children
Aside from that, each node could be defined as part of some categories - so I should be able to query the DB to get all the nodes of some given category (Example: Give me all nodes, under category CAT1, at a specific point in time). Each node could be part of several categories.
Further more, the historic data could have multiple versions at each data point - so if I have versions A,B,C - For each data point, I should be able to get the data for version A, B or C.
This of course can be achieved using a traditional SQL database (which I kind of created using PostgreSQL) - but that would require recursive queries to build each point in time (to compute values of all children). Also, though this data is relational - it feels that it can be defined better using non-relational tools. Just a feeling :)
Another thought was using NoSQL (MongoDB), where each node would contain its children - but then you have trouble getting to specific inner nodes without traversing all its parents and trouble using effectively the references of containing categories. I might be wrong about those points, though.
I would love to hear some pointers to what might be a good solution.
Thanks!