SQL vs NoSQL or MySQL vs MongoDB

Which database and what the differences actually are 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've got to know how both database systems work what separates these worlds.

SQL now SQL is not a database SQL stands for structured query language and in the end SQL is a language that allows you to write database queries that look something like this no there are more commands to just deselect command but this is how you create such queries you have certain keywords certain syntax features like a select keyword or the from keyword and you add your own data like select which fields from which table during 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 all the 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.

Databases that are behind that so the database we-well get the data from with the SQL language here or with the structured query language the database we typically use is a relational database and that means we have a database which works with certain assumptions or in a certain way and it supports the SQL language the structured query language such a database works with tables.

 Something sort of a products table for instance if we would like to store products of our app we could do this during a products table so a table is like a data bin a storage the container now in SQL.

a world where we query data with SQL we will have very strict requirements for the data we store in our database tables does one be precise with a transparent schema of which data can enter a table and this the schema defined by so-called fields.

 so in our products the 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 a new row in our table is well a record that has values for these fields and the 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 or price in the description and the next record also has named price description and some extra data like the tax you can add this by adding a tax 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 tax 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 the world now this is a database table and that schema thing is really important all records in a table have to the schema and when we put data into such a table we therefore need to normalize it which suggests we've to make sure that however we are fetching the info 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 the building block of a SQL world the average 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 user 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 might split that up we might have a user's table with its fields 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 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 a 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 SQL.

the many-to-many relation obviously we also get a one-to-one relation now .we could have a user and we could have a table with contact data the user maybe is made up of ID a reputation age and therefore the contact data might 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 e-mail and telephone number fields then maybe we would like to separate it and cue tables but we still want to make sure that every user has one contact data piece which are often 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-to-many and strictly speaking our one-to-one year what do you want too many because since we don't assign a user ID to each contact data theoretically, one contact data could belong to multiple.

one-to-many would be pretty much the same now with users and products we have a creator ID on the product point and the user who created that product and therefore this product only belongs to this one user but when the user can be part of multiple products and then we, of course, we got many to many many to 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 task are often assigned to multiple users there is no way we store that information in queue tables only because we might need to add one new field so one new column for each row we would like to assign to a user and therefore the other way around and since we will not predict what percentage roles a user will have and the way many users a task are going to be assigned to you we add an additional table in between the user roles table this table features a user ID and a task ID field and thus it creates as many connections.

Distributed across multiple tables which we then connect through relations and the SQL language to structured query language is capable of querying these relations there are special commands so-called joins which you'll use to retrieve connect the info in one result set albeit it's stored in multiple tables obviously the more complex the relationship 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 that can merge together with SQL queries.

The MongoDB world or the NoSQL world there are other NoSQL solutions to AWS dynamodb would be one, for example, a serval s1 now I'll have a look at MongoDB since it's similar 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 the efficient way something SQL solutions can struggle with but more on that later so how does it work well we have databases?

The NoSQL world queue 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 they may be the users and orders collection in our shop the database now in such a collection we have so-called 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 would possibly have a special structure in there there's no scheme implied on you so you'll have this document during a collection but then also, this one where you bought no age or this one where you bought no name and it isn't just that you simply say the worth is null you can really have totally different documents in one at the same collection this, of course, has two the downside of you not being sure if all the data 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 the solution that this is a super flexible solution here you can add new data for the instance you're building a replacement business you're building a replacement app and at some point you would like to fetch more user data than you did in the past.

 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 employed and you can, therefore, be flexible regard regarding your data format, there also are no relations well at least kind of you can theoretically set up relations and do decorating 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 it is done in reality NOSQL solutions NoSQL databases like MongoDB rely way less on such relations instead of the ideas.

You put all the information in one place so you might have the collection of your order where each order does not only contain the user and product ID but it contains the key user data you would like to figure with the order something just like the addressed email and therefore the ID and the key product data you would like wish to price the name and therefore the ID anything like that so whatever you'd possibly got 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 and an extra products collection with more detailed data but you don't really need to query that connect data because of 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 has 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 product collection.

 But also in the orders collection but if you have an application where you have a lot of reads and not that many writes for products for example then this would be a great set up 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 there are often both a plus or an obstacle it is a disadvantage if you would like to be flexible about the info but it's a plus if you would like to have the 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.

The NoSQL world if you can go for the more structured SQL an approach where you only update the user in your user's 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 the 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 their updating.

We have our database server if we scale horizontally we simply add more service we add more power by adding more service obviously we've to make sure that our database is break up across these servers but we still can work with it which is harder than you would possibly guess and for a SQL service it's especially hard and sometimes impossible because of the info cannot be split across multiple servers so therefore this horizontal scaling is usually fairly often not supported for SQL databases vertical scaling then is that the alternative.

There you merely add more power to your existing server the the downside of that, in fact, is that we'll be some limit there's only that much the computing power you'll add into a computer and thereafter it'll be hard which is indeed one among 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 likelihood is that that you simply won't hit that for your application though because we're talking a few lots of computing power where we face the limit but it's something to stay in mind and NoSQL is best there because of MongoDB and uber NoSQL approaches can easily be split horizontally thanks to the way the info is stored that's way simpler because you've got no relations you've got these standalone collections.

Even in one the collection you'll split that data across multiple servers then merge it together automatically so horizontal scaling is feasible in NOSQL it isn't in SQL when everything we've to think about is that for SQL we've certain limitations if we've many and thereupon I mean tens of thousands per second read and write requests especially if we've very complex queries with tons of joins now NoSQL is schema-less which are often a plus since you're more flexible in fact it can get a disadvantage because you cannot believe your record to possess a particular field it'd just not have it because there's no schema to force it to possess it you furthermore may haven't any relations or only a few relations and are often "> this is often great for reading tons it can be an obstacle if you've got tons of write requests that affect multiple collections because then you've got to update some data in multiple collections because you're duplicating it rather than keeping a relationship so if you've got data which is strongly related and which you store in multiple collections and you update that data tons NOSQL won't be your best solution now data is usually merged or nested during a few collections and thereupon I do not mean that you simply simply simply do not have many collections I actually just want to stress that you typically want to stay all the info during a collection that you query tons if you bought a orders page you would like to place all the info therein orders.

The collection which you would like on the orders page in order that you do not need to reach bent the products collection just to display orders which is what I mean here you've got some collections and therefore the before the amount, in fact, varies counting on your application size you've got some collections which usually serve certain purposes and the idea isn't to possess thousands of tables which you connect with relations but instead you shrink that a touch and instead have all the info in one collection that you simply typically need in one a part of your application now I already did mention the scaling NoSQL is often scaled in both directions.