 I think we're good. There I'll go to the chat. All right. Oh, good. The correct number of people are in the chat. Well, the stream just started like a second ago. Keep it this way. All right. Got that going. Got that pause. I'll open it up over here. All right. Let's make sure I muted the yep. I muted all that non-sense desktop audio. That's what I had to mute. Okay. Yeah. It's Monday, June 25th, 2018. I'm rim and this is geek nights tonight. SQL and relational databases. Cause in 13 years, I guess we never talked about this. So I think we got a, at least temporarily a new bit in the show in the beginning. Cause you know, the format of geek nights, like we do our opening bit where we're like, Oh, it's warm today rained on me. I ate a taco last night and we do news. We do things that things of the day. Then we do our meta stuff. Then we do the main show. And then we do like the stinger nonsense. I got a new bit. The PUBG moment. Cause shouldn't we just do, we're going to do Tuesday on PUBG. Might as well just hold it. So I'm going to just tell one PUBG story cause I've been playing a lot of PUBG. So my story is how I indirectly killed a guy in a way that was so funny that I was laughing and because I was laughing, someone else killed me. So I started like the upper right corner of this map and the circle of course is as far away as possible. Time to run. Yup. And I'm at this like little tiny town that's on a road. There's no vehicles at all. Like I figure there's no way to run there in time. So I'm looking for a vehicle that I confirmed there are no vehicles anywhere near me except one. So I decide to run toward this next little town and hope there's one there. And if not, I was just going to die. But then I hear a noise and a dune buggy is driving from even further away coming into town. So I take a couple of potshots and this this individual, instead of just driving by like a smart person, right? Because you'll die from the wall that, you know, but they guess they want to get a kill. Yep. So they, they stop and get out and start stalking me through this town. So the circle is coming. So I take a couple more potshots and then I hide and I keep sneaking I sneak around away from this guy and eventually I have a clear shot toward his dune buggy. So I just ran for it and he didn't see me and I got in the dune buggy and just as I start driving away, I hear a ton of gunfire and he is behind me running and just emptying his clip and missing. And I left him behind because you don't get credit for that kill, even though what you should. Yeah, I should. The game has no way of knowing that you killed that person with the wall, but I was all gets credit. I was so happy about this that I was just sort of holding down on the road and laughing. So that didn't actually count as a kill on your stats. No, no, no, no. I did kill two other people in unrelated incidents. Big step up. I think so you've killed three people. So I've killed three people in total of two more people than I have killed. We got to play some more. Maybe I should just play after I eat tonight. I'll go home. I'll eat and then I'll just play until I kill someone no matter how long that takes. Even if I don't go to work tomorrow, maybe when we team up again, our new goal should just be our only goal is to kill someone and we don't care if we make it past that point. Yeah, they're their teammate can kill us. We're okay with that. Yeah, we just got to kill one. All right. So it's a tech day. It's not if we somehow kill two, we'll be like, and then some sniper will be like, what are those guys jumping around for? Badoop? Who are they TV? So I got any news on this glorious Monday? There's not a lot of Monday news, but I got this one. So this is actually a pretty big deal. I don't know. It's not that big a deal. Adobe is always if you go look at it, you know, people always pay attention to Apple and Microsoft or whatever, right? But when Adobe announces stuff, they usually announced these ridiculous features usually that make it to Photoshop way after they announced them or other software like Premiere. I remember when I was watching on Adobe thing a few years ago, and they were announcing the ability in like Premiere, you could like draw around a license plate in a video on one frame and to blur it out and that it would automatically follow that license plate in all the other frames of the video and it wouldn't have to go to every single frame and blur. And I was like, that's really amazing that they did that. And that was years ago. So a new thing that they announced is of course the people who made Photoshop can do this, right? Technology that can tell when something's been photoshopped and which parts of the image have been photoshopped. So now you no longer have to be able like, I see the pixels as soon as this software becomes available for public use, there'll be some tools and you could just load in and be like, yep, this tool says this was likely Photoshop. The reason this is so important is that the Adobe tools and in general media creation tools, there's sort of this, there's an ethical dilemma where if you make certain kinds of editing too good and too accessible, it makes it very easy to make convincing fakes that can, as we saw in the 2016 election, really damage society. There's a lot of, and the technology of making fakes, you know, there was that fake porn thing recently where they put celebrity spaces onto porn actors. Or even more, what about the audition stuff where you could theoretically have audio of someone and just change one of the words they said, it sounds totally natural. Now, normal person thinks, oh, I will use that to correct what I mispronounced that word in my podcast. But expanding brain person says, I will just use that to get people to say things they didn't say and then sue them over it. So, the ethical thing to do is to also create... Expanding super mega brain person says, I'll pretend I used that technology when I didn't. Yep. But the ethics of that kind of technology... I will say bad things and then claim that someone used that technology to make me say bad things when I didn't. I mean, how many conservatives are doing that now, but I didn't tweet that. Someone hacked my account. Exactly. But because there's been a lot of problems lately with technology being used to harm society in very direct ways, the ethical thing to do is as you design technology that obviously has possible nefarious uses is to also design at the same time the technology to mitigate that damage. If you're going to be a mad scientist to invent a poison, but that poison also cures a disease and doesn't kill people who have the disease, so we need to make this poison, you should also make the antidote for that poison in case someone gives it to someone who doesn't have the disease if they're dying, right? To just make the poison only would be bad. Mad scientist shit. Also, don't be like a cartoon mad scientist where no one can get the antidote. You should make the antidote more plentiful than the poison. If any one accident, you know. Well, don't be a super villain is really the answer. You can be a mad scientist, but the situation Scott described starts edging into mad scientist super villain. But mad scientists are not super villain. I imagine there's some okay mad scientist. They're just mad. They make like an apple replicator that just makes more apples and that's it. That's not mad. That's just a... That's kind of mad. What do you need that for? Apples... Feeding the world? Apples literally grow on trees. We have more food and more apples than we know what to do with. No, there's a lot of people who are hungry. Yeah, that's a distribution problem. Not a production problem. Yeah, but what if we can't put an apple tree where they live because it's not fertile, but we can put an apple machine where they live. Well, also apples are not enough to live on by themselves. Better than starving. Contrary to the Disney cartoon about Johnny Appleseed, apples are not alone able to sustain you. They'll keep you going long enough that you can grow something else. Maybe. You eat some apples. You won't be dying of hunger immediately. So now you can go to Maslow's hierarchy's next level and start making a farm. So in some actually pretty related news, there's some facial recognition company and the CEO made an interesting argument. Oh, I saw this. He basically said, and I'm going to cluster a bunch of... I'm the CEO of a facial recognition company. I know all about this technology, but trust what I have to say about it. There's a bunch of stories on facial recognition recently again, but this was pretty interesting because the head of this company basically said, law enforcement should not be allowed to use facial recognition because of the technologies built in biases and vulnerability to abuse. So he's sort of making this argument that commercial or even surveillance capitalist uses are probably okay, or at least they're neutral because they have both benefits and harms to society, but using them for government or law enforcement purposes is way more dangerous than it is valuable. All right, I'm going to make an argument, but you have to promise that you will not start discussing this argument. You will just hear the argument and the question I'm going to make and not answer it because it will take you hours. Okay, in return, we should do either a Monday or a Thursday show in the future on the tech transfer questions around facial recognition. No, this is a different question, but it's not a tangent. I'm just saying to get my promise to not turn this into an argument, you got to give me the show at some point in 2018. All right, here you have a person. Heard Scott said all right. And this person owns a company and they're declining to serve customers based on the industry that they are coming from. All right. So we obviously think this is okay. Yeah. And we think it is not okay to decline to serve customers because they are a certain color, gender, sexual. Okay. I'm curious where you're going with this. I won't start the argument, but I'm just going to point out that there's a very obvious and clear line if where you're going is where I think you're going. So what if someone were to do both? What if a gun store refused to sell guns to white people, but used this argument instead of a discrimination reason? Ah. Or just freedom reason. Law and precedent aside. Why are you answering the question? You promised not to answer. I'm not going to argue it. That's the end. Go and move to the next topic. Okay. I'll just let it be. I got things to say. I, I will hold up to my bargain. I'll just, I'll keep it in. I'll keep it in. It's hard, isn't it? So tantalizing question. In some other news that won't start an argument. So I saw this trending all over the place and it won't affect me because I'm going to buy a 4k monitor. I got to buy a new monitor. But what's wrong with your monitor? Oh, the other one. Yeah. The one that's that one. The one that's 16, 17 years old. That one. Also, this one. I think I bought that in 2002 or three. Also, this monitor, the only reason it looks okay is I've turned the brightness way up. This monitor is real old and real dim. I was actually watching on YouTube. It was some people who, it was like people recording a radio show, but they were also just live streaming them sitting in the radio studio. Yeah. With microphones. They had that monitor, the Samsung 17 2x. Wow. But Scott, this monitor is from, I think I bought this in 2007. This old Dell ultra sharp. What? That's the same ultra sharp bag. Yeah. The U2410. Yeah. It's like the best 1600 by 1200 monitor you can get. Yep. But I want a higher resolution monitor now. Well, they're all 16 by 9. I know. I'm going to, I have to give up on 16 by 10, even though it is the superior aspect ratio. Why? Just stick with lower resolution. Because I also need so many case for. I do need, I need more peas. You don't need more peas. I need a few more peas. I don't need. You know what? I've got two of those monitors, right? On my desk. And you know what? I'm planning to keep them for another, until like everything is 4K. Like all the videos. Oh, I'm real. It's because I edit a lot of video. More peas would make it easier. But I'm going to take this monitor. I buy a 4K TV before I buy a 4K monitor. But also I'm going to take this monitor, turn it sideways, stick it here and take this old Samsung monitor and give it its proper Viking burial because this thing is too old to suffer. Still works. Someone can use it. But the brightness is turned all the way up and colors just don't appear. I see colors. I see blue, green, red, white, red. There are still some colors on this monitor. There's some orange on there. That is true. But anyway, here's the problem. So with LCD monitors, you never want to run the monitor in a resolution that is different from its native resolution. I don't want to get into why because. Right. There's always some other way. For example, if I am playing a game and I need to be 1080p, right, for streaming purposes, I won't change the monitor to be 1080p. I'll just get the game to run and 1080p full screen. So there's black pixels at the top and bottom. Yeah, there's ways to get around it. But it's old monitors actually did have variable resolution. Like pixels could be smaller and it would be fine. But CRTs are ancient technology. Ready to talk about them in the modern world with an LCD. If the resolution that your video card is sending to the monitor is not its native resolution, meaning one LCD pixel equals one computer pixel, your monitor is going to look like shit. Well, you could use a resolution of the same aspect ratio and then it would still be one to one pixels. You would just have a lot of pixels that weren't doing anything. Yeah. Well, yes. If you also, if it downscales and you have a lot of blank space around it. Exactly. Or there'll be a setting in your monitor but the other problem is that even if you do that, yeah, it would be fine but then you're just wasting a whole chunk of your monitor. But even if you do some perfect aspect ratio like every four pixels in the square would be the equivalent of one pixel. It has to be one to one. The resolution you're right. One pixel of the actual, one physical pixel needs to represent one pixel in the frame buff. What I was getting to is that that still is not good. So what's happening now, this happened a while ago where suddenly everyone was making these 1920 by 1080 panels and that's why monitors got into the weird place they are today because it's cheaper for manufacturers of the core components to make the same thing over and over. So it caused pretty much the same in the modern era to make a 2560 by 1440 panel as it does to make a 4K panel. So what some companies appear to be doing now with a 1440P monitor is they just put a 4K panel in it and lock the firmware into downscaling. So the moral is either don't buy 1440P monitors or only buy a monitor from a high quality, that you know is a high quality model that is the advertised resolution. Or if you actually want to buy a 1440P resolution monitor you need to do your research to confirm that the monitor you're buying has a physical native resolution of 1440P. Because if it's a 4K panel in there your monitor is going to look like shit and you probably can't easily hack that firmware to use the rest of those. I'm sure someone will but the point is especially at the prospect of being able to get a 4K monitor for much less than a 4K price someone will find a way to hack that firmware. But yeah this is why on monitors I never skimp I always tell people get the fancy IPS screen go all out get the ultra sharp don't get that shit oh crap model get the fancy model because monitors matter a lot that's the part of the computer actually that's going into your eyes you're using it the most it matter you better off getting a better monitor than a better CPU or a better video. Like in my old job I had a generic 1080p and that was keyboard and monitor the three most important things. Kind of like the computer chair. But when I had that generic 1080p monitor at work the problem was Photoshopper in design to make something something to look fine because I just can't see the full color gamut then I look at it on a real monitor later or I print it and it looks like Garbo. So yeah I think that's enough news I don't care enough about start TLS to talk about it but anyway things of the day what do you got for me I see two videos here. These two videos go with each other one video is actually they're from the same channel and one of them is linked as the top comment on the other one but we're going to provide both links to make life easy for you so here's the deal right there's a lot of your react videos all over YouTube that's a big trend and it started off as like those kids react things and it was sort of just like lol watch people's you know emotional face reactions when they see things have kids use old computers lol right they're usually utterly painful to watch yes but now there's actually this new genre of react video I don't it's not that new but I'm actually way into it because what it is it's have person who is expert react to thing and inform you about it right so you sort of so for example this one it is like this really good kpop channel it's react to the K where there's a bunch of students who are students at the Eastman School of Music at U of R and they watch their classical train musicians right attending like basically a conservatory that's relatively high brow right it's not as fancy as the one in New York but it's still still high brow they're watching kpop videos right and being like oh do you see the pentatonic thing they did there alright so here we have what is pentatonic memes guy I have no fucking clue okay just saying move and carry on just making sure anyway I just know it's a music word so the here right is a same genre of video we have three marios not marios but the chefs from Italy from high fancy Italian restaurants in Rome right and they're watching the top five most viewed youtube videos about how to make carvenara and they are judging the people who are you know including one famous chef right they're judging these people and they're like oh what do you put in the garlic right and then the other video is they show you how they make it right the right way so it's really fascinating to see them you know like actual Italian chefs go to celebrity chef and be like oh what are you doing you don't know how to make that what's wrong with you but you know that also shows like you know you can't always trust the most viewed thing on professor you right oh my god you know what I I could have gone to youtube and search for like how to make carvenara and in the top five videos are all wrong I gotta find this one I want to make this happen I want to get some filthy casual to like who's good and like you like I'd like to see a speedrunner of mega man to watch you like mega man and a speedrunner by on a commando watch me play by on a commando commentate like the thing is I don't play mega man too in a speedrun way I play in a in a when I play mega man I play in a way that's safe where I know that I can get very far and have a high chance of beating the game without dying I'm not exactly in a hurry right it's like I get to a hard jump I'll stay there I'll get ready think about it you know like in Mario like oh there's a big jump coming alright let's get ready let's get in the right position okay go right I won't just like go go go go go except this level one then I go go go because it's so easy so my thing of the day and before you ask us got asked when I said this is my thing of the day if the if this lock is easily defeated the answer is clearly yes because otherwise it wouldn't be my thing of the day okay so easily defeatable lock there's this fancy hundred dollar smart lock it's a padlock but it's not like a gone line internet of things smart lock it doesn't you can't you can't hack it with some Wi-Fi you can't hack it with some Wi-Fi or anything it's just a computer inside of it yep instead of having a combination or a key it just has a fingerprint reader you can program different fingerprints into it and the fingerprint reader not only is super reliable and they couldn't easily defeat it but even if they scratch the shit out of it it still works fine like they scratch the shit out of the reader it still unlocks only when the right person tries to unlock it fingerprint memory and record detection technology was actually very high quality yep that's not the vulnerability I'm sure it's not 100% foolproof but it's very good so the problem with this lock that actually sounds really useful as like a good lock you can't get your combination you can't forget your key I'd be very inclined to get this right is the lock like actually physically secure as well as as good as any other padlock in terms of you know like being damaged by a hammer so it is in the sense that physically to break it it would require old cutters so the same as any other equivalent lock except for one extra for this convenience except for one fatal flaw okay so I can hack this thing in 30 seconds oh no so it turns off turns out that the back of it does it have a battery or something that runs out well it has a battery but if the battery runs out you just can't unlock it the battery seems to last for fucking efforts like this giant lithium ion battery they're just in it but the back looks like it's steel you can't just break into it if you just put a suction thing on the back of this lock and twist it the whole back just comes off and then what's under there four phillips head screws and if you remove those because the back that you twist off is how you replace the battery and then if you just open that back take the battery out and open the four phillips head screws you can just physically open the lock by tugging on a thing inside of it and you're done so you can just take the back off the lock once again tech bros like to design technological solutions that solve things but lack the basic domain knowledge of the core area from which the problem arises if I'm making a lock that has a battery what you should do is make it so that you can only access the battery compartment if the lock is unlocked it's almost like if you're designing a lock you should talk to someone who is an expert that you could have easily made that twisty panel untwistable when the lock was locked with some sort of you know piece of metal in the way and then make it so that when you unlock the lock the twisty piece of metal on the back to access the battery compartment is now twistable oh my god so one of the first comments on this youtube video now imagine if someone did this but instead of stealing stuff they just remove the battery and close the lock troll of the century remove the battery open the lock then close the lock open it back on without the battery now the person is trying to open this lock desperately they finally go and get some bolt cutters or whatever and find a way to open it and then they open the lock and then everything is gone and you're already gone gone with the shit because instead of looking for a thief they tried to fix their lock they didn't think there was any thief involved they didn't check any security camera footage because they didn't think anything was stolen and by the time they check it you're even farther away that's something I've learned in my entire life if you think you're going to commit a crime the most important thing to know is how long all the places that have security camera footage from that area actually keep their archives because usually they don't keep them that long you got a time delay the discovery of your crimes children so in the meta moment the book la book which might be if it is not the next Thursday Geek Nights episode it will definitely be the following Thursday Geek Nights episode it will be done within a month reading the Odyssey I'll have to set a reminder to read it again because basically I've only been reading it I'm an iPhone on the train and I haven't taken any trace but if we go to the beach on Sunday I'll probably just finish the whole thing laying on the beach I'd have to bring a phone I guess I'll bring my phone to the beach I'm going to bring my phone phone's waterproof I could just bring it to the beach I'd rather just not maybe I could just get away with my watch only I'm confident we'll do this soon I'm piping up some initial thoughts because I really really recommend that everyone read this translation of the Odyssey regardless of whether or not you have read any other translation of the Odyssey it's great in the rest of the meta moment the next big convention that we will be performing at is Pax West and then Pax Unplugged and then after that I think it's Magfest and hopefully also the Anime NYC because I can take the subway to it Anime NYC is after Unplugged before Magfest I don't even know when they all are all I know is all the panel division deadlines are like this week and I'm sorting all that stuff out I've submitted a bunch I haven't run any of them by you for West but I'm sure it'll be fine I'm putting in a bunch because I figure variety let them pick anyway otherwise I want to call this out again because I've noticed I've been fleeing in particular Anime forums and tabletop gaming forums I've seen a lot of Twitter chatter about that and the reason the people are leaving all those forums is because most forums on the internet are full of fucking Nazis these days because they have poor moderation or they worship at the almighty teat of free speech without understanding what that actually entails so I just want to remind all of you if you're a nerd or geek of some kind and you're listening to these words why would you be? I don't know but apparently you are anyone who's still listening to this episode of Geek Nights this far into it would probably do well to join the Geek Nights community forum because the only rule we have is you can't be a goddamn Nazi and you can't be annoying and we don't make a big ceremony about it just if someone shitty we don't even say anything we're here forever and you don't have to worry about it so if you want a community of people who are chill to talk about anime or to form gaming groups I can't think of a better one than our own forum like I'm not even joking board game geek doesn't really have the Nazis like ANN does but even then board game geek I would not form a gaming group I would not form a gaming group from the board game geek forum is all I'm saying I would not advertise where at a packs I am hanging out LFG in that forum but I would do that in the front row crew forum which is our forum and also if you don't feel like you have a lot to contribute I say join anyway because people don't realize how many lurkers that forum has it has a lot of lurkers like it's terrifying but if you post in the Geek Nights forum you probably get more readers than your actual blog a lot of people use it as their blog and start a thing a lot of people have threads that are only so and so cares and they post about a thing all the time and even though they're the only one posting in this thread using it like a blog people are reading it I have proof I mean I'm just looking at it the things of your day thread has like 35,000 views and like 2,000 posts there's people in here and they're all cool scouter I just banned them and you'll never worry about them again and we're not there obviously but SGDQ is happening right now yep and I'm just going to watch it all on YouTube as they get uploaded because I've been too busy the nature of having changed jobs is that I do it's interesting I do less like work like I'm not designing UIs anymore I'm not looking at an API to do a proof of concept like I'm not doing that type of work anymore but instead I have to all my work entails talking to people making powerpoints and then presenting them to people and making decisions so I don't have as much time to sit at my desk and like idly consume something while I work on something I spend all my time literally like going to a meeting being the adult and after people plead their cases to me for an hour I say this is what we're doing that is the decision and that's all I had to do so yeah I'm going to watch YouTube streams instead of the live stream alright there will be no board game chat in this episode of Geek Nights new listener who's hanging out in the stream because Monday Geek Nights are about neck beard, grognard not really grognard more just neck beard, computer stuff Tuesday is the day you want and there won't be one tomorrow there will be one a week from tomorrow because we have lives now we're not even tired of our board games a week from tomorrow we're probably going to do an episode on PUBG we're probably going to talk all PUBG that's our latest obsession you have to wait for a Tuesday that happens to be a board game Tuesday but I will say the next board game we're probably going to review on Geek Nights is going to be Azul because it's really good isn't it Azul AZUL the board game doesn't that mean blue in Spanish? I think so it's set in Moorish Spain and it's about laying bells in mosaics it's a real good game why are people getting in this stream though get out of here go do something we got five people hanging out it's a lower number than usual Monday always gets the least people Monday gets the most downloads maybe it's because tech people know how to download a podcast I don't even know I am really hard pressed to understand someone listen to me good job I hope it's the person we were just asking about board games alright so yeah we somehow did an episode on NoSQL it was there was actually a funny article today someone wrote and they were like blockchain 2018 that's accurate so we're going to talk about relational databases I don't think we're going to get too you're not going to learn from this episode whether or not you should use MySQL or Postgres as far as you all that matters to you is for most people the exact same goddamn thing with slightly different user interface so I guess what is a relational database compared to all the other ways you might store data we're going to start babies first so you are going to make some software right and you have some data and you want to store that data somewhere where it doesn't go away when someone closes the program it opens it again and you put it in a fucking file yep if you're using python you could just like what pickle.io there's other ways to put stuff in a file but you could like serialize it into an object and like write it to a file system somewhere right there are a lot of ways to put things in a file but the problem is when something's in a file it's not that easy to work with that data right it's like you can't easily if you want to search your file for things or let's say you got like a list of names and you know it's like that's hard to do the thing that put the data in the file is usually the only thing that can really take the data out of the file and if there's any question about how you're going to use that data the people who wrote the thing that put the data in have to deal with that so like what if two people are going to try to write to the file at the same time you have to handle that what if you want a bunch of different programs to all share the same data and you know like you've only got one file in the file system you can like synchronize this file if you want to store is 4,000 terabytes of data right so this is what a database solves now the thing with a relational database right it's like well what's what's relational about it versus other database as well imagine you got yourself a spreadsheet right and this spreadsheet has a bunch of columns now spreadsheets already revolutionized like data in the modern world spreadsheets are a big deal name phone number address whatever you know all the data right a spreadsheet well spreadsheets you can search spreadsheets you can sort you can filter spread you can write functions against them reports you can put math in the cells you can do all kinds of stuff in a spreadsheet the problem is if you get lots and lots of data suddenly spreadsheet gets really really big I have a spreadsheet on my desktop at work so I was doing a little like some really simple data analysis it has like 900,000 elements right now imagine sometimes you know spreadsheet gets lots of redundant data for example let's say we're going to make a spreadsheet of game collections right now it's one thing normally if you're just keeping track of your normally as in normalized versus denormalized people who would put a game collection in a spreadsheet are just keeping track of their own game collection yep so they just they only one sheet they just have you know name of the game you know what year it came out who designed the game now this is fine because say if I'm the only one using it it's just like the file thing we talked about before spreadsheet is just fine but if I change my schema like I want to add another column I'm the only one using the spreadsheet anyway it's not going to break anything because my spreadsheet I know what I did to the data right and it's only but now let's say you want to have a website that lets everyone keep track of their game collection well so suddenly you got a spreadsheet in column one is you know name of the game and column two is name of the person who owns it right so I own a copy of Puerto Rico now rim also owns a copy of Puerto Rico so now in the next row it's like well it's Scott Puerto Rico then it's rim Puerto Rico but both rim and Scott Puerto Rico have to have all the information about Puerto Rico right it's like well we're you know suddenly it's like Scott Puerto Rico just made by Andrea Saifarth came out in whatever year it was right and then suddenly we're repeating all this information about Puerto Rico and we're repeating all this information about Scott every single freaking time well a relational database is basically a bunch of spreadsheets except you can link from one spreadsheet to another we can say Scott Puerto Rico and that Puerto Rico is actually a link to another spreadsheet that has all the info about games you have one spreadsheet with all the people one spreadsheet with all the games and then we have a table that links the people to the games so now we don't have to repeat ourselves right and if we update say that Puerto Rico changes its name to a different game we can change that and it changes for everyone we don't have to change for everybody would you change it in one place change for everyone because it's just a link what this solves is primarily around data integrity in that now you understand like you basically put rules around how data is related to other data and you can enforce those interactions you can react to those interactions you understand them and you'll never get into a situation where the data gets out of sync the detriment of doing this is that it does reduce the performance because if I want to do a query against this data now we don't have to hit look at all these different tables right it's like oh I want to get so we've got one table this is a list of people one spreadsheet that's all people and one spreadsheet that's all games and now we want to say alright give me a list of every game Scott owns well we gotta get the Scott row from the people table and then we gotta look at this other in between table and figure out okay which games does Scott own get all those from the game table and then mash them together into one spreadsheet and then we'll actually deliver that and that takes CPU the CPU has to do a bunch of work right normally if you just had spreadsheets you had to do any work you just look at it right now one reaction to this problem was no sequel which we've talked about another reaction to this problem is you might hear people who are more databasey talking about normalizing a database or denormalizing a database that's just about you because you have to make decisions about where are we gonna break apart sometimes it's obvious people games so when you're actually doing real world work it's not so obvious which spreadsheets to make and which ones to link you might think logically it makes sense to split it up a certain way but your user might not be a human your user might be some UI on a webpage somewhere and the kinds of questions that UI is asking might not map to what you logically think of the data has so over time people took this multiple spreadsheet database idea where the sheets linked they're called tables by the way no one calls them spreadsheets I promise we're gonna skip from that to the modern usage of databases right so here's the modern usage right is basically over time this basic model of having tables that link to each other to store your data they added features and they added features and they added features and added features to where knowing how to manage this software with all these features they've added is a job in and of itself they added indexes they've added sharding they've added master slave configurations and replication they've added transactionalism atomic updates it's like there's just so views and triggers and oh fucking stored procedures the bane of my existence a decade ago they've added so many goddamn features to these things special kinds of columns that searches it's like the amount of features that have been added to databases it's any it's good that these features are there because if you're making software that uses one of these databases you don't need to reinvent the wheel because the database probably already has the feature you want you don't have to code it again you need to code a search because the database has a search you just need to learn how to access that search function that's already in the database so without getting into the details of all those things the real gist of this is that the place you use a relational database today is when you have a large amount of structured data that needs to be accessed by multiple different actors well I mean even if you're used just having something that only needs to be accessed by one actor oh yeah if you had structured data they have a thing called sq light which is a really you would not believe how often this thing is used you probably have like a hundred instances of it running on your computer you don't even know about it like every browser uses it to store like your cookies and shit and like it's everywhere just hidden it's sq light and what it does is it runs a relational database inside of a file it so it lets people who are coding code with sql just like they're using a relational database except instead of having a server and all this multi-user stuff it just saves the whole database in a file and it's really small and fast so if you're ever writing like a game and you want to have a database in the game and it's just a single player local game like a phone or something use sq light your phone probably has like a hundred sq lights running right like it's a better question than is what are what are the benefits of a relational database overall because it mostly comes down to like we said data integrity alright so the first benefit is basically that it's so used right my sql postgres ms sql anyone who knows the basics of technology and has worked in technology knows sql enough to write a select statement right so sql is basically this language the structured query language and you use it to communicate with these databases you can use it to make tables you know change now this is important because in theory you can write the same sequel queries against a database it doesn't matter what the thing is it's carrying the database the idea is that any application any programming language you could literally be chiseling ones and zeroes onto a stone and then reading them in somehow sql is sql the problem with a big asterisk at the end of that right I like I've written the same sql that's worked pretty much exactly the same with postgres and my sql this obviously some differences but ms sql I just I've had to do some of that recently yeah I tried to do just select star limit a thousand which means select everything from this table only show me a thousand rows though in case the tables too big I don't want to see all the rows I just want to get a sampling right and nope in Microsoft sequel it's select top one thousand star from now the way around this in the modern world if you're doing a big database really is that usually your applications especially if it's like a UI in the web or something they're not like actively making raw sql queries every time you do something usually there's some middle tier in the way that presents apis and on the back end of those apis are well formatted highly optimized sequel queries anyway so sql is this language I highly recommend anyone who does technology work you should learn it if you don't know it already just learn the basics of it I don't care if you know like how to do a left outer go to a terminal type sqlite it should show up it's almost guaranteed to be installed if it's not it should be easy to install it's free it's open source and then just you know do I think it's slash question mark to bring up the help or help or something like that to bring up the help and just start reading and then get a little sql guide and like a sql babies first and be make a table and then like select fill the table with people like and names and contact info and then like get all the people who live in New York or something like that right and just learn the basics and it's really it's like plain English it's not something it's like select star from cities where right city equals something select name phone number from contact info where state equals New York like you know that's literally what you type in and then it will select the names and phone numbers of everyone in that table who lives in New York New York in the state column so another big advantage of relational databases is the fact that they you sort of intrinsically set up your cardinality as in how your different pieces of data relate to each other this is that question like well one Scott can have many games but many games can only have one Scott like those kinds of questions it forces your design to address those questions right so as you're designing your database you know you answer all these questions and then when you're actually making application your database will prevent you from messing up right yeah for things like okay I've decided that names of these things cannot be longer than 100 characters so that column in the in the database only supports 100 characters and then someone tries to type one in it's 101 characters they didn't they mess up the validation on the on the form on the website and someone types in something it's not going to get inserted in the database they'll be an error yep right it forces you to stick to all your rules if you make it so that you know each person can only you know own one game and you try to add another game to a person you won't let you right you know your data structure is like set in stones you have all these rules and you're prevented from breaking them and that's actually a good thing now one thing that a lot of people seem to mess up is whether just because you have a relational doesn't mean that it's necessarily acid compliant and even if that database could be acid compliant a lot of default settings will make it not compliant yep so even when things are as a compliant people still have timing issues and so I had to look it up because I never remember fully what acid stands for but I know what it is do you remember because I just pulled it up I forgot what the I was yep consistent consistent the I is the one I forgot item potent isolated and durable but here's what this comes down to there's two fundamental ways to set up your database in this regard so say I am adding Scott to some like I'm making a big update where I'm gonna add Scott and I'm gonna add a bunch of games to Scott let's say that in the course of adding Scott I fail but then all my things to add games succeed like I put Scott's name in wrong so now I've got all these games that I added but they're referencing a key that doesn't exist there's no Scott that's no good so at homicity or atomic operations is the idea that you chunk several related operations into one transaction and if any one of those steps along the way fails you back out the entire change so you can never get your database into an inconsistent state you group 100 commands together into one big chunk and it starts doing them in order but if at any point it fails it can undo all of them and go back to where it started also let's say I'm in the middle of doing these 100 things and I'm like I'm at number 50 and things are going along nicely and someone else comes along and does a query because it's multi-user right yeah someone else comes along and ask the database or something they won't see any of the 50 things that happened already now there's no one will not be able to see those changes until they are all done 100% and then they're locked in and then from then on anyone who queries when I'll see all those changes yep now that's both isolation and consistency and actually the isolation part gets really fascinating like here's that will go a little deeper into how this stuff can be cool most of these things happen because you have multiple people working on the database if you ever had multiple people working on a spreadsheet before we've solved a lot of these problems because you can see whoever else is working on your google sheet yeah time with those little colored boxes but imagine if you're all working in excel and someone just saves the fight you someone you both open a spreadsheet at the same time on your computers you save it to the shared directory and then someone else doesn't load in your changes they just save right over and to blew it all your work away now arguably that was solved by webdav but whoever fucking got webdav to work no this is right this is why use a relational database and not a spreadsheet right yep like an excel spreadsheet excel spreadsheets are for accounting and math they're not for data but here's an example of two ways to handle that situation and this is where I not every like databases are complex enough now to where you're going to use one it for real and it's not just like a local tiny embedded database that only your application is using like a real big iron shared database you should have someone on your team who is a database expert because this stuff gets real complicated in a hurry so there's there's a bunch of ways to handle that situation where multiple people are working with the database so one way is locks say I am reading it I have a query I'm asking the database a question in the middle and say my question takes ten seconds to answer well that's a long time yeah four seconds and Scott tries to make a change to the database so one way to handle this is that until my my select query is done the database tables I'm querying from are locked Scott's change just gets queued up you can't make the change till I'm done asking my question because otherwise I might ask for the 50 states and it just gives me Rhode Island back Scott inserts smart and then it gives me smart back and the Scott realizes smart was wrong and removes smart and I get more or fewer results than I expected things could go real south in a hurry but the problem with locks are that your database's performance is now garbage yeah everyone just said everyone's trying to use this database all at the same time and they're just waiting like imagine if you have a computer you're trying to write a comment and a blog post and you gotta wait for everyone else's comment you know what it's like if your database you know boo and every time you look at him he stops moving unless you're the only one looking at him then he'll open his eyes for you so in isolated and consistent way to do this and this is how I back when I did some database administration which was before 2008 at this point so a decade ago with Postgres you can set up concurrency and clever locking so an example would be every time I start a select query the state of the of all the tables that I am looking at is almost snapshot it and saved and its own copy so my query even if it's long running can finish against the state of the database as it was when I began my query if Scott makes updates he's making them to the real database but I won't see them because his updates were after I started my select that means Scott can write to the database all he wants while I'm reading but you can imagine there are ways for that to go south too every query makes its own snapshot of all the tables that can add up in a hurry and cause its own performance problems alright so this brings to another thing which is okay you've got this system that's being used by a zillion people and we've solved all these problems so they can all use it but even though we sort of made it possible so that the data won't get messed up or anything like that or no one has to wait it still takes a lot of computing power to do all this right so there's a lot of the things that are features of relational databases are things to just improve performance and you might not even know they're there caching indices multiple indices pre calculated results tables right so indices are like the most common one and it's basically an index is basically a fancy like look up that lets you know it's the way the data is that because the data is in the end still in a file somewhere on a hard drive right even though it's not a file it's like a fancy server that's in between you in the file is still a file or files right on hard drive somewhere where the data is so the index is basically a fancy thing that helps it search the data faster it's sort of like okay you know it's like look at the index of a phone book and it tells you what page to start looking for a phone number on right it's like so if I say you know select from the table all the you know the names that begin with B it's like aha well I know where it doesn't have to look through every single freaking name in the whole million row table it can do it a lot faster because it has this index files that it built and keeps updated as the data changes so that it can execute the queries faster by referring to these lookups to begin with yep so another big thing which is related to what rim was talking about before is you know you have so many users your computer you know that's running the database is even though it's really night finally tuned you only have so much CPU so much RAM you want to distribute that shit across a bunch of computers that's right you got to get more computers in the mix you get a one database running on many computers that way you can get more users because you know back in the day what people would do is they would just get a bigger computer and that was a strategy you know why that was the strategy that was the strategy because Microsoft Windows cost money if you had Microsoft Windows and MS SQL server and you wanted to get two computers you had to buy licenses again and they weren't cheap now like Oracle and people they link it to CPU cores for their licensing right and it's like well that's you know Oracle also gets it by more Oracle you to give them more money when you want to get more computers in the mix well guess what Linux cost $0 right Postgres and MySQL are free unless you get the paid MySQL which you don't want yep and Postgres like honestly Postgres is better than MySQL and it's free I've been using I use Postgres before it was kind of super production ready Postgres is like the one to use if you don't know which one to use Postgres and MySQL are super production ready but if you're a database nerd you'll hate MySQL and like Postgres better but there are big time companies using MySQL super well yep so the thing is is basically MySQL is a little bit more user friendly if you're setting up a blog probably just go with that and then if you're doing anything serious you better not use MySQL if you're a bank don't use MySQL so in the old days though to get it back to Scott's distributed model yeah distributing a database was a giant pain in the ass oh huge pain in the ass like to get a distributed Postgres setup good god we ended up getting a little bit easier before Postgres did but now Postgres also makes it Postgres has this stuff built in but you used to I used to have to do stuff like put the binary transaction logs of Postgres on a DRBD volume and have that replicate to another place and have a Postgres another Postgres running in read-only mode that happened to also look at that day it was a mess so what most people do and what's sufficient way do I even tell you what I did for MySQL back then what's sufficient for Postgres is you have one server that runs the database normally then what you do is that server whenever it receives instructions to add data it will send those instructions to other computers also running the same exact database so you go to the master and you say insert a new person into that table and it does and then it tells this other computer hey insert this person into that table okay but that other one the slave right the poorly named slave database doesn't accept inserts or updates or data change SQL statements from anyone except that master database so if you tried to go there and insert it would say sorry I can't insert I can only select so that way now you can write all your applications to or when they need to read data or select from the database they make their selects on one of your one or more of your slave databases and then if they need to insert they send it to the master and now all the inserts go to one computer and all the selects go to all these other computers the slaves might fall a little like a few seconds behind the master one so you might have some data timing issues a lot of times you'll have a situation where you go insert and then immediately select and the thing you just inserted isn't there yet that happens a lot there's clever ways around there there are a lot of clever ways around it that's out of scope for the show the point is that when you have this kind of setup a lot of selects will actually use first power if they're complicated and you'll be preventing people from inserting when you make those selects now they have people inserting all day because it's really easy to insert it's always really easy to insert it takes almost no computing power to insert unless you've got stupid indexes that are busted or you've got long running stored procedures for some reason don't do that and now people can select freely and if they tie up a whole computer selecting no big deal we got like a hundred slaves but this only solves the problem do you read many architecture in your actual application if you have a lot of things all trying to write to the database at the same time this doesn't help you at all and the solutions to that are actually way even more complicated most applications are right few read many you got a blog, you got some website how much writing is going on how often do you update the website with a new blog post the main use I've seen how often do you read you got a million people coming to read the website I've seen though for the situation we have a lot of things writing is usually around disaster recovery failover environments where you want to have a hot hot system where your primary and your backups are all hot and can be used as production at the same time and you know the real answer is if a slave goes down well you have a hundred computers or however many usually you don't need a hundred I'm just making up a number usually it's like mastering a couple slaves that gets you a lot of users that's not that many it's like oh one went down well we have a full copy of the database and the same exact software running on all these computers just make another one be the master now and fix the master that broke but I guess what I'll say in terms of that failover disaster recovery thing is that hot cold is really easy to do with relational databases hot warm is actually really easy to do in terms of write access hot hot where both sides or n sides can all be it's not hard to do anymore in the sense that a lot of databases have functions to do this built in like it's a configuration challenge in the old days it was nigh impossible the way I did it twenty well fifteen years ago and you should not do it this way is I had two MySQL databases one of them its indices were hard coded to increment as even integers and the other one was hard coded to increment its writes indices odd numbered integers so there could never be a conflict if you wrote to either one and they both had binary transaction logs pointed at each other do not do that so that does remind me there is the thing that's very popular these days when you get really massive which is called sharding when you do a sharding just like in MMOs actually right sharding is sort of what MMOs do okay well we've got this huge database but you know it has like all the names of all our users but you know what we don't really often have to search through all the users most of the time we're only searching through a subset of the users divided by say region right they got the US office is only really searching the US users and the Europe office is only searching the Europe users so you know what well it will still be the same we still want to combine everything as occasionally we do want to just search everything but most of the time we're like ninety percent of the time we're searching one section right so what we'll do is we'll take the database and we'll chop it up well even though it's the same table we'll put all the US users on this server the Europe users on that server or you know you might how you divide it depends on your application right you divide by you know region you could divide by alphabetical you could divide by all sorts of things whatever makes sense so I ran into a problem writing say website like Reddit you might divide by subreddit so like all the data for this subreddit all the comments all this in this database server and all this other subreddit is on this other database so I ran into a problem once with sharding or it was we were doing effectively sharding this was bad two jobs again but now all the queries related to one subset of your application go to the same place and now you can balance the load is a really popular subreddit well it gets its own database all these unpopular ones we can all cluster them together on the same server so we sharded market data like market data from the stock market based on the alphabet like the letter that ticker started with so all this all the stocks that begin with a server all the ones begin to be around the end that was fine except that s was a problem yeah no you just as I'm saying you had to give two servers to s you can put qz and whatever all the same server together yeah but sharding requires you to analyze and understand the usage patterns of your database and this is why you have that person at your company who is the database person there's a reason why you can get a degree in database administration person if you if they do they'll just know to do some sharding they'll know to optimize some indexes they do all these things on the database server and all the people using the database don't even know that any of this is going on all they're doing is connecting and typing sql and the results always come back and they don't know any of this stuff happen because there's this abstraction layer going on right and but meanwhile you can have so many more users so much more reliable data so much more safety for the data nothing will happen to it'll always be safe and all these great things happen completely behind the scenes so that's why people use databases I spent a decade and not in Excel administering databases in the course of my work and I'm glad to never do that again I will just share an anecdote from when I went to RIT I was a pre-freshman and I had to decide what major like am I going to do IT or am I going to do computer science or all these things and even within IT now they've changed it they've split it out more but IT had all these different concentrations you could focus on an IT was database administration and the way database administration because IT you had to take classes in every discipline and then from there you'd pick a few specialties so we took our initial classes on like database administration and they told us in every one of these classes if you specialize in database administration you will make a shit ton of money immediately out of college and I heard that I understood that I took my database classes and I said that ain't worth it and I didn't want that money so I don't know if you necessarily want to be a database administrator because you will be a database administrator forever if you get really good at it a lot of people who like were previously database administrators are now like calling themselves things like data scientist or you know things like that they do not want to optimize your postgres database you know this or like you know information something something right or like all these kinds of other adjacent you know or like you know they're trying to be like oh I'll be able to be like a machine learning person they're trying to move away from you know I'm an information manager or something right but counterpoint unlike a lot of things if you want to learn database administration and you can't afford to go to college databases are some of the most well documented things there are and free yeah you can download postgres run it on a linux box professional enterprise quality level database the documentation that's free and online documentation super detailed super good you just become a postgres master all on your own and then easily make jobs and money I think you could learn database administration on your own on the internet for free easier than you can learn most programming yeah probably yeah just the resources are more consistent SQL is pretty solid the hard part really is learning just the fundamental concepts yeah and not because the documentation is mostly going to tell you like here's this command here's what it does and you might not understand all the language in the documentation yeah begin insert blah or blah equals foo insert foo commit you might need to get like a book with an animal on it to talk about you know the the general like you know scope of things you can literally vocabulary and then everything else will be easy but I guess the experimentation and like the practical knowledge is way easy to learn because kind of like a python interpreter you can literally just log into your database from a command line and start asking it questions there's also tons of free GUI tools you can use to look at your database like it's a spreadsheet so you have to even learn how to use you know the command line right you can just use the GUI and type your stuff in there my real advice to you is don't use a no-SQL solution unless you understand why they're pretty much dead unless you understand why you can't use an acid compliant real relational database right you'd be surprised like it the relational databases the SQL databases are so flexible you know that it's like almost every application out there can is like you can use one well the worst case is when you see people implement a non-relational database and then over the course of building their thing end up reinventing acid right it's like reinventing acid is like a thing that people do it's like using you know sometimes people use UDP instead of TCP because they need the performance and then they re-implement TCP right and they re-implement TCP only do it if you're re-implementing part of TCP not all of TCP if you re-implemented all of TCP in UDP you should use TCP to begin with yep only if your UDP is your your you know layers on UDP have made it so slow that it's not faster you wasted your time right so yeah but you know if you're making any software at all and you suddenly realize I need a place to store all this data and I might want to search it just pick a relational database use SQLite if you only if it's a local one person application and use MySQL or Postgres if it's a multi person application that has a server and just start from there and only change if you absolutely have to and you're smart enough to realize it's not working alright think we're done that was easy record the patreon thing really bit I'll just do it on the stream for once and the patreon patrons for this episode of Geek Nights are Craig Oliver, Alan Joyce, Rebecca Dunn Heidi and me, Nikolas, Shimmering Ruby, Obelisk, Hanging in the Sky Kajar Tavashan, MyStady.com, Jeremy Minor, Linkiji, Roberto Kingsley, Kelly Stotter, Cold Guy, Amanda Duchet, Sean Yeager, Matthew Smith, Nikolas Brando, Mr. Strongstration, 421 Creations, No I Never Ever Wondered Why We're Here, Semper 5 Bitch, Shervin von Horl, Rory, James David Wright, White, Rachelle Montanona, Superboy, Tripwire, Sailor Vista, Don Schleich, Joshua Drewstar, Clinton Walden, Renfer New Zealand, Ryan Perth, Drew Openlander, Finn Chris, Midgift, Daniel Redmond, Sean Klein, Chris Reimer, and Thomas Hahn. And now I leave you with