5 ms·

Yeah, use a graph database. Most trees end up becoming graphs as changes happen over time and the CTEs won't help much anymore.

olavk 8y ago
Doesn't graph databases have similar query facilities? How does shifting to a graph database avoid the complexity of recursive queries?
- meritt 8y ago
  The primary facility in graph databases is each node explicitly contains a list of relationships it has with other nodes. The relationships are treated as first-class citizens. Neo4j has a great set of docs that introduce Graph DBs to people already familiar with RDBMSes [1] https://neo4j.com/developer/graph-db-vs-rdbms/ https://neo4j.com/developer/graph-db-vs-rdbms/
  - davidcuddeback 8y ago
    Another good resource is Designing Data-Intensive Applications [1]. Chapter 2 does a really good job explaining how different categories of databases relate to different data models, including examples of querying graph-like data models using `WITH RECURSIVE` compared to a query language for graph databases. [1] https://www.amazon.com/Designing-Data-Intensive-Applications-Reliable-Maintainable/dp/1449373321/ https://www.amazon.com/Designing-Data-Intensive-Applications...
- wooly_bully 8y ago
  They're similar in complexity, but made easier by the fact that graph databases explicitly exist to query these types of relationships. Where someone may not know how to find their root manager node in an employee table, it would be an ideal thing to query for in a graph database. There are functions like 'shortestPath' in neo4j for example.
ris 8y ago
Though I would propose that in the vast majority of applications, the amount you're going to need to do graph-like operations is significantly outweighed by the amount you're going to need to do regular relational-type operations. Being able to do them "well enough" without adding yet another moving part to your system (one which holds data that probably needs to be synchronized with other data stores) is extremely valuable.

olavk 8y ago

Doesn't graph databases have similar query facilities? How does shifting to a graph database avoid the complexity of recursive queries?

meritt 8y ago
The primary facility in graph databases is each node explicitly contains a list of relationships it has with other nodes. The relationships are treated as first-class citizens. Neo4j has a great set of docs that introduce Graph DBs to people already familiar with RDBMSes [1] https://neo4j.com/developer/graph-db-vs-rdbms/ https://neo4j.com/developer/graph-db-vs-rdbms/
- davidcuddeback 8y ago
  Another good resource is Designing Data-Intensive Applications [1]. Chapter 2 does a really good job explaining how different categories of databases relate to different data models, including examples of querying graph-like data models using `WITH RECURSIVE` compared to a query language for graph databases. [1] https://www.amazon.com/Designing-Data-Intensive-Applications-Reliable-Maintainable/dp/1449373321/ https://www.amazon.com/Designing-Data-Intensive-Applications...
wooly_bully 8y ago
They're similar in complexity, but made easier by the fact that graph databases explicitly exist to query these types of relationships. Where someone may not know how to find their root manager node in an employee table, it would be an ideal thing to query for in a graph database. There are functions like 'shortestPath' in neo4j for example.

ris 8y ago

Though I would propose that in the vast majority of applications, the amount you're going to need to do graph-like operations is significantly outweighed by the amount you're going to need to do regular relational-type operations. Being able to do them "well enough" without adding yet another moving part to your system (one which holds data that probably needs to be synchronized with other data stores) is extremely valuable.