Episode 28 – Hierarchical Data – Adjacency Lists and Nested Set Models

So, how DO you persist hierarchical Data? We discuss two common solutions to this age-old problem: Adjacency Lists and Nested Set Models. Also, Build Atlanta, technical problems, multi-monitor vs ultra-wide, and utilizing gaming mice to up your game. See all the notes and take the poll by going to: http://www.codingblocks.net/episode28 News Big thanks for the reviews! devup_sam, CordonWebGuy, Ed__B, Kricorn2, Kenneth Glish, oly724, Mrfirley, Mbermanpga //build/Atlanta No HoloLens, but at least there were whale sharks! Awesome alligator joke, investiGATOR...get it!? Michael continues to disappoint us about Resharper Coding against a deadline - all good programming sense goes out the window! Oh yes...and don't forget to take the poll! VIA @theallenu Worst part about coding under a tight deadline is you throw good programming sense out the window. AKA #Technicaldebt — CodingBlocks.net (@CodingBlocks) June 1, 2015 Adjacency List Every record knows it's immediate parent Pros Simple, easy to understand schema Easy to query for children Referential integrity Easy to update Cons Deep trees cause problems Difficult to query subtree Performance issues Nested Set Model Each node keeps track of it's position in a tree in order to facilitate fast querying. "Clothing-hierarchy-traversal-2" by Clothing-hierarchy-traversal.svg: Rp22 derivative work: Zygmuntjr (talk) - Clothing-hierarchy-traversal.svg. Licensed under CC BY-SA 3.0 via Wikimedia Commons. Pros Fast to query Cons Mo' complicated Expensive to update/delete/move No referential integrity Transactional systems difficult to update Resources We Like Models for Hierarchical Data SQL for Smarties by Joe Celko Stack Overflow That article Allen wrote that Joe thought started this discussion. Ultrawide vs Multi-Monitor setups: you decide. The LG 34UM95 is the monitor of choice for Allen LG 34UM95 on Amazon Tip of the Week Big-O Cheat Sheet Unlock productivity in Visual Studio with a Gaming Mouse! Cube viewing in Visual Studio Show summary Talked about two common ways of storing hierarchical data in a relational system: Adjacency Model and Nested Sets Stay tuned for more! Let us know what you think about some shorter episodes Allen's blog post, the post that kicked this all off!

Episode source