SQL vs. NoSQL: The Basics Part 1

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:

ISBN	Title	Author	Price
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
P001	Addison-Wesley	James Sommermann	jamess@aw.com
P002	Penguin Publishing	Martin Fedun	mfedun@penguin.com
P003	Sams Publishing	Beverly Chang	bchang@samspub.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:

ISBN	Title	Author	Price	Publisher ID
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.

 

Databases NoSQL SQL

As a member of the marketing team at ZL, I have developed an interest in the dynamic information governance industry, its complex products and the strategic players. Born and raised in Jamaica, I made my way to Silicon Valley via Philly, where I studied economics at the University of Pennsylvania. In my spare time, I enjoy exploring big cities, Yelping new restaurants and playing tennis.