What kind of database should I use? 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!
It'd help more if you could be more specific about what you're modeling, as it's hard to picture at the moment, for me at least.

For tree structures, in SQL, any database that supports Recursive Common Table Expressions is a good choice (any major one other than MySQL).

For historical data, the History Table pattern has proven to be effective. For table foo, make a table foo_history and copy-on-write from foo to foo_history. An example.

IBM's DB2 supports SQL:2011 Temporal, which lets you have foreign keys based on time, and is the only db that supports it that I know of.