SQL or No?: Relational and Non-Relational Databases

sqltestMany cloud applications are not complete without a database storing information. Traditionally, relational databases like MySQL and Oracle provide the needed tools; although, with the advent of non-relational or distributed databases (more commonly known as NoSQL), various alternative options have arrived to counter the conventional SQL solution.

Amazon Web Services (AWS) is one company that capitalized on the wealth of database options, providing users with multiple services for database management. However, before we delve into the differences between AWS database options, just what is a relational database? And what does NoSQL really mean?

Relational Databases (SQL)

Relational databases use tables with a fixed schema to store data. This means that data is shown in records presented as columns and rows, with the data related in a pre-determined manner according to common keys. For example, consider a database of employees:

id_num last_name first_name
0101 Scott Michael
0102 Schrute Dwight
0103 Halpert Jim
0104 Beesly Pam
0105 Martin Angela

The common keys in this database are id_num, last_name, and first_name. Of these keys, one or more primary keys can be defined. Primary keys cannot contain a null or duplicate value because it is intended to work as a complete identifier and help secure database integrity. Similarly, all rows should be distinct.

Consider which key in the database above would work best as a primary key. If you thought id_num, you are correct: While people may share first and last names (what if a Jim Smith joined the company or a Vivian Beesly?), employee numbers should remain individual to each person, making it an ideal primary key.

This column and row structure also allows for fine-grained security. Returning to the employee table, if the company wanted to allow employees access to their own records, the database could be secured to do this.

Moreover, what of database changes and updates? SQL works with what are called ACID transactions. This means:

  • Atomicity: Whenever the database is changed the changes either completely succeed or completely fail; there are no partial successes
  • Consistency: The database is always in a valid, stable state; no transactions can bring the database into a state that goes against the defined rules of the database
  • Isolation: Each transaction is run as though it as full use of the system to maintain consistency
  • Durability: Every transaction is permanent; once a change is committed it cannot be lost

Nearly all relational databases use Structured Query Language (SQL) to communicate, a declarative language meant specifically to be used with databases. Relational databases excel at computing complex queries.

Relational databases are scalable but at the vertical level. Databases are generally located on a single system and require an increase in CPU, RAM, or disk space to scale.

Interested in learning more about SQL? Check out our course at the Linux Academy!

Non-Relational Databases (NoSQL)

Unlike SQL databases, non-relational databases are not locked into a single schema. Where the tables, rows, and columns of SQL-based databases are non-negotiable, NoSQL databases are more flexible, and can store and read data in a variety of ways, including:

  • Key-value stores: Key-value stores use an associative array of key-value pairs. Each key appears no more than one in a single database.
  • Graph database: Intended for content best represented by a graph.
  • Document store: In document stored data, each piece of data, along with it’s associated data, are viewed as a document.
  • Column store: A key-value pair that exists outside of a table; columns may change depending on row, etc.

What the NoSQL database supports of the above depends on which you use. Similarly, if you wish to maintain SQL-like ACID transactions and joining tables, you need to ensure that whichever NoSQL database you choose offers these features.

Querying NoSQL databases also is highly dependent on the database solution being used. NoSQL is often called “Not only SQL” because many still support traditional SQL queries. Others have their own languages that feature RESTful, JSON- or Javascript-like structures. NoSQL query languages are often made to be resemble programming languages, making it easier for developers to pick up.

With the flexibility of non-relational databases also comes scalability. While traditional relational databases are scalable, it often comes at a high cost, regardless of load. In contrast, NoSQL scales at a horizontal level: Meaning, to scale out all you need to do is add more instances to share the workload. This can also contribute to higher availability, greater speeds, and ease of scaling.

Examples of NoSQL databases include CouchDB, MongoDB, and AWS’s DynamoDB, an excellent starting point for exploring NoSQL more in-depth. Check back soon to see an example of relational and non-relational databases in practice as we take a look at AWS’s database offerings on Wednesday.

Want to brush up on your AWS database knowledge before then? More information on AWS RDS can be found in the AWS CSA – Associate and Professional courses, while DynamoDB can be found in the AWS CSA – Professional and AWS Developer – Associate tracks.

Elle K

Elle is a technical writer and Linux aficionado at Linux Academy.

Leave a Reply

Your email address will not be published. Required fields are marked *