3 ms·

I believe you can do this even without the `closure` extension, using standard SQL `with recursive`. https://sqlite.org/lang_with.html#recursive_common_table_e

I believe you can do this even without the `closure` extension, using standard SQL `with recursive`. https://sqlite.org/lang_with.html#recursive_common_table_expressions https://sqlite.org/lang_with.html#recursive_common_table_exp...

smartmic 3y ago
There is a valuable section about alternative tree models in the book "SQL Antipatterns" from Bill Karwin. In the end, he compares five hierarchical data designs. Unsurprisingly, all have pros and cons and it is a (somehow advanced) matter of picking the right tool for the job. Closure tables may in fact be the lesser know variant, adjacency lists and recursive queries one of the better knows. But there are also path enumerations and nested sets.