Best practices for modeling B-Trees in SQL?

Question

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!

krapp · Accepted Answer

What you're describing (id and parent id, sometimes also root id) is known as the relational model.
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.

kazinator · Answer

"How can I use vehicles moving through a network of roads to build a working model of an internal combustion engine?"
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.

beckingz · Answer

enums depend on the database. In general they're better than categorical strings (varchar columns) if the number of categories is below 256. The primary benefit is data quality (no typos allowed), but performance benefits also exist.

Best practices for modeling B-Trees in SQL?

"How can I use vehicles moving through a network of roads to build a working model of an internal combustion engine?"http://tunes.org/wiki/abstraction_20inversion.htmlThe 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.

enums depend on the database. In general they're better than categorical strings (varchar columns) if the number of categories is below 256. The primary benefit is data quality (no typos allowed), but performance benefits also exist.