I have a domain where I deal with data which has a parent/child relationship, of arbitrary depth.
Also, full time traversal needs to be enabled, to show the state of the data at a specific point in time.
Currently I am looking at two different types of databases, and was curious as to which would be more appropriate, or how I would overcome some issues in either of the solutions.
Any links to articles regarding this information would also be appreciated.
Solution 1: rdbms
good fit for time versioning with a solution like temporal tables in sql server (although the db needs to be open source and free, so I would have to add this to something like PostgreSQL)
not that great a fit for arbitrary hierarchical data; need to implement adherent list/nested set
Solution 2: graphdb
natural fit for hierarchical data
I don’t know a proper way to model time versioning in a way which is performant.
So I am looking on some feedback on the advantages/disadvantages of either database type, and how to overcome some of the shortcomings of either.
I personally was leaning towards an graphdb solution, where I add a start_time and end_time attribute to all nodes and relationships, but I am not sure about the performance and if there are any better ways to get time versioning.
The two main considerations are of course performance and simplicity of the query.
I realize this is a rather open question; I am merely looking to see if I am overthinking this or perhaps fail to take some other positives/negatives inti account.