What is a Relational Database? A Beginner's Guide

Relational databases are the gold standard in the industry when it comes to storing and managing data.

Relational databases store data in tables of rows and columns. Each row is a single record, and each column gives a single piece of information about that record. Furthermore, tables are related to each other through common columns.

Thanks to its intuitive structure, relational databases are exceptionally powerful when it comes to organizing and manipulating data.

So, let's delve deeper into this topic to understand why relational databases are so effective.

The Relational Model

Relational databases employ the relational model: data is organized into tables of rows and columns, where each row is a single record, and each column is a single attribute of that record.

Each table must have a single column (or a group of columns) whose data is unique. Such a column is known as a primary key, and they are necessary to avoid duplicate data.

Relationships between different tables are maintained by foreign keys: columns that refer to the primary keys of other tables. Unlike primary keys, foreign keys are not required to have unique data.

Let's take a practical example to understand this concept better.

Example: A Simple E-commerce Database

Consider an e-commerce website where you can buy and sell goods.

The database of such a site will usually consist of separate tables that store information on customers, orders, shipping, and more. Some of those tables might look something like this:

Table Name: Customers

Table Name: Orders

As you can see, the first table only stores customer information, while the second table only stores order information. Similarly, we can store other kinds of information on other tables.

Let's look at the basic properties of both tables:

Table Properties

Notice that both the Customers and Orders tables have a common column: customer_id. It is through this column that we link the two tables, thus forming a relationship between them.

Note: A table can have multiple foreign keys, but only a single primary key. However, the primary key itself can be a combination of multiple columns.

Database Transactions and ACID Principles

A database transaction is a set of database operations that are executed together as a single, atomic operation. For example, a transaction might include updating the age of a customer in the Customers table and inserting a new order into the Orders table.

Relational databases follow the ACID (Atomicity, Consistency, Isolation, and Durability) principles to ensure the reliability and consistency of transactions, thus ensuring the integrity of the data.

Let's explore each of the ACID principles in detail:

Atomicity

A transaction is always treated as a single unit of work that is either completed entirely or not at all. In other words, a transaction cannot be partially successful: it's either a complete success or a complete failure.

If any part of a transaction fails, the entire transaction is rolled back to its original state to prevent any changes to the database. This ensures that the database remains consistent and free of corruption.

Consistency

Database transactions must follow a set of rules or constraints that ensure the validity of the data in the database. For example, if a transaction is attempting to add a record to a table with a column that has the UNIQUE constraint, then that column value must not be a duplicate of previous values.

Isolation

Each transaction must be executed independently of other transactions i.e., transactions can be executed concurrently, but one must not affect the other. This is achieved by locking the rows affected by the transactions so that data being accessed or modified by one transaction is not accessed and modified by another transaction until that transaction is complete.

Durability

Once a transaction is successful, the changes made to the database are permanent and will not be lost in the event of a system failure or crash. To do this, databases must use transaction logs, which record all changes made to the database during a transaction. In the event of a system failure, the transaction log can be used to recover the database to a consistent state.

RDBMS: Managing Relational Databases

Relational databases are, at the end of the day, a collection of related tables. We still require software to manage the database and perform operations on it. Such software is called a Relational Database Management System (RDBMS).

Almost all RDBMS use SQL (Structured Query Language) to run queries on their databases. A query is an operation that is performed on the database, such as creating a table, inserting records into it, performing mathematical operations, etc.

Each RDBMS software comes with its own version of SQL. These SQL versions are more or less the same, with slightly different syntax and supported queries.

Some popular RDBMS are:

• MySQL
• Oracle
• Microsoft SQL Server
• PostgreSQL
• SQLite
• IBM DB2

Advantages of Relational Databases

Having learned of the essential relational databases, let's shift our focus to their major perks:

• Intuitive: Relational databases organize data into separate related tables of rows and columns, making them very intuitive and easy to understand.
• Data Integrity: ACID principles ensure that data is accurate, consistent, and free of errors and duplication. The data is also protected from partial or unsuccessful transactions as well as system failures.
• Flexibility: Relational databases can store different types of data. It is also easy to expand them in order to accommodate new data types or changes in business requirements.
• Security: Strong security features such as access controls and encryption (among others) protect data from unauthorized access or modification.
• Querying: SQL is a powerful query language that allows users to search and retrieve data using different criteria. It is also widely used and well-supported.
• Scalability: Relational databases can handle relatively large amounts of data and can be scaled up or down as needed. They can also be optimized for performance, allowing them to easily handle high-volume transactions or complex queries.

Disadvantages of Relational Databases

In spite of their numerous advantages, relational databases also come with a few drawbacks. Here are some of the major ones:

• Careful Planning: You need to carefully plan the tables and their relationships before you can actually use the database. Poor database design can lead to poor performance and inefficient queries.
• Complexity: Larger databases often have a lot of tables and complex relationships. This complexity can make it more difficult to maintain and troubleshoot the database.
• Scalability Limitations: While relational databases are highly scalable and can handle relatively high volumes of data, they still have limits and cannot handle the data volume of modern apps such as social media. Also, the database performance cannot be improved satisfactorily by adding more servers to the network, requiring us to upgrade the hardware of our server instead.
• Cost: Relational databases can be costly to set up and maintain thanks to licensing fees, hardware costs, and the high salaries of database engineers.
• Limitations of SQL: SQL is extremely powerful when it comes to querying structured data. But it cannot process unstructured or semi-structured data, such as those from social media or web logs.
• Flexibility Limitations: Data consistency can be a disadvantage if the data needs to be changed or updated frequently. For example, it may be difficult to make changes to the database without affecting other parts of the database or causing data inconsistencies.
  

When to Use Relational Databases?

While relational databases are extremely popular, they are not suitable for each and every situation.

Keeping that in mind, here are some general cases where you might be better off with a relational database:

• Structured Data: Relational databases are ideal for handling data that can be arranged into tables of rows and columns.
• Small to Medium-sized Data: Relational databases can provide a simple, reliable solution for storing and retrieving datasets that aren't extremely large.
• Data Validity and Security: Relational databases have many constraints that facilitate the validity of data. If security is a high priority, then relational databases are probably the better option.
• Transactional Applications: Relational databases are great for transactional applications requiring ACID properties, i.e., all transactions must be completed or rolled back.
• Business Applications: Relational databases are commonly used to store crucial data on people and products. Examples of such applications include Enterprise Resource Planning (ERP) systems, accounting systems, inventory management systems, and e-commerce websites.
• Ad-hoc Queries: These are database queries that cannot be solved with standard, predefined queries. SQL is well-suited for such problems since it allows us to write complex queries that can retrieve and manipulate large amounts of data.

Specific Use Cases

Now that you have a general idea of when you should use a relational database, here are some specific areas and fields that require its use:

• Banking and Financial Applications
• Healthcare Applications
• Human Resources Applications
• E-commerce Applications
• Content Management Systems (CMS)

Conclusion

Relational databases are highly organized yet intuitive, and they have been tried and tested for a wide range of applications. In fact, the relational model, along with the ACID principles, has been the gold standard in the database industry for many decades.

However, it is also important to remember that relational databases are not appropriate for all situations. Being cognizant of this fact, you should carefully consider all the pros and cons of relational databases before using them in your application.