SQL vs NoSQL Databases – Part 1: Theory

This is part one of a two part series about databases. The first post focuses on theory, and the second part focuses on the practical decision of evaluating which database to choose for a project.

There are two kinds of databases: relational databases and non-relational databases. What is the difference, and what considerations are relevant when choosing which to use?

Developers refer to the contrast of relational and non-relational databases as SQL vs NoSQL. SQL represents relational databases. It is actually a query language for relational databases, most prominently MySQL. NoSQL, meaning Not Only SQL, is a more diverse collection of databases. It refers to any type of non-relational database. The use of NoSQL databases has grown in popularity over the last decade for a few reasons discussed in this post and the follow-up post.

Databases store data. Sometimes the data being stored is fairly straightforward. For a simple contact list, all you may need is a simple key-value pair, as in a person’s name and their phone number. You may just want a person’s phone number, accessing it using their name. For a task like this, a non-relational database works well. You can use a key to access its associated value (even if the value has more information than just a number- it could also have an address, a city, or more). The data can be stored essentially in simple key-value pairs.

A relational database should be used when the connections between data is important. If it makes sense to store the data in tables and rows, use a relational database. Tables and rows represent the underlying structure of relational databases.

This allows, for example, ease in finding all the entries with the same area code quickly. You can query multiple tables easily by joining them together. In a non-relational database, you would have to check each key-value pair, check if the value matches, and store that which of the numbers have the same area code.

Sometimes it is essential or helpful to keep track of many aspects of the data. I might want to easily access all my friends who have the same area code, or live on the same street or in the same city. This would take a long time in a non-relational database with many entries (unless you have a lot of redundancy, which takes up more memory- see the appendix here). However, it would be easy to perform this query in a well designed relational database. Once any two or mote tables are joined together, the new table can be easily queried. Joining tables allows for a variety of useful queries.

One of the main tradeoffs you get in the variety of available queries you lose in the convenience of set up. For example, in a MySQL database you have to define how many characters and what type of thing goes in a given column or row. This cannot be changed on the fly- most commonly, your database has to be disconnected when redesigning your database tables.

I’ll discuss in the next post how to choose a SQL or NoSQL database for your project.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s