SQL vs NoSQL or MySQL vs MongoDB

hi welcome to this video sql or nosql that is a question you get a lot or often the question is mysql or mongodb either way let's have a look at when you might want to choose which database and what the differences actually are so let's dive into that and to understand whether you want to choose a sql or a nosql database for your next web project or whatever your next project is you have to understand how both database systems work what separates these worlds so let's start with sql now sql is not a database sql stands for structured query language and in the end sql is is a language that allows you to write database queries that look something like this now there are more commands than just the select command but this is how you create such queries you have certain keywords certain syntax features like select keyword or the from keyword and you add your own data like select which fields from which table in this example here so that you can retrieve the data from the database you need and there are not just commands for retrieving data there are also commands for inserting updating or deleting data for creating new tables for joining tables if your data is distributed across multiple tables so there are a bunch of commands which you can combine it's quite powerful this is sql now often when we think about sql versus nosql we of course think about the databases that are behind that so the database we will get the data from with the sql language here or with the structured query language the database we typically use is a relational database that means we have a database which works with certain assumptions or in a certain way and it supports the sq language the structured query language such a database works with tables something like a products table for example if we want to store products of our app we could do that in a products table so a table is like a data bin a storage container now in a sql world where we query data with sql we will have very strict requirements for the data we store in our database tables to be precise we have a clear schema of which data can go into a table and this schema is defined by so-called fields so in our products table we would have for example these fields an id a name a price and a description and every new entry we add every new record as it is called every new row in our table is well a record that has values for these fields and the important thing is it can't have more fields than the ones we defined for the table so it's not possible that one record has like a name a price and a description and the next record also has name price description and some extra data like tags you can add this by adding a tags field but then all the entries or all the records need to have tags they might be null they might be empty but you need to provide some information for that tags field and if it only is that there is no information so you have a clear schema and this is how a database table looks like in a sql world now this is a database table and that schema thing is really important all records in a table have to adhere to this schema and when we put data into such a table we therefore have to normalize it which means we have to ensure that however we are fetching the data and whichever extra data we might have or whichever data might be missing that we bring it into a format which fits into that table that's one important building block of a sql world the other important building block is that you typically don't only work with one table but instead with multiple tables which are related so we could have a users of products in the orders table we would not have one orders table only which then holds all the products and users information per record instead we would split that up we would have a users table with its fields and then we have some entries in there and the same for the products so we would have something like this where we manage users and products separated from each other but of course there is a connection for example a product could be created by a user or in this example here we could have orders a order simply is a combination of a product and a user because one user might have many orders and one product might also be part of different orders of different users so the orders table would be our connection here it would be setting up the relations we would have a user id a product id field in there and therefore we can connect a specific user to a specific product with the product which was ordered by the user this is very typical for the sql world and data relational databases which use sql and therefore we got two important characteristics already the strict schema and that relation world by the way there are multiple types of relations in a sql world we just saw the many-to-many relation obviously we also got a one-to-one relation now here's an example we could have a user and we could have a table with contact data the user maybe is made up of id a name a age and the contact data could be also an id typically every record has an id in every table but besides the id a contact data field contact data record could have the email and phone number fields and then maybe we want to separate that in two tables but we still want to ensure that every user has one contact data piece and that can be done by adding a contact data id field to the user which holds the id of the contact data in the contact data table which belongs to that user and that would be one to one because one user has one contact data entry or record and one contact data record belongs to one user you could also add a user id field to the contact data table and point at the user to which it belongs we can also have one too many and strictly speaking our one to one here would be one too many because since we don't assign a user id to each contact data theoretically one contact data could belong to multiple users here's another example one two menu would be pretty much the same now with users and products we have a creator id on the product pointing at the user who created that product and therefore this product only belongs to this one user but one user can be part of multiple products and then we of course we got many too many many too many is the case we already saw we got two tables users and roles let's say a role could be something like admin editor stuff like that and we want to connect these two tables now since a user can have multiple roles and a role can be assigned to multiple users there is no way we store that information in two tables only because we would have to add one new field so one new column for every row we want to assign to a user and the other way around and since we can't predict how many roles a user will have and how many users a role will be assigned to you we add an extra table in between the user roles table this table has a user id and a role id field and therefore it creates as many connections as we need that's a many-to-many relation so you can learn way more about that and further links can be found in the video description but this is not about learning all about sql it's about learning the key characteristics and that's the strong schema and the relational nature of the data we store data distributed across multiple tables which we then connect through relations and the sql language the structured query language is capable of querying these relations there are special commands so-called joins which you can use to retrieve connected data in one result set even if it is stored in multiple tables obviously the more complex the relation is and the more tables are included the longer such an operation can take but it is possible and it is one of the core strengths of the sql world the fact that you have normalized distributed data which can merge together with sql queries now that was the sql world let's now have a look at the mongodb world or the nosql world there are other nosql solutions too aws dynamodb would be one for example a serverless one now i'll have a look at mongodb since it's the most popular nosql database now what is a nosql database or what is mongodb now the name mongodb is simply stemming from the word humongous because it's built to store lots and lots of data in a very efficient way something sql solutions can struggle with but more on that later so how does it work well we have databases in the nosql world too like a shop database but then in there we don't have tables but so-called collections but you could translate it with table essentially maybe the users and orders collection in our shop database now in such a collection we have so-called documents documents now look a bit different they are basically the rows in your table you could say but not only do they look a bit like jason here the really interesting thing is that they don't have to use the same schema you can have multiple documents in one collection which have different fields so you might have a different structure in there there is no schema applied on you so you could have this document in a collection but then also this one where you got no h or this one where you got no name and it's not just that you say the value is null you can really have totally different documents in one and the same collection this of course has the downside of you not being sure if all the data adheres to your format but on the other end you don't want to have that format in the first place if you decide for such a solution because the big advantage of course is that this is a super flexible solution here you can add new data let's say you're building a new business you're building a new app and at some point you want to fetch more user data than you did in the past not crucial one which would well kind of be bad for the existing users but maybe useful data which you want to have for all new users now you can still store them all in the same collection because there is no schema implied and you can therefore be flexible regarding your data format there also are no relations well at least kind of you can theoretically set up relations and do the querying manually but in general there are no relations in a nosql world you have your collections with your documents in there and whilst you can as i just said kind of relate multiple collections and that is possible that is done in reality no sql solutions no sql databases like mongodb rely way less on such relations instead the idea is that you put all the information in one place so you might have your orders collection where each order does not only contain the user and product id but it contains the key user data you need to work with the order something like the address the email and the id and the key product data you need like the price the name and the id anything like that so whatever you might need to display it on an order page and process it internally you would put that into your collection and therefore you might still have an extra users collection an extra products collection with more detailed data but you don't really need to query that connect data because you got the data you need in each collection which you might be querying from different points of your application and this is the idea have less relation merging going on have super fast and efficient queries there for it but of course the disadvantages that you have some duplicates data if the product name changes you have to update it not only in the products collection but also in the orders collection but if you have an application where you have a lot of reads and not that many rights for products for example then this would be a great setup still so who is the winner then the more strict sql world or the more lose nosql world the most important thing first there is no clear winner it really depends on the kind of application you're building and the kind of data you're storing and in really big applications or businesses you typically use both solutions because you work with different data or with different applications and therefore you have different needs each solution has its strengths sql for example uses schemas and that can be both an advantage or a disadvantage it's a disadvantage if you want to be flexible about the data but it's an advantage if you want to have that predictable layout the relations also can be a huge advantage if you have data like say products and users and they are changed frequently then it might be worse to update them in multiple collections in the nosql world if you can go for the more structured sql approach where you only update the user in your users table and every new query which creates orders and pulls in that user information will automatically take that updated user because it's only stored and managed in one place opposed to multiple places the downside is that if you have these complex queries and you do a lot of reads that might be worse performance or might be leading to worse performance than in the nosql world where you will have all the data already merged in the right way in one collection and you don't need to merge it manually through a query data is also distributed across multiple tables in the sql world therefore and this can also be both an advantage or disadvantage for the reasons i just mentioned regarding the updating we all have to talk about scaling and we can differentiate between horizontal scaling and vertical scaling now what's the difference now in both cases let's say we have our database server if we scale horizontally we simply add more servers we add more power by adding more service obviously we have to ensure that our database is split up across these servers but we still can work with it and that is harder than you might guess and for sql servers it's especially hard and often not possible because the data can't be split across multiple servers so therefore this horizontal scaling is often very often not supported for sql databases vertical scaling then is the alternative there you simply add more power to your existing server the downside of that of course is that there will be some limit there's only that much computing power you can add into a computer and thereafter it'll be hard and that is indeed one of the restrictions of a sql database approach at some point scaling can become super hard because horizontal scaling is impossible or very hard and vertical scaling has limits now chances are that you might not hit that for your application though because we're talking about a lot of computing power when we face the limit but it is something to keep in mind and nosql is better there because mongodb and other nosql approaches can easily be split horizontally due to the way the data is stored that is way simpler because you have no relations you have these standalone collections and even in one collection you can split that data across multiple servers and then merge it together automatically so horizontal scaling is possible in nosql it's not in sql one other thing we have to consider is that for sql we have certain limitations if we have lots of and with that i mean tens of thousands per second read and write requests especially if we have very complex queries with a lot of joins now nosql is schema less and that can be an advantage since you're more flexible of course it can be a disadvantage because you can't rely on your record to have a certain field it might just not have it because there is no schema to force it to have it you also have no relations or very few relations and this is great for reading a lot it can be a disadvantage if you have a lot of write requests that affect multiple collections because then you have to update some data in multiple collections because you're duplicating it instead of keeping a relation so if you have data which is strongly related and which you store in multiple collections and you update that data a lot nosql might not be your best solution now data is typically merged or nested in a few collections and with that i don't mean that you don't have many collections i really just want to emphasize that you typically want to keep all the data in a collection that you query a lot if you got a orders page you want to put all the data in that orders collection which you need on the orders page so that you don't have to reach out to the products collection just to display orders and that is what i mean here you have some collections and the amount of course varies depending on your application size you have some collections which typically serve certain purposes and the idea is not to have thousands of tables which you connect with relations but instead you shrink that a bit and instead have all the data in one collection that you typically need in one part of your application now i already did talk about the scaling nosql can be scaled in both directions which is great of course and finally it offers a great performance for mass read and writes except for the cases where we will update a lot of collections regularly so if we have that user in four different collections and that user data changes all the time having to update this all the time and again we're talking about thousands of write requests per second here can be leading towards performance than in the sql world now that was a lot of talking and finally the question remains which approach do you want to choose generally you can build every application with either approach there is no clear border where you would say this has to use a sql database or this has to use a nosql database you can build any application with either database system and you'll probably only face issues if you become really really big at this point though as i already mentioned you typically use both systems both approaches for different data types in your business so in the end you have to think about the core strengths do you want to have a clear schema do you use a lot of relations do you maybe work with data that changes frequently and is used on different parts of your application a lot maybe you need a sequel database for that because this would allow you to manage your data exactly in that way the nosql approach is great for applications where you want to read a lot maybe also write a lot but not necessarily update dozens of places in your database in each right instead maybe you got a few features in your application which are used heavily and a few other features which are not used that much and then you want to ensure that you can read all the data you want to display as quick as possible and that you don't have to run complex join queries for that maybe scaling all the matters a lot then and therefore the horizontal scaling is a great strength so then you might be leaning towards the nosql solution i'd say that nosql is a bit more hyped these days and it offers significant advantages but it's wrong to say that it's strictly better than sql approaches and the other way around too in the end it's always coming down to testing playing around and simply choosing the right tool for the job you want to get done

Channel: Academind

Views: 1,675,702

Keywords: sql, nosql, mongodb, mysql, sql vs nosql, mysql vs mongodb, mongodb vs mysql, nosql vs sql, scaling, sql horizontal scaling, what is nosql, what is sql

Id: ZS_kXvOeQ5Y

Length: 21min 29sec (1289 seconds)

Published: Wed Jul 25 2018