What is a Relational Database?

You’ve probably come across the term database at some point in your life. Databases are a set of data that is stored on a computer.

Hospitals use databases to track the treatments they administer; accountancy firms use databases to keep track of accounting records; and web applications use databases to keep a list of their users and content for their platform.

But what about relational databases? How do they work? What’s the difference between a database and a relational database? 

Those are all good questions and in this guide, we’re going to break down the basics of relational databases and how they work.

What is a Database?

Databases allow you to store data on a computer. In a database, data is stored using tables. Tables are like the blueprint for storing a particular type of data. Each table has a set of columns which define what data can be stored in a database.

Within each table you can store records which are individual entries in your database. Each value in a record should correspond with the columns in the table.

Here’s an example of a database:

In this table, our database contains four columns. These are: id, title, text and meta. The title, text and meta columns store strings. The id column stores an integer which we can use to uniquely identify each post.

Our database contains one record. This record stores data about the blog post with the title “What is a Relational Database?”. 

What is a Relational Database?

A relational database is a special type of database. It allows you to access data in relation to other pieces of data in the database.

Data in relational databases is stored using rows and columns. Rows, or “records”, are individual entries in the database, and tables. Tables store the structure of a particular type of data. In a relational database, tables can be linked – related – to each other. This allows you to create connections between similar data points in the database.

Suppose you are creating a database for a blog. You want your database to store two tables: the users who can sign in to your blog and a list of posts for your blog.

You may want to link these two tables together so every post on your blog is associated with the account of the user who created the blog post. This is made possible in relational databases. We’ve illustrated this in the diagram above.

In this diagram you can see there is a relation between our two database tables. Our “User” table is connected to our “Post” table. When we create a post, we associate that post with a user using the “createdBy” field. This helps us keep track of who has created a database.

In simple terms, relational databases are a way of organizing data.

How do Relations Work?

Every row in a table is assigned a key, which is a unique identifier used to track that particular record. The keys that are associated with a record are called primary keys.

When you want to create a link between two tables, you can reference that key in another table. This is called creating a foreign key. In our example above, each record in our “User” table has a primary key. Each User value in our “Post” table has a foreign key.

Think of it this way. Primary keys are stored in the primary place a piece of data is stored. Foreign keys are stored in another table and are used to reference data from a specific table.

What is SQL?

Relational databases can be queried using the Structured Query Language, or SQL. This is a standard language that allows you to interact with databases. While the technology itself is old, almost every database runs on SQL. It makes it easy to join tables with just a few lines of code.

SQL is not only useful for database developers. Using SQL, people who need to analyze data – such as marketers or salespeople – can gather information about the data stored in their database. For instance, a salesperson could use SQL to find out how many clients they have acquired in the past month.

What Are the Benefits of Relational Databases?

When databases were first used, every developer wanted to store data in their own way. This meant that you needed to spend a lot of time reading over a database schema to understand how a database was structured. Often, these databases were made based on inefficient structures because no standardized processes were used.

Enter the relational database. The relational model is a standard way of storing data in a database. It makes it easy to create connections between data. These connections allow you to create links between meaningful information by joining tables together.

In our diagram above, the main benefit of relational databases is clear. You can see the relationship between the “Post” and “User” tables clearly. A User is the owner of a Post.

There are a few other benefits to the relational model that you should know about.

Redundancy

The relational model reduces data redundancy. Information for a single record appears in one place, rather than in multiple tables. In our example above, a User record is stored in the “User” table. We don’t have to repeat that data in our “Post” table; all we need to do is create a connection between the two.

The reduced redundancy made possible by relational databases helps make data more consistent. When you update one record in a database, any value which is linked to that record will automatically see the new updates. If we updated a user’s email address in our above example, such a change would be immediately known by all the Posts which the user created.

Flexibility

The relational model gives you more flexibility over your applications. This is provided by SQL, which uses a language for creating tables called DDL, or Data Definition Language. This allows you to add columns, add tables, change relations and more. 

"Career Karma entered my life when I needed it most and quickly helped me match with a bootcamp. Two months after graduating, I found my dream job that aligned with my values and goals in life!"

Venus, Software Engineer at Rockbot

Find Your Bootcamp Match

What Systems Use Relational Databases?

Relational databases have been implemented by a range of database programming products. Most modern databases use SQL to process data or at the very least are capable of processing SQL.

Most of the databases you’ll encounter in small-scale applications are powered by SQL. Even larger applications use SQL and the relational model to store data.

In technology, relational database systems have a longer name: a Relational Database Management System. Here are a few examples of these systems:

PostgreSQL

PostgreSQL is an open-sourced SQL database. This means that it’s not controlled by any company; it is maintained by a community of developers.

PostgreSQL is commonly used for web application development. The system is easy to use and reliable. The system has also been in development for years which means there is an abundance of resources online about how to work with a PostgreSQL database.

The drawback to a PostgreSQL database is that it is slower than other databases like MySQL, which we’ll discuss below. It also doesn’t have such a large developer community as some other database servers.

MySQL

MySQL is the most popular SQL database in the world. It is most commonly used in web application development, often alongside the PHP programming language.

MySQL has been around since 1995 and there is a massive following of developers who have expertise using this database model. The system is also easy to use and inexpensive to set up. This makes it an ideal choice for applications of all sizes.

MySQL is not as effective at scaling as some other database systems and it lacks a few advanced features that are present in systems like the Oracle Database.

Oracle Database

Oracle, the multinational computing company, owns a technology called Oracle Database. This is a relational database system for large applications. 

Unlike MySQL and PostgreSQL, Oracle Database is not open-sourced. This means that you can’t spin up your own Oracle Database without a license.

Oracle has been known to scale well. That’s why it is used for applications such as banking, which have massive data querying and storage needs. The main drawback of using Oracle Database is that it’s not open source which means that there are fewer people who know how to use the technology.

SQLite

SQLite is an open-source database. It was designed to be as lightweight as possible. As a result, it can store an entire database in one file. This makes SQLite really good for small applications because you don’t need to spin up a larger database when you’re only going to be storing a few tables.

SQLite is commonly used for small web applications and in other applications such as MP3 players and cell phones.

SQL Server

SQL Server is a relational database system owned by Microsoft. It’s code is closed, which means that you can’t spin up a database without having a license. Most of the users of SQL Server are enterprise customers.

The SQL Server system can scale effectively. However, it can be expensive to operate an SQL server database at scale because you have to pay to use it when you pass a certain point.

Conclusion

Relational databases store data in tables. Each table stores a set of columns which correspond to values in each database entry. Relational Database Management Systems are used to manage data in a relational database. There are a wide range of these services available, although the most common are: PostgreSQL, SQLite, MySQL, Oracle DB and SQL Server.