What is a Relational Database?

A relational database is a type of database that stores and provides access to data in tables, and relates data in one table to similar data in another table.

In a relational database, each table consists of rows and columns. Each row represents a record, and each column represents an attribute of the data. Think of an Excel spreadsheet — every tab in the file is a table, every row is a record, and every column is an attribute.

Relational databases have been around since the 1970s, are used in a broad range of use cases, and remain a popular choice even today because they offer a mature, stable, and proven technology You can expect to find them managing data for e-commerce applications, inventory management, payroll, customer data, and more.

In this article, we’ll explain how relational databases work, discuss their benefits, compare and contrast alternative systems, and describe some more popular systems available today that can handle relational database use cases.

In the early years of computing, each application typically stored its data in its own unique format. Storing data in so many different ways made it difficult to maintain systems and integrate them as needed. The relational database model arose as a way to standardize how developers could work consistently with data at a high level while leaving the details of the underlying operations and storage format(s) to the software.

The relational data model organizes data in tables consisting of rows and columns, which allows RDBMS to efficiently retrieve and combine data from different tables through the use of relational operations. The following illustrates how a relational database might keep track of children at a summer camp. Each row contains the information for a specific camper, including a unique ID number along with the child’s name, age, and favorite food.

For the unique ID, also known as a "primary key", its purpose might not be immediately obvious, but that’s what makes it possible for other tables in the database to track any related data—things like dates of attendance, cabin assignments, activities, certificates, awards, and so forth—by including the camper’s ID number in the subordinate tables. The ability to join many different tables by such IDs is what makes relational databases… well, relational.

Relational Database Management Systems are the software systems that manage relational databases. While a relational database itself is some collection of structured data on conceptual tables, which are maintained on storage media in various formats, the RDBMS is what makes it possible to interact with the data.

For example, a relational database system makes it possible to administer multiple relational databases on one or more servers and accept commands to create, read, update, or delete data (called “CRUD” operations) in tables.

Relational databases execute commands through what’s known as Structured Query Language (SQL). SQL has been standardized across RDBMS systems to perform CRUD (and other) operations in a consistent way, so the same SQL statements can be executed by any RDBMS that supports the standard.

To return to our example, elementary SQL statements could be executed to insert a new row for Tom, who is 13 and likes spaghetti or update Lisa’s favorite food to ice cream. SQL could also retrieve all the rows in the table, or a filtered and sorted subset of only the children between certain age ranges who share a favorite food.

That said, SQL isn’t a requirement for a system to be classified as being relational. Modern document-relational databases like Fauna offer all the properties of a relational database without offering SQL as the primary query language.

From a development standpoint, relational databases present a more structured view of the world. The relational structure fits well with modeling real-world business entities (e.g. customers, orders, catalog, items etc.) and the business relationships that exist between them.

SQL statements themselves are often simple enough to be human-readable, and lower the barrier to entry for application developers. Given its age, SQL has become a common language familiar to many developers and database administrators.

A key benefit that relational databases provide is data integrity. Relational databases provide “ACID” guarantees, a major value for business-critical operations. ‘ACID’ stands for Atomicity, Consistency, Isolation, and Durability, all of which refer to important properties of a transaction, a term used to describe a unit of database work. Let’s explain these properties one at a time.

Atomicity is that property of a transaction which ensures that it’s “all or nothing”. That is, the transaction either succeeds as a complete unit of work, performing its various CRUD (and other) operations, and is logged as a single commit to the relational database, or fails similarly. In case of failure, atomicity requires that any parts of the transaction already executed are rolled back completely, leaving the relational database exactly as it was before the transaction began.

It’s easy to see why this is important. Consider a user placing an order for an item at an e-commerce store. Buying an item might mean that while the user orders a “single” thing, this results in three different items being pulled from internal inventory. The first two might not be a problem, but if the third is out of stock the entire transaction must fail and be rolled back. Atomicity supports this type of common business logic.

Consistency goes hand in hand with atomicity, insofar as it requires the data to always be in a consistent internal state when a transaction starts and when it ends. All of the changes to the underlying data for the tables and other structures must be complete and correct, so that each successful transaction “moves” the database data from one well-defined valid state to another with nothing left hanging or yet to be done.

This might sound much like atomicity, but it’s subtly different as demonstrated by many NoSQL systems that instead feature partial or eventual consistency. Such systems often reply to commands faster because the actual details of data storage might not be complete when they respond to the calling client. This can be a great benefit when creating high-performance websites that scale to serve large numbers of users simultaneously. The key here is some NoSQL databases enable you to trade consistency for speed on a per call basis when you need to. Relational databases do not have such a capability.

Now we come to isolation and durability. When many different users are interacting with the same relational database at the same time, each attempting various transactions against potentially the same set of tables, it’s important that each user sees only their view of the data. Reading data from a table to which a different user is simultaneously trying to write must show the existing data and not the changes to avoid things “leaking” out of a transaction that may yet fail. Isolation ensures that the “view” across the entire system remains consistent and unchanged by other operations while a given transaction is in process, as if it were running in isolation.

Durability requires simply that any successfully committed transaction’s details are permanently recorded once completed. The underlying data representation changes must be applied in their entirety so that no loss occurs—even in case of caching, hard drive or other system failures.

There are many different relational database offerings available in the market today. Some require on-premise hardware, which means you’re responsible for all the provisioning, maintenance, and upkeep. Others offer cloud-hosting options but effectively give you remote databases to which you can connect but must still administer and manage yourself. Still others remove all the administrative burdens, the vendor handling all the “grunt work”, so that you can focus on creating your applications.

When architecting your database, it’s important not only to consider your options for the present but provide a roadmap for easy scaling later. The following table compares some common examples in light of the various factors we’ve discussed.

In this article we’ve discussed how relational databases and non-relational offerings can satisfy different use cases along with their strengths and weaknesses. To summarize, relational databases are best at enforcing relational integrity while offering ACID guarantees and easy programming through SQL. But all of that comes at a cost of limited flexibility and performance.

In contrast, non-relational systems offer far greater performance and often improved flexibility at the cost of some (or all) of the ACID guarantees so important to business-critical applications. They work best for a different set of use cases.

That’s why Fauna combines both the flexibility and performance of non-relational systems with querying and transactional guarantees of relational databases without compromising simplicity and scale through its document-relational model.

Fauna is the first document-relational database, offering the benefits of document and relational databases in a package that is attractive to developers. Like other document databases, data in Fauna is stored in JSON documents that are initially schemaless, providing flexibility in early application development as schema evolves. Later in application development, optional schema support (a feature that is currently in early alpha) allows developers to incrementally start enforcing the shape of data across groups of document collections.

Unlike other document databases, documents stored in Fauna can reference each other based on attributes, allowing developers to use arbitrarily complex joins in the database layer and eliminating the need for multiple round trips and custom join logic in application code. All requests execute in the context of a transaction, which means that the result is guaranteed to be consistent and up to date at the snapshot time of the request. Fauna’s novel architecture allows the database to be scalable and correct, offering strict serializability — the highest level of consistency guarantee — without compromising on performance.

All of this functionality is delivered via an API, which removes the burden of operating, upgrading, and scaling a database from developers. The API supports queries that are implemented in the Fauna Query Language (FQL), which is Turing Complete, so transactions can include effectively arbitrarily complex logic. To learn more about the design of Fauna’s Query Language, visit our blog.

Finally, Fauna is free to sign up, easy to get started, and offers clear and simple pricing. If you think Fauna could be a good fit for your application, why not give it a try today?