SQL, Structured Query Language, is the fundamental language that allows for the operation and optimization of many popular technology companies today, including Yahoo, Twitter, Wikipedia and Microsoft. SQL is also the language that is the basis for the type of databases that I’ll be covering today, Relational Databases. Relational databases are the fundamental database type that new computer science students will be introduced to in the process of learning database concepts. This article will introduce the idea of the relational database to those unfamiliar with them, in addition to how they work practically and how they compare to other popular database types.
History:
Relational Databases were first introduced conceptually in a paper written in 1970 by IBM Employee Edgar Frank Codd entitled “A Relational Model of Data for Large Shared Data Banks”. In the paper, he introduced the concept of a “relation”, which is a set of n tuples and k elements organized in a table where each tuple is a row and each element is an attribute. Like tables that you are probably already familiar with, the tuples are used to organize the overall table into subsets of distinct pieces of information, or rows. Each individual element or attribute could contain anything, from a string to an integer to an array or datetime object.
At the time this paper was released, navigational databases were the most common database used by technology companies. The issue with navigational databases was that they were only good at finding things that the user already knew existed at a specific query path. Additionally, navigational databases were heavily reliant on specific fixed data structures and strict organizational requirements. Codd’s Relational Database concept sought to overcome these issues. Rather than needing to know the specific query path necessary to finding information, Codd attempted to give the power of data exploration back to the user by making the database structure easier to navigate. Additionally, though Codd’s idea still required the use of data structures, relational databases were more clearly organized for the user to understand and explore.
Over the course of the 1970s, Codd began to turn his idea into a fully fledged operational database management system that could be used as a replacement for navigational databases at a professional level. IBM made the first substantial progress in this field with the RBDMS prototype System R in 1974. However, despite IBM’s best efforts, the first commercially available Relational Database Management system was Oracle, which was released in 1979. In addition to the use of relational databases, Oracle also utilized SQL for basic queries and table joins. With this, relational databases began their conquest of the tech industry and are one of the most widely used database types in the present day.
Practical Relational Database Modeling:
This section is going to go over a simple relational database schema, as well as the structure of SQL queries to utilize the benefits of a relational database. Below is an example schema:
One of the biggest concepts to grasp when using a relational database is using table joins to create and explore relationships between the data. In this example, I’ll say that each User of the app is capable of writing many tweets. Therefore, a tweet can only be saved in the database if it’s associated with a user. For every tweet saved, we will save the user’s id inside of their written tweet data. In a situation like this, I could say that the easiest way to access all of the tweets that a user has written is to query the User table in order to find the desired user. After finding the users username, I could do a table join between users and tweets where all the tweets queried are associated with the desired user. In SQL, this query would look something like this:
NOTE: for the purpose of the below example the desired user will have username “TwitterFanAdam”
SELECT Users.id, Users.Username, Tweets.Text
FROM Users
INNER JOIN Tweets ON Users.id=Tweets.userId
WHERE Users.Username = 'TwitterFanAdam';The ability to run queries like this is one of the biggest advantages to using a relational database. With a few simple lines of SQL, I’m able to see all of the tweets that TwitterFanAdam has ever written. This example with users and tweets is extremely simple, but this relational concept can be expanded to a database of infinite size and complexity. The awesome simplicity of SQL and relational databases has lead to their commonness in business contexts.
The noSQL Conundrum:
Despite the widespread use of relational databases in modern technology, certain pioneers in the world of database management have attempted to create alternatives. Generally, these alternatives are considered to be noSQL databases because they use alternatives to SQL for queries. Some general examples of noSQL databases that I will dive into in future articles are document databases, key-value databases, graph databases and more.
When considering whether a SQL relational database will be a good option for a project, keep its primary strengths over noSQL in mind. The biggest strength of a SQL relational database is its ACID compliance. In short, ACID stands for Atomicity, Consistency, Isolation and Durability. I will dive into ACID compliancy in a future article, but for the time being a program that is ACID compliant can be considered a program that prioritizes highest possible reliability over performance speed. Therefore, when preventing data loss and minimizing database request failure are top priority, the use of a relational database may be advantageous. Additionally, a relational database is often valuable when your application models will never change. In the above example with tweets and users, a relational database is useful under the assumption that neither the user model or tweet model will ever change. If I did decide to change either of the models, while having important user data in the database, the availability of my program decreases due to having to take it offline and reorganize the data. Another advantage of a relational database is its inherent lack of data redundancy. If properly organized, a relational database will almost always be normalized, which means that the database minimizes redundancy as much as possible. noSQL databases aren’t always normalized and sometimes can create redundancies unless they are well planned ahead of time.
As a final comparison, a relational database prioritizes organization and consistency where noSQL databases prioritize dynamism and speed.
Final Thoughts:
Relational databases are remarkable for all the benefits they give their users, including organization, ACID compliance and consistency. Their strengths have made them very popular in the business world and they are a great fit for many applications. That being said, check back for my new article next week on document databases to take your first step into the world of noSQL.