Ask HN: Best practices for modeling B-Trees in SQL?
What are some best practices for modeling B-Trees and other types of trees in SQL? What does the schema look like? I know I can just store a node value along with it's parent id in a table row, but I'm sure there are more ways of going about this.
Also, what are pros, cons, tradeoffs of using enums vs a reference table and foreign keys?
I'm after high level principles, no need to think too hard about this. Thanks very much!
(
5 comments
| 3 points
| on HN
)
There is also the nested set model described here[0,1].
[0]http://mikehillyer.com/articles/managing-hierarchical-data-i...
[1]https://coderwall.com/p/ohomlg/nested-set-model-the-best-app...
The relational model is much easier to implement (HN and most forums use it), and insertions are faster, but building an entire tree from any point but the root requires recursion. Nested sets are more difficult to implement and inserts and updates are more complex due to tree balancing, but getting the actual trees is simple at any level.
http://tunes.org/wiki/abstraction_20inversion.html
The best practice here is to just let the database be built up of B-Trees (or whatever) at the low level and use it like a database.