Relational databases are also known as Relational Database Management Systems (RDBMS) or SQL databases. They organize and store data in a tabular format of rows and columns, making it easy to understand how data relates to one another. The most important ones are MySQL, SQL Server, PostgreSQL, and Oracle.
When should relational databases be used?
Need to store structured data - Relational databases should be used when there is a need to store and manage large amounts of data in a structured and organized way. For example, a relational database would be a good choice for storing data about customers and their orders in an e-commerce application, because each customer can have multiple orders and each order is associated with a specific customer. They are particularly useful for data that is interrelated, such as when multiple tables need to be joined together to retrieve information. It will be easy to retrieve interrelated data if it’s stored in a structured format.
When dealing with financial transactions - The ACID properties in relational databases gives you a guarantee around data atomicity, consistency, isolation, and durability when dealing with transaction operations.
When dealing with frequently changing data - Relational databases are well-suited for use cases where data needs to be queried and updated frequently, and where data integrity and consistency are important. They are commonly used in business and enterprise applications.
Benefits of Relational Databases
Query capabilities - It provides a powerful query language using SQL to allow easy data retrieval and data manipulation.
Simplicity - The main benefit of relational databases provides is an intuitive way to store data and allow easy access to related data points. It’s free from complex structuring. This is why it’s commonly used by organizations that handle large amounts of structured data, from tracking products to handling transactions.
Normalization - Relational databases employ normalization to reduce data redundancy.
Data Security - It provides multiple levels of security, including user-level to system-level security which protects data stored in the database.
ACID Compliance - Relational databases supports ACID compliance. ACID is a concept that describes four properties of a transaction of a database system that ensure the accuracy and integrity of data. A transaction is a group of operations that are executed as a single unit of work. An example of a transaction is when transferring money between bank accounts. Money must be debited from one account and credited to another. The key property of the transaction are:
Atomicity - When performing a transaction, all the operations are performed or none of them are. Atomicity ensures that if a debit is made successfully from one account, the corresponding credit is made in the other account.
Consistency - Consistency ensures that the transactions maintain data integrity, leaving the data consistent and correct. Data constraints must be followed. For example, we can have a constraint in the amount column that the data cannot be negative. If the transaction leaves data in an invalid state, the transaction will be aborted and an error is reported.
Isolation - Isolation means that the intermediate state of the transaction is invisible to other transactions until a commit is done (concurrent control). For example, Account A has $1,000 and there are two transactions happening at the same time. Transaction A wants to transfer $1000 to another account and transaction B wants to transact $200 to another account. If these two transactions were allowed, they would leave the account in an invalid state(-$200). To prevent this, a database should only allow only one transaction on an account at a time. The transactions should be done sequentially and put in some sort of queue.
Durability - Durability ensures that when a transaction is completed successfully, changes will persist in non-volatile memory even in the event of system failure.
Limitations of Relational Databases
Expensive - The main drawback of relational databases is increased costs. Relational databases are costly to set up and maintain. It requires performance hardware and a lot of storage which is costly. The initial cost of software can be pricey. It gets worse when you factor in the cost of hiring skilled personnel to manage the relational databases.
Scalability - Relational databases may struggle to handle very large amounts of data or a high volume of concurrent transactions. Relational databases are typically designed to run on a single server, you can only scale vertically. Vertical scaling is limited to processors and memory upgrades. To scale a relational database effectively it has to be distributed onto multiple servers. Handling tables across different servers is difficult.
Flexibility - Relational databases are designed to store structured data, making it difficult to handle unstructured or semi-structured data such as text or images. They require a schema which makes development difficult if you don’t know the data shape up front.
Performance - Relational databases may have performance issues when executing complex queries or when dealing with a high number of joins.
Limited Indexing - Relational databases typically have a limited indexing capability, which can lead to poor query performance for large datasets.
