Coding Blocks - Hierarchical Data – Adjacency Lists and Nested Set Models

Episode Date: June 8, 2015

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 […]

You're listening to CodingBlocks or head to www.codingblocks.net and find our other social links there at the top of the page. And with that, welcome to CodingBlocks. I'm Alan Underwood. I'm Joe Zack. And I'm Michael Outlaw. And this is our first remote recording. So we'll see how well this works out. Yeah, so me and Outlaw are in the same room, and then Joe Zack is somewhere in sunny florida we're sending smoke signals to him that's right you see that no it's dark now oh yeah this isn't gonna go over well all right so let's kick it off with our news this week or this week i say it like that i mean it's been a while since we recorded been busy so too long oh yeah it has been

too long we were all kind of chomping at the bit to get back too long oh yeah it has been too long we're all kind of chomping at the bit to get back to this so yeah it's been too long yeah the first thing we want to talk about are the poll results so first off thank you for all of you that went up there and told us how often you wanted to hear us and if you haven't now's your chance you go to uh slash poll or slash episode 27 so codingbox.net slash episode 27 or codingbox.net slash poll or slash episode 27. So codingbox.net slash episode 27 or codingbox.net slash poll. And let your voice be heard. Yeah, so right now we've got a pretty tight race

between the way that we do it right now to where we release once every 12 weeks. Wait, wait, once every 12 weeks? Whatever, get out of here. You know what though? You know what though? I still want to go through the results yet, so I'm not sure if we want to say that. every 12 weeks whatever get out of here you know what though you know what though i like i i still want to go through the results yet so i'm not sure if we wanted to say that but what the results were

but i will say this though about some of these like especially for those that like took the time uh to to write your own uh uh you know result there it will definitely make it harder to uh uh tabulate all of that but there were some awesome little gems in there and i'm pretty sure one of you guys uh i'm talking to you alan and you joe set some of these up though because like for example someone said yeah their quote was anytime i can hear michael's voice would be awesome. Yeah, we did not do that, but it was fun. No, I don't believe you. I don't believe you. And I thought there was another one that was similar along that same vein that was like,

but it said sweet voice or something. No, my absolute favorite was one a week, one hour long, sweet Jesus. Oh, yeah, right. Yeah, no, there were some really good ones in there. They were like that. So, you know, I really do appreciate the time where we appreciate that you taking the time to go up there and vote. And like I said, if you haven't,

uh, now's your chance. And I think we're going to make this a regular thing. You know, we'll have, uh, you know, some,

some different polls and see, see what happens. Try to put some interesting questions out to you guys and give you guys an opportunity to, uh, you know to let your voices be heard. Yep. So definitely appreciate the time taken to do that. And speaking of appreciation, I wanted to give a big shout-out and thanks to DevUpSam,

Corden Webguy, Ed B., Crycorn2, Kenneth Glish, Oli724, and there's two names here I'm going to need some help from Outlaw pronouncing. Oh, Ed B. I got that one. And Cordon Webguy. Hey, no. I did those. You get the hard ones.

Well, I guess this first one is Mr. Furley. That one's not so bad, right? I thought you were going one is Mr. Furley. That one's not so bad, right? I thought you were going to say Mr. Furley. No, I think I got that one right. And then, oh, man, why would you do this to me? Just go fast.

M-Burman PGA, right? Yeah, maybe that's it. Yeah, maybe. M-Burman PGA, right? Yeah, maybe that's it. In Berman PGA? Actually, that reminds me. I was watching Tosh.0 the other night. And by the way, he's from right around here. And there was an app. I want to know if you guys have heard of this.

It's called Speech Jammer. And what you do is you plug your headphones into this thing and you kind of hit play and then you try to talk. And it kind of sends this little feedback loop of your own voice back to you that's supposed to kind of mess with your speech centers in your brain. So it's actually really hard to talk while you're kind of hearing this stuff. So I thought that was kind of funny. It might be a fun little gag, like get your wife on there and ask her to tell you a story or something,

and you just end up sounding ridiculous. I thought he was trying to give us some kind of hint about this remote recording setup. He was having a hard time because he was hearing himself. I think he's making excuses already. We're like five minutes in. He's already dropping an excuse bucket. I'm going to drop into the sleep bucket in a minute yeah no doubt but seriously some of these reviews are absolutely fantastic we got several that said hey this is a must add to your podcast

collection or you know quickly becoming one of my favorites so hey guys seriously thank you very much uh we haven't gotten enough of them i like like the one where Mr. Furley, and he says that we're funny. We try to be sometimes. And sometimes it just comes across that way anyways. But, yeah, keep them coming. We absolutely love and appreciate that you guys take the time to do that. So thank you. Super, super appreciated.

The next thing we want to talk about was Build atlanta yeah man that was awesome outlaw is even wearing the shirt right now yeah coincidentally um that was a so outlaw and myself went to this to this all-day event that microsoft hosted here at the atlanta aquarium and it was very well done first off like i mean it was this is like the the on the road version of build yeah right yeah so we didn't actually get to see the hololens man they didn't well i mean we got to see pictures of it but we didn't get like touch one or see an actual i think everybody there wanted to see it right like i think that's why half the people showed up to it but is this thing real though i mean is this really going to hit the market man personally i think it's vaporware i don't think it's ever going to happen i don't know

what's the hardware equivalent of vaporware whatever that is that's what that is i don't know dude google glass well see google glass actually felt like to me like it that really was vaporware but this thing looks like it has potential. Yeah, but that actually made it into the market and people could buy it. Yeah, now is it in the market anymore? Well, no, not now. But, well, yeah, whatever. But what I mean, though, is it was actually a thing that people could buy.

It was just a ridiculously expensive thing. I'm curious to see if this ever even gets that far, though. Or is this going to be like the Surface table that you only hear rumors about? I feel like anything at CES, like if you see it at CES, it's just never going to happen. But you know what? Here's the thing, though. From my perspective, just looking at this, like what they've shown of it, if they can bring the price of this thing down into within reason,

let's say within an Xboxbox one type price range you know 500 ish dollars if they can get it there google glass was 1500 why do you think this thing's going to be less so so think about it like this microsoft has been known to take a hit on hardware knowing that they'll make it up on the back end the xbox 360 sold the loss the xbox sold the loss that's just how game consoles are done now not not always but but the thing is is think about this if they can get this in the hands of people and they can and they can monetize a market because they're they're going to use case for the thing is so small i don't know the thing is i can't imagine building software for it like that would be

incredibly here's the problem here's the problem in in it doesn't suffer the the exact same fate as 3d tvs but a similar one and that is that for people in glasses like myself i'm automatically like me it's probably going to be uncomfortable to wear on top of my glasses or not you know comfortable or not like look correct or something do you need contacts yeah it's not happening yeah i don't know downers gosh i don't know man like it looks really cool to me like some of the things that i saw that they could do with it now how well it actually works in real interaction who knows like you can't even see the thing anywhere but i i just can't imagine writing software for it could you imagine actually trying to do an augmented reality type thing to where it's you know it's not like something on your phone where you point your phone in a

direction it shows you the windies it's the closest right maybe this should be the next poll is hololens real because dude it's real no i mean i this thing could actually be like a michael bay production right just making you think like like you optimus prime looked pretty convincing in those movies right this could be another one of those yeah i don't know i'm excited about it i hope it i hope it is real but i mean we haven't seen one yet but anyway i guess back to the show on the uh on the atlanta build it was really like it was a day full of information that i thought was really cool like the approach that microsoft is now taking to software is you it's almost like responsive web design like but what what i got out of the day's worth of stuff where they have what was called wp um f no not wpf uh it was the uh oh god why can i not

remember what it was right now but basically they're bringing the entire platform all together so you'll be able to write a piece of software that could run on something from a raspberry pi to a windows phone to a Windows tablet to the desktop. They're trying to unify the experience. I mean, they've been touting that for years, though. They have, but now they're actually forcing it all through the same programming model, which is a cool thing. Back in the day, C had this grand vision that you could write your C code on one time

and run it anywhere, too. Well, Java also. And then and run in anywhere to, well, Java, Java came along with a similar promise and Microsoft's been saying a similar thing with like, Hey, write your universal app. I mean, I get it.

It's cool. There, there's some stuff out there. I'm not trying to be a Debbie downer, but, uh, there, there,

you can tell that they're definitely feeling pressure from, you know, Apple and Google, uh, especially in the mobile market. But yeah, and there was some really, there was some really cool stuff there, but you know apple and google uh especially in the mobile market but yeah and there was some really there was some really cool stuff there but you know one thing too is just like the expense of it though oh god that that whole day was covered yeah i mean we didn't we didn't pay for parking we didn't pay for food and dude they rented out the entire uh ballroom of the

atlanta aquarium so it was not a cheap event and i mean they want people to get excited about what they rented out the entire ballroom of the Atlanta Aquarium. So it was not a cheap event. And, I mean, they want people to get excited about what they've got going on. And I will say this. As a.NET developer, it is exciting, but it would be way more exciting if they had, you know, a ton of mobile users or, you know, something along that line. Because right now, still, like if you're going to write an application you're writing it for windows right you're not you're not really going to be writing it for the windows phone now the the one cool part is if you're at a mobile app you're going

to write it for ios first that's what i'm saying but here's the cool part right like if let's just say if this this thing happens and this is what they're saying, Windows 10 is going to be a part of this universal platform that they're doing. If you can write it for Windows and then for essentially free, it will scale down to a phone and a tablet and the Internet of Things devices, that could be cool, right? Like now you have buy-in to a platform and you didn't really have to work at it because like you just said. Any application you're going to write for a Raspberry Pi isn't going to be something that you're going to be interested in on your phone. No, no, no, no.

What I'm saying is you write a core for your desktop app and now let's say it's a business layer or something like that. Or you layer your application properly. That now becomes available across all devices. Yeah, you're not going to write a desktop application that's going to translate perfectly to a phone, but it is cool that you could have that same core be a part of it, right? I got two words for you that came out of that event.

What was that? Data Lake. That was cool. Data Lake, what's that? It was, you know, honestly, I don't even know. There were some Azure announcements that were made there too, and one of them was this new Data Lake service for dealing with gobs of data.

But I couldn't help but laugh when I heard the name because it just sounded kind of humorous. And then, yeah, there was the image of the dump truck spilling trash everywhere. And I just kind of thought of that as like, you know, here comes the data truck coming to get your data to take it to the data lake. Yeah. And code was the most disappointing part of it for me. Like the massive text editor that is code? So at the original Build event, they announced code,

and then I guess I misunderstood its purpose because I was thinking like, hey, this is a cool new IDE that they're creating to be cross-platform. But at the Build event in atlanta they were very careful every time they mentioned it they referred to it as an editor yeah they never once talked to it because because even in my own experience like as soon as it was talked about i was like oh let me go download this thing and start playing with it and see if i can write some code you know like let me just write a simple hello world app and compile it and watch it run or debug it or whatever and you know you

try to you know bring up a taskbar you're like hey do a build and it's like okay yeah i configured that and you're like no you should already know how to do the build and seems like a sublime competitor notepad notepad plus plus well well yeah maybe but um and they they at one point during the presentation in atlanta they mentioned that um you could do compiles for uh what was it um it had to be a mono specific project in order to compile only on Linux and OS X. So I've been playing around with this on a Windows 8 box, and that explains why I couldn't do any kind of compile with it. Well, it does say on their site, code.visualstudio.com,

that it has an integrated debugging experience. Yes, for JavaScript. Oh. Yeah. So if you wanted to write C Sharp on it, right, and then compile it, you have to A, be on something other than Windows, which means B, it's a mono project.

Because at the moment, they haven't released their own uh their own core yeah non micro non windows based um dot net platform or runtime that's really that's interesting i mean okay so i will say this if you throw it into this into the campus sublime that is still pretty cool right i mean but then was it buying you though that that's the disappointing part it's free oh my god so so you can download sublime and use it forever for free and it'll let you Right. I mean, but then was it buying you though? That that's the disappointing part. It's free. Oh my God. So, so you can download sublime and use it forever for free and it'll let you in notepads out there. I'm not saying, I'm not saying that you should,

I mean, you know, you know, give them a few bucks. Right. But, but I believe if I'm correct me from wrong, I'm,

I'm fairly certain that this is based on Adam, which is another free editor. Yeah. Adam is, I don't know. Is it based on Adam? I really don't know. Oh, fairly certain it is. Yeah. Well well either which way i mean they did they did talk about it but

it wasn't a huge it wasn't a huge part of build i mean build they definitely they showed some stuff with azure that was really cool like some of the analytics you get if you're hooked up into their services i mean they were they were walking through I mean, if you were in love with statistics and you like to analyze every bit of everything that happens, like you can see all kinds of cool stuff on their dashboards and Azure. Um, I mean, the, the unified platform was one of their big selling points and that was mostly using a WPF and XAML, right? Was it WPF or was it just XAML? I know that they were talking about things,

but it was similar to responsive design because you had things like saying, hey, if the screen width is 1024 or less, then switch to this mode. I think that's pretty exciting. For a while there, we were kind of afraid that maybe they were going to be getting away from XAML because they kind of killed off Silverlight four or less then then switch to this mode so i think that's pretty exciting you know like for a while there we were kind of afraid that maybe they were going to be getting away from xaml because they kind of killed off silverlight and we're promoting html and javascript but looks like

it's still it's still in there actually not only is it still in there it seems to be the focus for yeah it's definitely if there's any question as to like what what was the future for xaml it seems like it's uh not going away yeah it's it's pretty much the predominant for creating your UI stuff. So that was really cool. Just to go back to the data lake, though, to give you a more definition of it, Microsoft touts it as a hyperscale repository for big data analytic workloads.

So now you know what it means it's clear as mud um no fixed size on file limits or account size it's based on a hadoop distributed file system blah blah blah blah yeah they they also said some ridiculous statistic there that didn't make much sense like some some massive percentage of linux running in the cloud was theirs do you remember what that it my battery on my laptop was dying because i mean oh i don't have the notes with me yes they made some ridiculous claim about well i say ridiculous like some obscene percentage of all the Linux web servers. I want to say it was like that they said 80% or something like that.

Man, I wish I had my notes with me from the show. But they said that they were all running on Azure, like cloud-based versions were running on Azure. And all I could think was there's no way. With AWS being there. I mean, you got Google Compute. You got AWS EC2 instances out there. You got DigitalOcean.

Really? You have. Well, let's take all the small guys. Let's just focus on the two big ones. Yeah, there's no way. AWS is definitely leading the way. I actually found that stat. It's over 70% of all Linux boxes running on Azure

are running on Azure. I believe that. Nice. Well done there. See what you did. Yeah. Yeah. There was something else that was on the tip of my tongue.

Yeah. When it was said there, like literally both me and Mike looked at each other like, no way. The.NET native. That was another cool feature coming out where uh you could just simply recompile your app to be a dot net native app and it would um you would gain uh it was a huge performance boost yeah the the numbers were like 20 40 60 so i'm trying to remember like which was which though you got you got uh a smaller file size in a smaller memory footprint and oh so you know i know what it was i know what it was it was it was something like uh dang no i don't one of them was the one of them

was the start time uh a cold start time, and then the other one was the warm start time for the app, and then the other one was the memory footprint. Yeah. So here's what he's talking about with.NET native thing is basically what happens is right now when you compile a.NET application, it compiles it into the CIL or whatever. CLR? Yeah. it you know it compiles it into the uh what is it cil or whatever um and then or no yeah so then that gets run but the first time you take a big hit because it has to

basically load it all up and and get it ready to run when you do it this way it actually compiles it down to c level code right wasn't that what it was no this was it compiles it down to native machine code that performs like c plus plus code but this is taking you know like your your universal apps you see sharp app and compiling it down to native oh here we go it performs it you know their app will start from a fresh start 60 faster than it did And all you had to do was just recompile it. And it would use anywhere from 15% to 20% less memory than it did. And again, you're just recompiling the app. Yeah, and another thing that they were talking about a lot

is they spent a lot of time in their runtime making sure that applications now are easier on the processor because they're more concerned than ever about doing things on the mobile platforms and tablets and that kind of thing. So they've really been focusing on these things a lot. I mean, there was definitely some cool things that came out. There was another website. It was something to cat Microsoft.

Man, I tweeted all this stuff out. Actually, real quick, I just wanted to mention that There actually was a tool called Ngen, I think it even came out When.NET came out, that let you Compile things to native code But I'd always heard it was kind of a bad thing Because you lost a lot of the benefits of Just-in-time compiling

So I've never been really clear on what the difference between Ngen and the new.NET native stuff is But I've heard the same clear on what the difference between NGIN and the new.NET native stuff is, but I've heard the same claims about performance benefits and everything. So I'm kind of interested to hear the difference. And if you know what the difference is, I would love to hear it. Yeah. Oh, and the site I was talking about, and it's free, by the way. You can go up there and check out all these things that we were just kind of mentioning,

is MicrosoftVirtualAcademy.com. So they definitely have a lot of cool stuff. And it's all free. They teach you things. They have sections broken down by type. So C Sharp and XAML, hybrid cloud automation, cloud app development, blah, blah, blah, blah, blah. There's a ton of stuff.

So definitely go check that out if you're interested in the newest things coming and learning for free. Yep. All right. So I think, uh, I think we've,

we've hit a lot of points on the build ATL. So what you got? Yeah, there's plenty of stuff out there that you can find. Yeah. Yep. Uh, so I just want to mention really quick,

um, Tampa code camp is coming up. I missed the Orlando code camp when I moved to the area, but, uh, just kind of throwing it out there. I do plan on starting to hit up some of the meetups Camp when I moved to the area, but just kind of throwing it out there. I do plan on starting to hit up some of the meetups in Central Florida.

So if you guys are in Central Florida, you should hit me up. Hey, what day is that? I'll be there. Yeah, you're going to be there? Should I drive now? Do I need to... I'm looking it up. I think it's July 14th. So you've got

a couple weeks. I'm actually going to be down there, dude. I'm not going to be too far from you. Oh, really? In Tampa? Yeah, yeah. I will be just across the way from Tampa. So I might actually come out to that. Oh, awesome. July 18th is the day.

Excellent, dude. I'm going to be down there. All right. All right. So the next one, I actually appreciated this. So Rebecca on Twitter, the other night I had a very, very long coding night. I think the longest ever that I've done. And I was getting a little punch drunk and I needed some humor.

And she came to the rescue. She said, what do they call an alligator in a vest? All right. Anybody? I don't know an investigator that's wonderful it was super corny and exactly what i needed at about three or four a.m whenever sometimes a good bad joke can just make you feel fuzzy oh wait wait wait why are you calling her joke a bad joke that was fantastic it's a good bad joke. You know what the great part is?

I think all of us looked at this on our phone, and it cut off the actual answer. And I was like, is she never going to give us the answer? It was ridiculous. You were texting back and forth like, what's the answer? I don't know.

And it was just further down on the tweet. She built suspense. So we appreciated that. That was fun. Yeah. further down on the tweet she built suspense so uh we appreciated that that was fun yeah now i i have to pour one out for my buddy here singleton so oh god no no me and singleton are tight no resharper oh my god so we've already talked about you know how how I had uninstalled it and everything, and I'm being sans ReSharper. And honestly, I've kind of gotten addicted to productivity tools,

the add-in that Microsoft makes for Visual Studio, productivity tools or Pro Tools or something like that, 2013. And it's got some nice little add-ins to studio but definitely not to the degree that um resharper has especially resharper with dot cover which was my favorite combination and uh so you know i said last time that i would eventually come back to try you know try and resharper out if I ever decided to or felt the need to. So, I did. I decided, hey, you know what?

I'm going to Ultimate. And because every version I've ever used in the past has just been immediate gold, like I've loved it, and it has like you know a part of me why am i going to bother with this trial i'm just i'm all in let's buy the upgrade to ultimate and awesome things will happen and i didn't like it really like too slow or shortcuts what didn't you like i didn't like it yeah it was really it was really bad um wait really bad well my experience was really bad let me be clear um you know how you you hear a lot of people that say that what they don't like about resharper is that it's slow and you know the what it does to visual studio and things like that, they're not wrong. So I had problems where just trying to navigate between,

especially the current code base I'm working in is rather large in terms of the number of files and whatnot. And going between files, it would just visual studio would hang often uh almost always and you'd get a not responding prompt coming up from visual studio all because you decided it was time to look at another file and it was random little things like that and uh just even sluggishness while using it. And again, I know before anyone jumps on this because I mentioned the productivity tools,

there is a known issue where productivity tools and ReSharper cannot coexist. So I had already – at first I tried just leaving productivity tools disabled and see if that would work. And then that still wasn't buying me anything. So I uninstalled productivity tools because I really wanted to commit to ReSharper, especially after I'd already paid for the license without trying the trial. And still, my luck still wasn't bad. Or wasn't good. Yeah i'm sorry yeah um yeah it did not work out well for me and so i ended up uninstalling it almost within 24 hours wow of paying for the license oh that's right and so for the first time what's your license key i'll try try it out and make sure it's...

Yeah, no kidding. Share it on Twitter and just let whoever gets it first get it. I mean, I just want to make sure it really is bad. Right, right. So I... But for the first time, though, like ReSharper, like a lot of tools, or not a lot, but, you know, several tools do this thing where, like, you know, after you uninstall it, they immediately open up a browser to their

feedback page, and they want to know, like, hey, why are you uninstalling this thing, right? And I have always just been like, eh, I ain't got time for that. Forget it. Why do you care? Whatever, right? But this was the first time where I was like, no. This is – I love this thing. I've loved this thing for so long now. Right? Like I have told everyone. I have gone to the hilltop and I have sang its praises for so long now. And now it has betrayed me.

I need to let you know how I feel about this betrayal. Right? So I actually did write back. So I will say that it's not over. Right. Um, because there, so there,

there's, there is advantage to filling out those forms. It turns out because their support team immediately got back in touch with me to wanting to find out, uh, why. and one of the jet brains tools is the profiler and uh so i sent them some profile information from visual studio so that they could debug see like what was happening with it so maybe they'll fix the problem in a future resharper bug but until then i'm kind of like you know at the mercy of

if they fix the bug you know if they decide to fix it if they can reproduce it if they can fix it whatever right and to be clear you're not running on like a core i3 you have like an i7 with 16 gigs of ram right oh yeah yeah yeah so yeah ssd and all that good stuff. It's not a slotch machine, and I can't use my favorite tool. That's a little rough. Which really makes it difficult. I haven't tried installing dot cover by itself because I suppose I could. It just hurts too much? Yeah, the paint's a little too fresh.

Yeah, that's what it is. Yeah, the pain's a little too fresh. Yeah, that's what it is. Yeah, I feel you. That would hurt. So all you ReSharper naysayers, I guess scoot over. Here he is. All right, so one last quick thing in the news section before we get into the real stuff here today is uh this hit me so hard the other night because we've all been there where you got to get something done right like it has to be done

you have to get it done dude i'm sitting there and i i think i even im'd outlaw and i was like dude i i'm really just starting to throw away good programming so it's right now he's like dude you need to tweet that. And I did. And a lot of people favored it because apparently everybody feels this pain, right? Like you're open. It's the 11th hour and you've got, you know,

10 more hours of work to do. And it's like, Oh God. And so you just really start, you know, almost just forgiving yourself ahead of time for what you're about to do. Because it's like, I don't have time to make this perfect. I don't have time to make this even decent.

I'm just making this, right? Well, maybe this should be the poll, the next poll then. Because I dare say that like a majority of code that any developer has written where they were like under the gun, you know, tight deadline and really you know pressured that's when they probably wrote the code that they are the least proud of or or there's you know portions of code they're like yeah i'm not proud of that part but i had to get it done so i'd love i'd love to see that commit message i just imagine saying like i dare you to say something you know 4 a.m actually i was i was laughing about this earlier so i've become a fan of rebase and get because i thought you could squash your own history well apparently

there's a way to do that but the way that i've done it doesn't so my commit messages like there's things like man this is some bs just shoot me like i definitely had a bunch of commits where i'm like man i'm fixing another problem with their framework i'm so ticked off right now right like remove test case since code didn't pass qa wait i didn't run a test case i guess you can't fail but no i mean seriously like we've all been there where it's like oh you started out you you'd separated all your classes you had this nice roadmap ahead of you and you get down into it and you know 90 bugs later that you're trying to work around you're like man i don't even care what this looks like when it's done as long as it comes up on the screen the

way it's supposed to right so yeah it just wanted it all of those who favored it and we retweeted i'm i'm sure that you've all been there i did it for the nerve i did it for the walls right get commit commented out failing tests if you guys don't recall or if the if this is new to you and uh you do find yourself in a similar situation as alan did where it's 4 a.m and you know you got to do a commit and you're like gosh what do i i don't even know what to say about this commit you really should just go to what the commit.com and let it tell you what your commit message should be yeah that's i probably i wish i would have remembered that the other night because i would

have or the other morning so yeah man that was that was a rough night so anyways on to what we're talking about today which are hierarchical data sets um we we did that yeah we did a couple episodes on sequel um a while back and these kind of things come up all the time. And they're a little bit more advanced topics. We're going to start down at the bottom with this, but we're going to work our way up. So the first one that we're going to talk about is called the adjacency list. And it's pretty simple. If you've worked in databases for long at all, you've seen this stuff. Essentially, if you think of an employee table, just a simple table, typically what you'll have is an employee ID and a manager ID.

That is the adjacency list right there. Essentially, it's a self-referencing parent-child relationship within the same table. Well, let me sum it up in an easier way. Every record knows its immediate parent yes yes so if you have an employee id let's say that you know let's say that michael's the boss i'm the middle guy michael's boss joe's the middle guy and i'm the grunt down at the bottom so that's why you were up at 4 a.m right so michael's id1 joe's id2 so michael his his manager id is going to be null because he's the top of the chain right that's right um joe like where this is going joe's id2 well his manager id is going

to be one that's michael and then me down at the bottom my employee id is three and my manager is two so that's joe so if you were to query this thing going up, then you'd see that, hey, I am Joe's worker and Joe is Mike's worker. And that's how the table looks. Okay, so it's a tree basically, right? So everyone has only one parent, but each parent could theoretically be parent to multiple. Yeah, so if we wanted to break that down to where it wasn't a complete waterfall down, right, to where it was Mike, then you, then me, it could be Mike's at the top, you know, ID one, uh, manager ID, Noel, then your ID to manager ID one, I'm employee ID three with manager ID one.

So both of us are just one level below him, but multiple children, just like you said. Okay. So an example thing I might want to query is uh give me all uh michael's reports yep and yep and well are we going to talk about direct reports or are we going to talk about like the full tree let's start with direct reports right because keep it easy yeah we'll get we'll get into the meat of the problem in a second but yeah so direct reports you basically be like hey select star from or star that's a bad idea anyways but select star splat splat from uh employees where manager id equal one give me all of mike's direct reports

right so that's it pretty simple another thing that you typically see in these type of situations are hey show me the top level guy and there's usually two ways of going about this. You'll either have the manager ID null, or you'll have the manager ID equal whatever his ID is. So you'd say, hey, select star from employee where manager ID equal null. That'd give you back. Or if they had filled it in, you could say where employee ID equal manager ID. And now you have the top level guy. So those have covered the basics of, of what the adjacency list is. Um,

now to get into the harder part, right? So let's say that you are a few levels deep. How do you go about doing that? Well, this is where you'd be tempted to use your recursive CTE, not to solve your problem. Okay.

Well, do this you could do it this way yeah so but go ahead cte for those who don't know or haven't listened to the previous ones that's a common table expression oh and just to be clear the previous episodes that we're talking about were episodes 13 and 14 yep and they those are pretty deep episodes so um if you if you want to put your sequel hat on it's there there's a lot of hats are a must yeah those that those are some pretty deep episodes um but a cte is a common table expression and all that is it gives you uh it kind of allows you to create virtual tables in your queries um With some added benefits, what we're talking about with the recursive CTE.

Now, here's the thing with it. It's only available in some RDBMSs. MySQL does not have CTEs. Postgres does, SQL Server does, Oracle does. A lot of what they call enterprise-based database management systems do have this. But like MySQLl one of the most popular on the planet does not have a recursive or even ctes so sometimes people

call these with clauses right or i think i've seen that word before we'll say um you're doing a with in your sql yep that's that's what it starts with is a with um so with a recursive cte they're they're and and i know why mike's saying that you would be tempted to do it and here's the thing if you have relatively shallow or smaller data sets that you're using just go ahead and use them they're awesome um you know what's smaller like a hundred a thousand i mean it kind of depends right it depends on the load of your system and all that um i mean i've definitely done it with thousands of records, and it performs fine.

That's going to be manageable. It will be manageable. So just to be clear, then, the way this CTE is going to work is, let's say you have with employee hierarchy as, and then you'll do a select star from, uh, what was your table? Employees,

right? Where, uh, employee ID equals one. Yep. Right. Your, your first guy,

or let's say we're manager ideas. No, let's get, let's get the top level guy. Okay. Where it's no, okay. Where employee ID equals no union all select star from employee.

Uh, what did I name the CT originally? CTte employee hierarchy yep okay so select star from uh employee where uh employee manager equal employee hierarchy dot uh employee id yep right Right? So that second portion of that second select statement in that union all is going to call back on the CTE itself, right? So it's going to recursively call on it.

And that's going to be fine in your smaller data set example. Yep. Or it can even be a large data set within that table. But depending on what you're doing with that across other tables that you're joining to and that kind of thing, it can get really big, really fast, and now your performance can really degrade. When you say performance, you mean CPU?

We're talking memory, everything? All of it, right? So it could be your CPU. So here's the thing. You can index these columns the the one great thing about the adjacency list is it's super easy to index right you got two columns you have your employee id and your manager id so sql server can utilize these indexes well i mean there's other there's other ways to solve hierarchical data too where you could also

index it nice so i wouldn't say that's necessarily the benefit of adjacency list well so the fact that it's simple though is what i'm saying is right like you have your parent-child relationship in one table when we get a nested set models though those are pretty simple things that you're going to index building them not building no but we're talking about the indexing though okay okay for the performance part because what i'm thinking like when when you talk about the employee id and the manager id and the benefits of the adjacency list immediately i'm thinking of the fact that you you could have integrity on those columns now yep very easy yep oh question yeah cyclic data oh if you have something that references something up above yep so here's the thing with something like sql server i think it has it has a max recursion

yeah max recursion of like 99 it might be higher than that i can't remember so it won't totally kill the box but really it's invalid for this you're not gonna be able to model this with this approach no actually what you would run into anyways is that would eventually end up breaking you because yeah if you have something if if you have a record five down that references a record that was three up from it then yeah you're going to start getting into this recursion so essentially you're just going to start getting back a ton of data that doesn't make sense right so yeah that's a problem but that's actually a data problem right that's an integrity issue and well it depends on your data you know for for like a manager hierarchy that would be a bad thing but well is it might

be well i mean i said there are some there are some uh management chains that are called flat right to where people can report to multiple different people so yeah or like checks and balances like the legislative branch checks the judicial no i'm thinking of like the one the more popular the the most well a big known one was blackberry when they there was a period of time where blackberry had two c two ceos yeah right so the whole company like it's starting at the top there were two guys. If you've got two parents, this is right out the window too. But I think those are going to be problematic though.

When you get into circular data structures like that though, we're talking about hierarchical. I don't know if you heard, Joe, but at the beginning of the show, we said we were going to talk about hierarchies. Now you're talking about circles. I think't know if you heard, Joe, but at the beginning of the show, we said we were going to talk about hierarchies, not circular circles. I think that's a different show.

That's the graph database show. Man, it's so difficult being remote. You can't see that we're talking about hierarchies. Yeah, you guys can't hit me anymore. Start throwing things across the table. So, yeah, I mean, here's the thing. We do get into the CTE, so when we were talking about the performance and all that,

if you think about what SQL Server is actually having to do behind the scenes, I'm sure they've built in optimizations. But in the old days, before you had things like CTEs, essentially what people would do is they'd write procs, right, that would then fill in temporary tables, and it would loop over and say, okay, go give me all the children of this. So, hey, select star from me all the children of this. So, hey, select star from employees where manager ID equal this.

Then you get down another level, and then you'd step through every one of those and say, all right, give me all the employees that report to them. And so you kind of fill this in. Behind the scenes, that's really what SQL Server is probably doing, is it's saying, okay, give me all those parents or all those children. Now give me all those children. And so it's one at a time stepping down the hierarchy. So if you have a really deep tree and if you have a wide table and by a wide table, you have a ton of columns in it, these queries get expensive, especially if the data sets split across pages. There's all kinds of things that can happen there, but the

deeper you go, the more recursive levels there are, the more expensive it is to actually run that. But on the plus side there for a JCC list, they're easy to implement. Oh, super easy. Especially if you have an RDBMS that supports CTEs.

If you have a database system that does not like MySQL, then you end up building something like what we just mentioned, where you're filling in some sort of temporary table, some staging table to where you can pull the data out with an adjacency list. Well, not only is the implementation of it simple, which is easy for me to say, the CRUD on it is also easier, too. Super easy. It's like moving data around, deleting data, inserting data into it. Those are all relatively cheap operations compared to other hierarchical data implementations. Yep, absolutely. I mean, moving somebody from manager one to manager two, you basically say, hey, update employees set manager ID equal to two where employee ID equal five, right, whatever.

So super, super easy. And you're done. If you do that, you're done. There's nothing else to look at, nothing else to touch. I mean, chances are if you're writing any code where you even touch the database, you even look at nothing else to touch i mean chances are if you're writing any code where you even touch the database you even look at the database you've probably already seen data that matches this model yeah like you you already know about it yep i mean uh old old uh like uh not dashboards like um what can i think forms this is how the commenting system would work in a lot of forms, right?

You'd have common ID equal one. If somebody replied to that, then common ID twos, parent ID was one, and so on down the list. And that's how a lot of commenting system works. Heck, I think even if you look in the WordPress database, you'll actually see those type of relationships, right? This particular piece of content belongs to that piece of content, belongs to that piece of content. So it's a very simple way to structure a schema for representing a hierarchy. So what are the problems with it? So we mentioned that large trees are going to cause problems for this.

If you're dealing with large data sets, then this might not be your guy. That's really the number one thing that comes to mind for me as far as problems for this. Yeah, and

here's another thing I will say. Even if you're not going down a ton of levels, even if it's something that you are constantly querying that way, it can get expensive, right? Oh, that's a great point, too. Yeah, so you kind of need to look at the frequency of it because taking the hit on it once or twice may not be a big deal,

but if every single query you have, and I'll give a good example. If you have, let's say you have... What if it was a product catalog? Right. That's a perfect example. If you're constantly having to query down the categories every single time you're going after products, that gets really expensive because you're now crawling that, that, that tree on every single query. And if you've got a lot of people hitting that,

I mean that, that can really start running into some performance issues. Okay. So, um, I just to make sure I understand this, I wanted to kind of sum it up. Um,

so would you say that an adjacency list is one strategy for solving the hierarchical data problem where each record keeps track of its parent. Yep. Works for me. Cool. All right. So I think we've got adjacency list down. It's a fairly simple one.

You've seen it before. But we wanted to go ahead and lay the groundwork before we moved on to any other, because we wanted to, you know, this is going to be the first of a few episodes where we wanted to talk about different hierarchical data models, right? And so we need to lay some groundwork out, so, or some, you know, framework out that we can all talk on a common language here. So I think we got adjacency list, but before we move on to the next model, I do want to say that, uh,

you know, if you haven't already, we would greatly appreciate if you would leave us a review. Uh, you can go to coding box.net slash review and find your platform of choice, be it iTunes or Stitcher, uh, you know,

or whatever. If you know of another platform that you like to findunes or stitcher uh you know or whatever if you know of another platform that you like to find us on and uh you want to leave a review there hey by all means please do and let us know about that that platform because you know there's there's probably there's a ton of aggregators out there that we're not aware of so yep um uh sorry i'm being distracting on camera he is being distracted there we go i figured if i took a selfie of myself sitting here then i could just kind of tape my phone to my webcam and go to the bathroom interesting all right so maybe the poll for this one should be like how how do you think this remote uh gig is working? Should Joe move back to Atlanta?

Yeah. I'm going to go ahead and vote. Yes. Yeah. Yeah. And if our sound quality sucks this time, you guys should let us know. We'd appreciate it.

Blame Joe. Yep. All right. So the next one, what's the next one we're going to look at? So as we, as we step into our deep dive here for uh nested for hierarchical data models the next one that we want to talk about is nested set models right you want to give us a a brief overview or you want me to dig into it i will try and give

a brief overview i think we brought this up previously as well um but yeah i did want to say we did talk about this in episode 13 at around the 44 45 minute mark but uh there was still some some i don't know that we went into it as deep as we probably could have right yeah i mean it definitely wasn't centered around hierarchical data sets. I think it was kind of a mention in passing. So essentially what it is, is this. If you tried to take a hierarchy and flatten it out to where if you thought about everybody that was underneath something, let's talk about employees and managers again, because it's something people can kind of relate to if if you were to just take your arms and wrap them if they were big enough to wrap around all the people under you that's kind of what the nested set is so essentially if if you envision that everybody has a left and a right all right and their right is always one more than their left if they are the only person at that level so let's say you're at the very so you kind of need to picture it from the bottom of the tree first right if you're at the very bottom of the tree let's let's say that your id is 20 hold on

before you go down that path though yeah because because i already see like there's some confusion and i think i got a better way to visualize this okay right like let's get out of the the employee uh mindset for a moment and let's just talk family tree okay right so so if you're building your own family tree and let's say that you're making the smallest family tree you could possibly make and being lazy and you just start with you and your parents okay so this left right business that alan's talking about basically what you're going to do is you're going to start at the, the top of the tree, right? And you're going to go down every node and count it. And then when you get to the bottom of a branch, you're going to start walking back up and continue counting, right? And then when you get to where a branch forks,

then you're going to go down that branch and continue counting and you're going to come back up and you're going to keep doing that until all branches have been visited and every node in the tree has been visited at least twice. Okay. And this is where the left-right value part, Alan, comes in. So as you walk down these nodes the first time, you're adding or you're setting a value for the left side. And then as you start walking back up a branch is when you set the right value. Okay? So in this very shallow family tree version, then if I start with myself, my left is one. And then let's say I start with my mom, her left is two because I don't go any further, right? Her right is now

three and I come back to me and then I go to my dad and his left is four. And because I'm not going any further down the tree, his right is five. And then I come back to me and now my right is six. So I have a left value of one and a right value of six. My mom has a left value of two and a right value of three. And my dad has a left of four and a right of five. Yep. Okay. Now, that is as simple as I can explain that, but it's hard to appreciate the value of it just yet. Yeah. Now, if you were to make this tree bigger, right? Let's say that we decided to include my mom's parents in this tree. Then as I, let's start over with the counting.

I start with myself. My left is one. I go to my mom. Her left is two. I go to her mom, which would be my grandmother. Her left is three. She's now the end. So her right is four.

Go back up to my mom. And we go back to the next branch, which would be her father. So his left is five, right six. And then come back to my mom. Her right is now seven. Now we come back up to me. And then we go to my dad to calculate his left of eight, his right of nine. And now my right is 10. So now you can see that when there was just three people in that hierarchy, my left was one and six, but as soon as we added two more,

my values went left of one and right of 10. Yeah. And what we're going to do to help illustrate this is we're going to put an image up on the show notes for slash episode 28 so that you can actually kind of see what we're talking about here. We'll link to some. There's already some that are made. I'm using a family tree as an example just because everybody can relate to that. That's an easy one to understand. But yeah, there's some, particularly in like the Wikipedia article, if you look at this, where they talk about product catalogs as an example for how you might use this. But the point that I wanted to illustrate though is just

that before you went too deep into left-right, that really all that is is i consider it and and i haven't actually read this anywhere and i'm curious to hear your take on it but um i consider this to be the expense of walking the tree yep that that's what those two values represent the left is that you walk that tree every time you insert or update yeah yes the left well okay hold on the the left is that you walk that tree every time you insert or update yeah yes the left well okay hold on the the left value represents the the expense of walking the branch the first path down that branch right and the right value represents the expense of walking all of the brain all of the sub branches until you got back to that node. Now, as you already kind of hinted at, though, and as you might have noticed when I introduced just two more people in that hierarchy, I couldn't just, I had to start over with my

counting. I couldn't just easily insert that in. So those would be extremely expensive operations especially in large data sets yeah so so notice what he said his first when when he was first just including his two parents his left was one his right was six right when he introduced his grandparents his right bound completely changed at that point but that's not just the important part so did all the left values for everybody under or after his mom after the first node was visited after him every other node at that point changed for its left and right values

so he's he essentially had to recalculate the entire tree at that point. Right. Which means that if I wanted to remove my grandmother from that list, she was the third node I went to. But if I wanted to remove her, then she would change the values for my grandfather, my mother, my father, and myself. Like the entire tree, everybody had at least one value, whether it be the left or the right or both, changed. Yep. So that's why typically in implementations where Ness's set models are used,

you wouldn't want to bother trying to even maintain doing those type of CRUD operations. Instead, you want this data to be throw away you create it you save it into a table and when you're done with it you throw it away and you rebuild it yep it's it's one of those things to where it's probably not if it's for mission critical time critical applications where data is transactional and changing you're not using this this particular model because it would be too hard to maintain however if it's something

that's not like to the minute critical then if it's something that you can build every hour every two hours something like that something you could do in batch something that you can do in batch that you can basically let it run do its thing and be done with it this is when you use the nested set model but let's go into some people be like well why would you even do that right because we've only described like bad things about it right like even even me describing it to you like hurt my brain a little bit just saying it and i already knew what it was right if you've never ran across this before then i can i can only assume that you would ask like well it sounds horrible i i never want to do that and and here's the thing too a lot of times um the the interesting part of this is

it typically typically relates back to the um uh what do we call it? The adjacency list. So just to give you an idea, this is usually something that you build off of an existing schema, like an adjacency list. This is not something you start with. The nested list is almost, I'm not saying it never is. I'm sure somebody out there has spent a lot of time writing crud operations to make this work, which seems somewhat insane to me. But typically, the way that this is used is you take an adjacency list where they already have a hierarchical model built, and then you build this nested set, and we'll go into the reasons for it.

Might as well right now anyways right like what's yeah it makes sense to me like if you want to know the ancestors of michael you just take that left to right values and say give me everything in between and you're done right well more specifically let's drill let's talk about like where uh if you wanted to find only the ancestors or you know um you know the the lineage of my mother in that example, right, her left value was two and her right value was six, right? So you could – No, it was six. Yeah, because grandmother was three left, four right, five – oh, yeah, it was seven.

Okay. Yeah, real-time editing. yeah i'm sorry so my my mom would have been two and seven and then you could do a search where the left value is greater than two and the right value is less than seven and then that would immediately return back the anyone in the tree below mother right now here's the here's the big advantage of this right like they are um you know depending on how much data you're working with you know it does it is a little bit more involved to uh create this data now that doesn't like you could you can have hundreds of thousands of records and still create this data now that doesn't like you could you can have hundreds of thousands of records and still create this data within like you know 30 seconds right so that might not sound horrible

it is horrible if you were trying to do that inside of a web call right so you wouldn't want to do it then that's why you'd want to do it in batch. But, you know, so you could build that all up front, but so, you know, you take this relative term of it takes a while to create, but once you have this data created, the querying of it is ridiculously fast. And easy. To get really large data sets back of these trees, right, of, you know, these sub- sub trees or start at the entire tree, like whatever portion of the tree you need, right?

You could real quickly get to all that data. And because we're just talking about simple integers, right? Or depending on how large you need, you know, you can easily index these, which is why I wanted to comment on large you need, you can easily index these, which is why I wanted to comment on the adjacency list. That's not necessarily just specific to that. So you can do some nice indexing too and really get your query performance on it. So what you would typically do when you implement these nested set models is you'd have

the data. And like Alan said, you know, you would have going back to our employee example, you know, you'd have that employee table and built into that. And, you know, each one of those records is the adjacency list where every employee knows its manager, right? So there's a column called manager ID, right? You would have typically this nested set model that, that output in its own table, right? And there would be a foreign key relation between the employee table and the hierarchy table so that you could join those back and forth. And so that you could say like, hey, where the employee, you know, join on the, where the employee ID equals the hierarchy's employee ID and then find where the values for left match whatever you're looking for and the values for right. So if you wanted to find, you know, if you wanted to find everyone

in the tree above someone, like, you know, going back to the example where I'd said that my mom's value was, uh, two and, uh, her right value was seven or let's go. No, let's talk about, let's use the grandmother in that example.

Her left was three and her right was four. Well, hold up, hold up before we go that far. I think there's one more piece that we need to add in here. So we got the left and the right. Okay. That tells us what we

encapsulate right like what what our reach is we'll say there's also one other piece of information typically when you're doing the nested set model that you add as well and that's the level so so as you're walking down and and you're in on and we'll go into why you do this in a minute but i wanted to add this in here because this is where the querying becomes easier so right now mike was at the top so we'd say his node level was one or zero depending on what your ordinals were his mom is level two his grandmother and grandfather are level three his dad when it started coming back up the tree now is also level two all right so we have nodes one two and three node levels one two and three and this is important in a second when we start getting into kind of where he was going with this so so now each record has a left a right and a level we'll call it all right and so now let's

go back to where you were talking about so you're going to query from your mom or your grandma well yes i was with grant i was thinking the grandmother because then you're going down like three deep right okay so her left was three her right was four right right so before we were saying where the values were greater than but instead we could say like where the left is less than three and the right is greater than four yep and that's going to walk you back up the tree right and so that's going to find that would return back my mother in that case and myself in that case and this is where the level comes into play but the level wasn't necessary it is so here's why so if you were going to do something like um say hey tell me everybody that is a direct report or a direct um parent of

mike the easiest way to do that is yes yes i see where you're talking about and even going back up even going back up because you could say hey who were her parents now you would just say hey we're node level equal no level minus one and left is uh less than that left and right is greater than this right and then that way you're just going up one because if you just said less than it's just what you said a second ago if you'd say give me everything where the left is less and the right is greater that's going to give you everybody above her well i i okay i understand what you're saying and and i don't disagree with it but the only reason why i wasn't bringing that in immediately though is because it's not required right like you could implement you can have the nesset set model without that level yeah you could you could

have that you could have the nested set model without it. I don't think so. It's just depending on your listing specific kinds of queries where you wanted to go at particular levels. And if that was the case, then yes, it would absolutely be crucial. I don't know that you could do some of these queries without it. And the reason I say is because when you start doing the left less than and the right greater than, there's no way to narrow it down to just one level above you.

Without doing some crazy joins. But again, if that's the type of thing that you needed to do. If you didn't need to do that type of query then that level isn't is irrelevant and that's why i'm saying like if you only needed if you only needed to build a model where you either need to go down the tree from where you start or from up the tree from where you start then who cares about the level but if you say that you only want direct reports or direct parents we'll see but and i'm saying i didn't list those examples but no but but that's why I'm saying, like, typically when you are doing a nested set model, you want those three key pieces of information.

And the reason I say is because without the level, you are now hindered to what you can do. You can only go all the way up or all the way down. Yes. You can't say, I only want his first two levels, or I only want his first level, or I only want the first two. Oh, you can do some math on that. And that's what I'm saying. On the left value.

Now it becomes extremely expensive, and your joins are going to be crazy, too. Okay, yeah, fair enough. I'm not discounting this, but what I'm trying to get at, though, is the core for the Nessus Set model is the left and right. Yes. core for the nested set model is the left and right yes adding the depth to it or the level or whatever you want to call that is a variation on this that definitely makes it far more beneficial depending on what you're trying to do but the root core of what's required for the nested set model doesn't require that right it doesn't okay that's the only point that i was trying to make and that's why i hadn't brought it up yet but okay but now to where you're talking about though now

you could do uh some some more advanced queries like what you were saying yeah i mean and i have we i don't even know that we've so yeah we somewhat talked about the pseudo query on it, right? Like if you wanted to do it, you'd say, hey, select star from, and let's say that you call this employee tree, right? Or something like that. Because you still have your employee table, which like we talked about earlier, or not employee, let's stick with the employee because it was already there. You have your employee table that had the manager id and the employee id right if you're going to create a nested set table you're probably going to call it employee hierarchy or something like that right um and then in that table what you would do is you'd say hey select all from employee hierarchy where you know employee id equal one and then you would join back to itself and say join employee hierarchy

where left is greater than uh employee the the parent employee we'll call it it's left and or you just do the between right we talked about this before you can just say between where left is is between um the top employees left and right um yeah you lost me a little bit there okay go ahead no i'm trying to i was trying to like so if you're trying to find everybody that's a child of that top level, right? You can say, give me everybody where their left is in between my left and my right. Right. And that'll return back to tree blood that are returning back to tree below you. So it's a very simple query. That's another part of the nested set that is actually pretty awesome is when you first put it all together, it's kind of hard to look at it and it doesn't make a lot

of sense. But once you actually fully understand what you're, what you're looking at, which is a flattened hierarchy is really all it is. When you do that, querying this thing becomes easy. Hey, give me all the left values below me. That's going to fall in between my left and my right. If you want, if you want everybody above, you can say, hey, give me everybody above where their left is less than mine and their right is greater than mine. Right, to get the entry above. Yep, and it's a self-join back to itself, to the same table. So you would join employee hierarchy to employee hierarchy on these type of predicates that we're saying where left is between, you know, this employee's left and this employee's right.

So it's, it's pretty simple to query, which is one of the big things. Now building this table, I think you did one recently. Yeah, it's a, it's a bit of an exercise,

right? It's definitely, it's definitely a fun little challenge. Uh, if you haven't done one in a while but the um yeah it in and again like let's exclude where joe was trying to get crazy with us on the circular crap. Which breaks it completely. You can't even do it, right? I mean, circular is going to be problematic for a lot of hierarchical... Just about anything. That's why they're called hierarchical.

But how do you prevent that? Right? Like, you know, if my query for kind of recalculating this stuff on an insert or update breaks, you know, there's no sort of constraint that I can really put in the database, right, to prevent this from happening.

No, you could. You could in SQL Server. You could create constraints that would not allow that. You would basically create, I believe, you can create a check constraint in SQL Server and you could write... How many levels deep would you go? You could write a CTE that would be that check constraint.

So it's going to be fairly expensive. I'm not doing that. But it could be fairly expensive, right? But what I'm saying is you can actually create a check constraint. So it's going to be fairly expensive. I'm not doing that. But it could be fairly expensive, right? But what I'm saying is you can actually create a check constraint, I believe, that would allow you to query the hierarchy and make sure that you're not inserting anybody way down here that's referencing somebody that's going to put them into some sort of circular loop. That's kind of hard for this model because it's almost like I need it's almost like I'd need to kind of disable that restriction while I'm

doing all these individual little updates in a transaction. And then I need to kind of put that constraint back on. Right. Cause like, as I'm kind of going through and rejiggering in this, unless I, you know, wipe out all the columns,

that's what you usually do. Again, this will be, you, you wouldn't even have this, this data. You would not want to be attached to your main table. You want this in its own table because every time this batch runs, you will be blowing that data away and restarting.

And so if you had that as columns in your main table, then it's going to decrease the performance of it because you're going to end up locking that. All the time. Yeah, every time this batch runs. But if you put it into a separate table, then you could do all your work in memory and then at the very end just trunk it in the table and dump it in. And let's be clear. You would never, ever want to try and recalculate these things on the fly.

I've heard of people who have tried to put enough space in between left and right, like pad the left and right. Like 100 in between each one. And then you can kind of fill in the spots. Exactly. So like what Outlaw was saying a minute ago where he was one, his mom was two, his grandma was three, and then so on back up. Instead of that, he would be 100, she'd be 200 and then 300 yeah but then you still take a hit because you have to have something on your constraint to be able to verify like hey have i gone beyond yet because if i have now it's time to take the big hit and redo this and that's the thing and to take it

even further now the logic you have to build in to actually determine when you need to rebuild this thing if it's not just going to be some sort of timed thing it's going to be insane so basically what i'm saying is you'd have to write incredibly complicated code to manage an update on something like this whereas typically what you do is in sql server like a way that you would probably go about this is you do a select into a new table so so that you didn't lose any any gap of time in between when this thing's being built and if you wanted to swap it out like a strategy that i've seen done is you select into a new table and then you would do something like an sp rename to where it's

almost instantaneous you rename the old table to underscore old and you rename your new table to what the old table's name was and then that basically swaps it in immediately um and let's let's take it back to it the two primary reasons for building this table are one performance and two easy to query right i mean that's, there is one other, you know, another downside to this that we haven't mentioned yet, and that's that you don't have the referential integrity that your adjacency list had. Right? Like, these left-right values are just calculated values.

They're meaningless outside of that table. Yeah. Like, even that depth column that you talked about like if you did go with left right and the and the depth none of those mean anything other than what you know within this table they don't that's their only place where they provide value yep so and let's be clear though they are extremely are extremely useful. Like, if you need performance, they are incredible. Oh, yeah. The Nest Set model, you mean?

Yes. Yeah, yeah. And also, too, I don't think that we mentioned this, but the guy who named it that. Joe Selko. Yeah, Joe Selko. He's the one with giving credit for having introduced the name Nested Set Model. And we'll have some links in the show notes to it.

Once you see some of this imagery to back this up, it'll lock in. It'll sync in a little bit better because i can understand how like if you're hearing this for the first time this is going to be a tad uh much to understand so that's why i'm trying to be i'm trying to explain it as well as i can and i hope i've done a good job of that we'll have some good pictures in the show notes yeah yeah i mean we'll have to it's but i mean like everything we do on the podcast all this stuff is is kind of hard to completely visualize but hopefully this will give you something to go look at be like oh if i run across this problem in the future i have

an idea of how i can solve it right um i've used nested set tables in the past and i definitely did them to where they were processed like every few hours or whatever and in situations to where you know you needed to see things like employees or even products or things like that because the performance gain that you get out of it is absolutely fantastic now if you have bad data it can cause you problems right like if your referential integrity on your on your real table that contains that data doesn't do things like check for circular dependencies or there's gaps in the data, now you have to figure out what you're going to do with it, right? Like typically you're going to look down from the CEO and then you're going to have that manager and then their manager or

whatever. But what happens when you have a break somewhere in the middle where this guy's just kind of floating and he doesn't report to anybody what do you do with that data right oh well this is a good thing that we haven't mentioned though is because that this by having this data in its own table right there's nothing to say that you can't have multiple hierarchies in that table right so if you're as you're creating these um nested set models right uh or as you're creating these nested set models, or as you're creating the data for this nested set model, depending on your data structure, you might have multiple people who are considered the top of their respective tree,

and you could have multiples of those trees. Yep. That's why tying it back to the original table with a foreign key is going to be crucial in that regard. So like in the employee example that I gave where you would have an employee ID in this hierarchical, I think you referred to it as the employee tree table. So you'd have an employee ID column that was a foreign key back to the employee table. Yes, yes. table yes yes um now one thing this does not do though and will not allow you to do is you could not have a child be a child of multiple parents all right no so that is there are other situations

and other solutions that we're going to talk about other models and well that does kind of confuse the family tree example that we started with uh okay that's flipping the tree that's on end yeah so the way that he actually created the tree where he was at the top kind of surprised me because it sounded like he created his parents but but no if you were to take it from his grandparents down you'd see that so like i could not report to both michael and joe in this type of situation unless you have the tree upside down but but then you'd have the inverse problem too um but that is one thing that this particular model will not support is a child can only be a child of one parent it cannot have multiple parents there are other models that will allow

that but that's just something to be aware of yeah we're gonna we're gonna discuss like i said we're gonna discuss some other models um you know coming episodes. So we look forward to this as being a fun topic to be able to dig into. And by the way, family trees start. You start it yourself and work down. I've never seen a family tree like that. I thought the tree was always like the branches came out. Those were all the kids and everything, right? Yeah, they go down.

Like when you draw a family tree. Okay, maybe I'm just crazy. I don't know that I when you draw a family tree. Okay, maybe I'm just crazy. I don't know that I've ever drawn a family tree, so I'm probably the worst person to ask about that. All right, so yeah, there's some other examples we're going to get into. Materialized path, closure tables. We're going to come into these in coming episodes, so we're really looking forward to this

yeah and I think this is all really useful information if you ever cross these paths you'll know some of the pains that you that you hit doing this kind of stuff it looks like we lost Joe yeah I don't know what happened there

we've had our first technical difficulty on the show, which is not too bad an hour and some odd minutes in. Yeah, somebody reboot the internet. It's down. All right, so technical difficulties have been resolved, and we're back. Yep. So I just wanted to recap.

The adjacency model is basically where a record stores its parent ID. And it's a simple model, but the problem is that it scales poorly. So as the data size grows, it gets slower and slower. And to contrast it with what we've just been talking about, the other solution we talked about tonight is the nested set model, which is much easier to query, much faster, much more elegant. But the problem is that you have to maintain that data so it's harder on inserts and updates but it's much quicker for actually querying that data uh i agree with most of that except with maintaining the data you would just

blow it yeah yeah well and you just can't do it that often right well you could i mean it depends on your batch yeah it could i mean so like like outlaw said i mean we've had situations i know he's done it recently and i've done situations where i had you know hundreds of thousands if not close to a million records and you can do it in under a minute yeah i mean i had it down to like like within the teens i think in seconds yes oh no no. Page load. Page load's got to be less than a second. Right. So you wouldn't do it on the page load. That's why you'd batch it. But you might do it every ten minutes. You might do it every five.

So it's not anything that you're actually going to be dependent on a user interaction. This is going to happen in batch behind the scenes. And then you're just going to use that table. For my purpose, I was able to do it once a day. And that 16 seconds or whatever, yeah, you wouldn just going to use that table. Yeah, for my purpose, I was able to do it like once a day. Yeah. Right. And, you know, that 16 seconds or whatever, you know, yeah, you wouldn't want to take that in a web request,

but 16 seconds, you know, at 4 a.m. in the morning, who cares? Yeah. Right. Or even throughout the day, right? If nobody's actually using that code, it's just happening, and then you get to benefit by using the table. Yeah, and we'll have, like I said, we'll have some links to benefit by using the table yeah we'll have like i said we'll have some uh some links to this stuff in the show notes and and um you know some imagery to

to help describe it so be sure to check out the show notes and uh yeah so let's get into the resources that we like yep so um one that i had found a while back and kind of what sparked a little bit of this was on SlideShare. There was a nice little thing that a guy put together that was literally several different types of models for hierarchical data. And we'll go ahead and link to that one. And then one that I had found years and years ago was Joe Selko's sequel for Smarties. That's a book that's available on Amazon. And it's really good.

I mean, if you want to get some mind benders, this is, I think, where the first nested set model thing came into play. So, or at least one of the first references that I'm aware of. So that's another reference. Yeah, and there's also a link that we'll include to Stack Overflow

where it talks about different options for hierarchical data, too, that gives a really nice, concise overview of different models. So we'll include that as well. All right, so now it is time for the tip of the week. Oh, yeah. Let's get into it. Tip of the month. Whatever. yeah. Let's get into it. Tip of the month. Whatever.

Why do you got to do that? Why do you got to be like that? It hurts. Okay. So here's the thing. Like, how well, be honest with yourself now, how well do you recall all of your big O notation? Totally.

Like, not just all the variants but also like the expense of it which one's better than another which is worse which is best like you know average you know do you remember all of that right i remember the time complexity there's a thing called big o there's a lot of it and and it is And it is a bit much to try to remember all of it. So especially if you don't use it regularly, I mean, I don't know how much you talk about Big O notation with your spouse, but it comes up every now and then. Right. But if it doesn't, then this is where you have the big O cheat sheet.com. It's awesome.

And this is really cool resource, uh, that you can go to and it, it maps it all out. You can see which ones are the, you know, average performers, which ones are the worst performers,

which ones are the best performers for different, um, it gets into it like for data structure operations, array sorting operations, graph operations, like heap. I mean, there's a bulk of different ways that it talks about, you know, which operations perform better and gives their big O notation for each of those operations. So again, big O cheat sheet.com. And we'll have a link to it in the

show notes. Yep. That's most excellent. Yeah. And, uh, the one I wanted to mention is actually, um, from, let's see, they've got a pronunciation guide, uh, dev V us dot has your websites.net has a, um, a great setup for basically the shortcuts they use with their gaming mouse. So we talked about this a little bit in episode eight, you know, basically gaming mice have all sorts of crazy buttons and they've got a great diagram here. It's got maybe, I don't know, eight buttons on the side of their mouse and they show actually the kinds of things that they have hooked up to these different buttons. So you can press a single button to build, a single button to step over, step into, stuff like that. So this is really nice.

And what I really thought was kind of funny about it is if I tried to be honest with myself and kind of set up shortcuts for myself for things that I actually do commonly, I might have one for restarting Visual Studio. I might have one for... I can go ahead and add and commit with a message of I don't know why this works you know

uninstalling ReSharper exactly it hurts to even make the joke troubleshooting my wireless connection how did you say they pronounce this because if I look at their URL I got to hear this again yeah it's the first

syllable is the problem on this again yeah it's um the first the first uh syllable is the problem one it's like on their pronunciation guide it's d-i-h which looks to me like did but i always thought the word was devious yeah because when i look at their url it's definitely devious yeah it's devious so how do you pronounce d-i-h devious yeah i don't know well yeah this is wrong yeah their their url is devious oh wait your websites i think i just got it it's devious no but yeah there's actually underneath the pronunciation there's a definition for the word which is showing a skillful use of programming tactics to achieve goals, as in, he's as devious as a programmer needs

to be. Okay, it's devious. Alright. I hate that I just said it totally wrong, even though it's a pronunciation guide at the very top. Oh well. Anyway, it's an awesome

article and it's got great pictures and it's great idea. Excellent. So mine is, is because I ran into some issues or a couple of us ran into issues recently where we couldn't see dimensions in a cube. Oh my God. You go into sequel management. Hold on,

hold on, hold on, hold on, hold on, hold on, hold on. You can't, you can't bring up this.

We haven't even talked about MDX or cubes, not even once. And suddenly you're just going to throw this on us as a tip of the week. I'm going to because anybody else out there who runs across this will probably be like, oh, hallelujah, right? Yeah, their brains are melting too so if you have the misfortune of working with cubes and mdx and you have to look at dimensions and things like this ran across the thing to where i i get this query from somebody an mdx query that has these dimensions in it that i can't see in the cube browser in SSMS management studio. And, and it,

it just stumped, right? Like there's magic here. I don't know what this magic is. The answer is to get, uh, data tools. I forget what it was.

Oh man, you know, business, business intelligence, data tools for visual studio. And if you install that, you can then open up an analysis services database within Visual Studio,

and you'll have access to everything. Now, well, basically at that point, you're looking at what it takes to build the cube. Yeah, so for clarity though. All of them. So for those who aren't familiar with it mdx is a multi-dimensional why don't you know what the x stands for query i don't know no i forget what the x stands for but it's a multi-dimensional query language yeah and it's disgusting it's

it's gross and and if you wonder like what's a multi-dimensional query language um excel yeah it seriously i we're not going to go open up power open up microsoft excel and now imagine you know you have you have your table as your worksheet but then there are multiple worksheets within that file you could have multiple workspaces or workbooks is what they right right right you could have multiple excel files and they could reference other ones like it's it's truly a 40 space but the key here is if you're not able to see all the dimensions that you think you should if you open up the cube inside visual studio using that plug-in then you will then you will be able to see all the dimensions and everything else that went into building that cube and there was a trick too with this with this plugin uh that you should probably mention which is if you already have

well you probably already have sql server uh tools installed but it during the install it's going to bring up your standard looking sql server install guI and it's going to prompt you hey do you want to install any kind of add-ons to an existing instance or do you want to create a new instance and you're going to be tempted to say hey I just want to add on to an existing instance and because all I want to be able to do is add in this ability to view cubes but that's not going to work yeah you'll need to uh specify a new instance and even though it sounds like it's going to create a new instance it's actually not yeah it doesn't it's complete garbage for ui for installing i think that's just the cost of uh reusing the uh the

regular installer yeah yeah but um yeah pretty cool pretty cool information uh frustrating that you have to find out that way but you know yeah oh oh oh i have one other tip okay only because he has now witnessed the greatness that it is a 34 inch ultra wide monitor what is your takeout law i don't know what he's talking about i think think he's going to try and steal it tonight. What? You don't even have one yet. He's going to get clubbed on the way out the door. You don't even have one yet.

I don't know what you're talking about. You keep talking about wanting to buy one because you're jealous of mine. It's amazing, right? You're just jealous of mine. Hey, what did you do when you sat down with Visual Studio on that thing? Well, I did what any self-respecting developer would do, and I started tearing off tabs. Yeah, I saw you started nesting.

Started setting up your own little bat cave. Dude, he had four vertical tab groups on that monitor. Man, it's a beautiful thing. Yeah, I've never seen someone have so much fun setting up vertical tabs. He's like, look, I'm going to add a third. Now I'm going to take away. I'll put two more up. True story.

I don't know why. I'm calling you out. Hey, I'm telling you guys right now, we're going to put another. I'm going to link to it again in the show notes or somebody's going to. That monitor is glorious. There's no other word for it. It's pretty spectacular. I'm not going to lie.

There was actually – I'll include this in the show notes. There was a Lifehacker article where it was talking about should you go multi-monitor or ultra-wide monitor. And they made a pretty good case for going single ultra-wide. And you saw it. It's pretty amazing. Yeah. I mean, it's awesome. It's pretty spectacular.

I'm not going to lie. All right. So that's it. All right. So we talked about two ways that you could possibly deal with hierarchical data and you know the adjacency list and the nested set model and uh like i said we're going to be getting into more in future episodes so uh you know uh we hope you enjoyed this one. Let's see.

No, we need to add a poll. What should the poll be? Okay, wait. You go on. I had an idea for the poll. Me go on. Yeah, yeah. That one's a blog post.

What was my blog post? I thought you wrote a blog post talking about this, and that's how this all kind of came to be, but now I'm not so sure. Well, I did write one about product hierarchies and categories and it's actually surprisingly one of our more popular articles on the site which is shocking because I've

given it no love since then but so I meant to actually put this in the resources we like section but apparently it's past my bedtime or we don't like it it's one of the two um so yeah uh that's pretty much it for that so um what about for the poll uh how long are you in programming oh yeah so let's do this on episode or uh cuttingblocks.net episode 28 if you go there we're going to have a poll on there, and we're

going to ask, you know, how long have you been programming? What's your experience level? It's either that or another poll option I had, too. You can see I'm having so much fun with these polls. They just keep coming off like poll ideas. Another one that I had, too, was that should Joe move back to Atlanta? I think... Hold on.

I think that we need to change this from poll to survey. Oh, right. You're feeling a little... Okay. We'll stop that right there. The survey. Go check out the survey on episode 28, please.

That's... And subscribe to us on iTunes Stitcher and more using your favorite podcast app and be sure to leave us a review on iTunes Stitcher and more contact us with a question or topic leave your name and preferred method of shout

out website Twitter whatever and we'll be sure to mention you and visit us at Cody blocks dot net where you can find our show notes examples discussions, discussions, and more. Send your feedback, questions, and rants to comments at codingblocks.net. And be sure to follow us on Twitter at Coding Blocks. And any kind of corny jokes that you have, we appreciate them. Send them to us.

Especially late at night. Yes, especially late nights at Coding Blocks. Just do it. We have fun with it. So, yeah, that's it. Episode 28 is a wrap.