1

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!

4
  • Have yu looked into graph databases?
    – Mat
    Aug 30, 2015 at 4:27
  • Yes, it feels it might be an overkill for just a tree structure. Am I wrong?
    – roman
    Aug 30, 2015 at 6:04
  • Your structure's more complex than a tree if you model the categories (because the nodes can belong to several categories).
    – Mat
    Aug 30, 2015 at 6:19
  • @Mat - You're right... but I think categories can be seen as labels. Then, I just need to find the nodes that have a certain label. Though then you need to traverse the whole tree searching for the label... Otherwise if using SQL DB I can use many-to-many tables I guess.
    – roman
    Aug 30, 2015 at 9:23

1 Answer 1

0

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.

1
  • Thank you - this is exactly what I've done. I thought it can be done more efficiently other than SQL.
    – roman
    Aug 30, 2015 at 9:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.