4 ms·

A great overview of the pros and cons of different approaches is given in https://de.slideshare.net/billkarwin/models-for-hierarchical-data https://de.slideshar

A great overview of the pros and cons of different approaches is given in https://de.slideshare.net/billkarwin/models-for-hierarchical-data https://de.slideshare.net/billkarwin/models-for-hierarchical...

jasonwatkinspdx 5y ago
This is a great slide deck and directly follows what I learned about this over the years. In nearly every case I think you want a flattened table of the hierarchy. I hadn't heard the name closure table before so thanks.
- tejtm 5y ago
  Not sure the age on that slide deck but Sqlite has definitely supported recursive table expressions for years now. In some ways it is more permissive with syntax allowed in the recursive portion than Postgres.
  - zozbot234 5y ago
    Yes, many RDBMS's have gotten support for recursive CTE's as of recently. It's definitely not a niche feature anymore.
    - jasonwatkinspdx 5y ago
      Yeah, and it's a straightforward standardized way to solve this. But I still think it's useful to know the flattening idea. I've seen it show up other places like map reduce. It's a useful general idea imo.
    - barrkel 5y ago
      Recursive CTEs are almost never what you want. They're the database equivalent of pointer-chasing in code. If you design your schema such that you need a recursive CTE to query it, be prepared for bad performance unless your CTE only ever does a handful of iterations over a handful of rows. I like paths for representing hierarchy, but closure tables can also be a good idea, depending on what you're modelling and how you query it.
      - zozbot234 5y ago
        > They're the database equivalent of pointer-chasing in code. That's the general case, but more specific CTE queries can be optimized, e.g. by adding database indexes. Recent versions of Postgres have greatly improved wrt. not making CTE's overly inefficient.
        barrkel 5y ago
        I said recursive CTEs. CTEs being an optimization barrier is a different issue - and often desirable with Postgres with its lack of optimization hints. Hence with not / materialized etc.
highpost 5y ago
https://www.youtube.com/watch?v=wuH5OoPC3hA https://www.youtube.com/watch?v=wuH5OoPC3hA
- belter 5y ago
  As there is no context, FYI for others: The video link is the presentation just above.
victor106 5y ago
The author of the deck has a book: SQL Antipatterns: Avoiding the Pitfalls of Database Programming. It’s one of the best books for data model design. Highly recommend.
nlehuen 5y ago
I'm a big fan of the nested set representation. It hits the sweet spot of compactness and expressivity - queries for parents, children, ancestors and descendants are simple integer comparison and pretty fast with the right indices. The only downside is that this requires updates of a large number of rows whenever the tree changes (not all rows though). If you have a sharded table that could be problematic.

jasonwatkinspdx 5y ago

This is a great slide deck and directly follows what I learned about this over the years. In nearly every case I think you want a flattened table of the hierarchy. I hadn't heard the name closure table before so thanks.

tejtm 5y ago
Not sure the age on that slide deck but Sqlite has definitely supported recursive table expressions for years now. In some ways it is more permissive with syntax allowed in the recursive portion than Postgres.
- zozbot234 5y ago
  Yes, many RDBMS's have gotten support for recursive CTE's as of recently. It's definitely not a niche feature anymore.
  - jasonwatkinspdx 5y ago
    Yeah, and it's a straightforward standardized way to solve this. But I still think it's useful to know the flattening idea. I've seen it show up other places like map reduce. It's a useful general idea imo.
  - barrkel 5y ago
    Recursive CTEs are almost never what you want. They're the database equivalent of pointer-chasing in code. If you design your schema such that you need a recursive CTE to query it, be prepared for bad performance unless your CTE only ever does a handful of iterations over a handful of rows. I like paths for representing hierarchy, but closure tables can also be a good idea, depending on what you're modelling and how you query it.
    - zozbot234 5y ago
      > They're the database equivalent of pointer-chasing in code. That's the general case, but more specific CTE queries can be optimized, e.g. by adding database indexes. Recent versions of Postgres have greatly improved wrt. not making CTE's overly inefficient.
      - barrkel 5y ago
        I said recursive CTEs. CTEs being an optimization barrier is a different issue - and often desirable with Postgres with its lack of optimization hints. Hence with not / materialized etc.

highpost 5y ago

https://www.youtube.com/watch?v=wuH5OoPC3hA https://www.youtube.com/watch?v=wuH5OoPC3hA

belter 5y ago
As there is no context, FYI for others: The video link is the presentation just above.

victor106 5y ago

The author of the deck has a book: SQL Antipatterns: Avoiding the Pitfalls of Database Programming. It’s one of the best books for data model design. Highly recommend.

nlehuen 5y ago

I'm a big fan of the nested set representation. It hits the sweet spot of compactness and expressivity - queries for parents, children, ancestors and descendants are simple integer comparison and pretty fast with the right indices. The only downside is that this requires updates of a large number of rows whenever the tree changes (not all rows though). If you have a sharded table that could be problematic.