This ZL Tech Connect entry is part one of a two-part blog post discussing SQL and NoSQL. Click here for part two.
Let’s start this whole thing from the beginning. Not the “SQL, which stands for structured query language, first appeared in 1974” beginning. Because, who cares? But the “SQL and NoSQL are databases” beginning.
SQL and NoSQL do the same thing: store data. NoSQL isn’t better or worse than SQL, each just use different approaches. In other words, they are alternatives, not replacements. It’s like saying a train is better or worse than a car, which isn’t necessarily true. They do the same thing: transport people/goods across land, but they do this differently – they are, you guessed it, alternatives.
Well that makes sense, but how are they different? I could talk to you about schema or drop the terms relational and non-relational databases – but then I’m not much more help than the first page of search results from Googling “SQL vs. NoSQL”, am I? Instead, let’s use a (simple) example and build on it as we go along.SQL
Imagine you have a small bookshop (shout out to SQL vs. NoSQL: The Differences by Craig Buckler for the inspiration for this example). You want to store information about the books you have in your shop. Say, you’d like to record the ISBN, the book title, the author and the selling price of every book in stock.
This information could be stored as a table, like this:
|0134023218||NoSQL for Mere Mortals||Dan Sullivan||$40.00|
|0132715120||Sams Teach Yourself SQL in 24 Hours||Ryan Stephens||$16.00|
Table 1: Book Table
So, every row is a different book and every column is a property of each book. The design is rigid. That is to say that you couldn’t use this table for a shoe store, which would have a very different dataset (for example, brand, model number, color and size), or maybe even another book store, because it might have a different filing system. Similarly, you can’t store any additional information about the first book in the above table, such as the publisher.
Now, let’s say your bookshop grew to become a bookstore, and you want to track which publishers are supplying which books. So, you create a table of publisher contact information, and since publishers often supply you multiple books, you assign each publisher a unique ID number:
|Publisher ID||Publisher Name||Contact name||Contact email|
|P002||Penguin Publishing||Martin Fedunfirstname.lastname@example.org|
|P003||Sams Publishing||Beverly Changemail@example.com|
Table 2: Publisher Table
So with Table 2 in hand, we can simply associate Publisher ID’s with books instead of typing in all the publisher information from Table 2 into Table 1 for each book. To do this, let’s add a new column to Table 1:
|0134023218||NoSQL for Mere Mortals||Dan Sullivan||$40.00||P001|
|0132715120||Sams Teach Yourself SQL in 24 Hours||Ryan Stephens||$16.00||P003|
New Table 1: Book Table with Publisher ID
And just like that, we have two tables that relate. In SQL, we can make as many tables as necessary. We just have to make sure we relate them. SQL is therefore…a relational database! (Sorry, I know I said I wouldn’t)
Therefore, like a train on tracks, SQL is limited to its predefined structure. While it is possible to expand and build new routes (or in this case, new related tables), SQL databases are best used for logical, related data requirements that can be identified up front. Some examples of data types that are suited for SQL databases are healthcare data for insurance companies or a company’s employee information, as the categories for these data sets are fairly static and can be predetermined
One of the benefits of SQL databases is that they allow you to enforce data integrity. Can you show me an example of this using my bookstore database? Why not! Our bookstore could use a SQL database to:
- Ensure all books have a valid Publisher ID code that matches one entry in the Publisher Table, and
- Not permit publishers to be removed if one or more books are assigned to them.
By implementing these schema (yes, I did it again) rules for the database to follow, it’s not possible for users to add, edit or delete records that could result in invalid or orphan data.
This isn’t so for NoSQL databases. But you’ll have to read part two for more on that.