What about the nested set model? https://en.m.wikipedia.org/wiki/Nested_set_model https://en.m.wikipedia.org/wiki/Nested_set_model If you can't use recursive

What about the nested set model? https://en.m.wikipedia.org/wiki/Nested_set_model https://en.m.wikipedia.org/wiki/Nested_set_model If you can't use recursive CTEs I thought the nested set was the older way to do this. There is a bit of work to maintain the structure but once you set it in place it works really well.