3 ms·

SQL Antipatterns by Bill Karwin has an chapter that covers this problem in depth. http://shop.oreilly.com/product/mobile/9781934356555.do http://shop.oreilly.c

• duncanawoods 11y ago
  Can you give a summary please - does he say it is an anti-pattern?
  • petepete 11y ago
    If I remember correctly (the book is on my desk at work) he says that adjacency-list hierarchies, without the support of certain DB-specific functionality, can be an antipattern. For example, if you're using a database without window functions, obtaining the entire tree in one query is impossible. Also, deleting nodes without removing the subtree is complicated compared to other approaches like Path Enumeration, Nested Sets and Closure Tables. In the application I'm currently writing (with PostgreSQL 9.5) I opted to use the adjacency list approach, mainly due to its simplicity; nothing other than the parent_id field has to be maintained.
    • netghost 11y ago
      How are you using window functions for querying the hierarchy?
      • petepete 11y ago
        Sorry, my post wasn't very clear. I meant I am using a recursive query to get the hierarchy and a window function to visualise it.
• netghost 11y ago
  There is also SQL For Smarties of you want other approaches.