Repository navigation
Data Modeling
Largely based on SQL vs NoSQL or MySQL vs MongoDB, a video by Academind
SQL is an acronym that stands for Structured Query Language.
Meaning, SQL is not a database, it's a language. It's a language to write queries to a database. In this language, you use certain keywords built-in and parameters that refer to fields/records in a database. You can do more than just get stuff from a database with SQL, you can also insert, update, and delete data or manipulate the relationships between tables in a database. A SQL query to retrieve something from a database might look like this:
SELECT id, name, price FROM products
In this query, SELECT and FROM are SQL keywords and the other words are parameters that refer to things in the database.
When we ask the question "SQL or NoSQL?", we're refering to the databases that work with these languages.
Databases are organized collections of information.
SQL databases work in a specific way and adheres to a predefined structure called a schema. They use tables to store data, which often have relationship with each other.
A schema defines the tables that are inside the database and the fields (columns) that must be in each record (row). All records in a SQL database must adhere to the format defined by the schema. Even if a record doesn't have any data to store for a specific field (column), it must provide something, even null, to fill that field for that record. Additionally, a record cannot contain some extra information that doesn't fit into the schema-defined fields. If there is no field for "temperature", you cannot create a query to record some information to that field (it doesn't exist if it isn't defined by the schema).
So all records in a table have to adhere to the schema. Which means that when data is add to a table in a SQL database, it must be normalized, meaning it must be formatted so it adheres to the schema. We have to take off any extra information there isn't a field for.
The other defining feature of a SQL database are the relationships between tables. You might hear the term "relational database". Typically, you'll have multiple tables in a SQL database where records in one table are connected to one another. For an online store, you might have a "customers" table that contains contact information and an "orders" table that's contains all customer orders. The "orders" table would be connected to the "customers table with a piece of data like an id.
There are three types of these table relations:
- One-to-one - one record in a table is connected to another (single) record in a different table
- One-to-many - one record in a table is connected to many other records in a different table
- Many-to-many - multiple records in a table are connected to multiple records in another table (this might be a table that connects two tables together)
When you hear "NoSQL", think "not a SQL database".
MongoDB is the most popular NoSQL database. It gets its name from "humongous", as it's made to hold large amounts of data.
NoSQL databases don't use tables per say, but use collections. In each collection there are documents. These documents are often stored in a format like JSON. You could view these as similar to records (rows) in a SQL database, but there are some big differences.
The defining feature of a NoSQL database is that stored data (in documents) does not have to adhere to a pre-defined schema. You can have documents in the same collection that don't have the same fields.
Because of this, there are no real relations between collections. You could create some relationships yourself, but this would mean updating the data in each collection affected.
NoSQL databases are very flexible and good at storing lots of data and performing multiple reads from its collections. However, this can be a big disadvantage for some use cases, especially when you need some relationships between datasets and must update documents in the database in multiple places.
There is no definitive "right" or "wrong" answer to "SQL or NoSQL?"; it all depends on the use case and what would serve your application best.
SQL databases are great for storing data with predictable fields and if you need strong relationships between records across tables. You can rely on your schema to know that each record will have certain pieces of data. But there are some limitations when it comes to scaling SQL databases in large applications and they are slower to store/read large amounts of data than NoSQL databases.
NoSQL databases are great for flexibly storing pieces of data that may not have data for each field. And they can be very fast at reading and storing such data. However, because there are no relations between documents stored in a NoSQL database, it is not best for updating data across multiple collections. You have no schema to restrict what you store, but you can't be sure of the fields that are included in each record.
In very large projects, you will typically see both SQL and NoSQL databases used to leverage their strengths.
All wiki pages are summaries of the work of others. See the top of each page for the source blogs/articles/books and their authors. All other content by Billy Bunn.