When To Use Relational Database?

Relational databases have been the dominant database technology for over 40 years. They remain optimal for many applications and use cases, especially when it comes to structured business data. This article provides a guide on when a relational database is the right solution.

Overview of Relational Databases

Relational databases store data in relations, more commonly called tables. Tables consist of rows representing records, and columns representing attributes of each record. For example, a table storing customer data may have columns for ID, Name, Address, Phone Number etc.

Relationships between records are established by linking tables through Primary Key – Foreign Key relationships. Joins between tables allow related records to be queried in a single SQL statement.

Some key characteristics of relational databases:

Structured Schema – Tables require a predefined schema specifying columns and data types.
ACID Transactions – Support transactions that follow atomicity, consistency, isolation and durability principles.
SQL Queries – Use declarative SQL statements to query, manipulate and define data.
Row Storage – Organize data in rows containing all attributes for a record.
Scale Vertically – Scale by adding processing power, memory and storage on a single server.

Here are some of the top use cases of relational databases.

Online Transaction Processing (OLTP)

Relational databases are the standard technology for transactional systems that capture and process operational data. Common examples include:

Order Processing – Managing orders, inventory, logistics, fulfillment.
Banking – Account transactions, interest calculation, lending.
Ecommerce – Shopping carts, payments, shipping, taxes.
Patient Records – Appointments, medical history, billing.

Why Relational For OLTP

ACID Compliance – ACID transactions maintain consistency across statements.
Referential Integrity – Foreign keys protect relationships and minimize data corruption.
Performance – Tuned for fast Inserts, updates and point lookups via indexes.
Scalability – Scale vertically by adding resources to handle more transactions.
SQL Standards – SQL guarantees interoperability between vendors.

If your application needs to efficiently process business transactions while maintaining data accuracy and consistency, choose a relational database.

Structured Business Data

Nearly all enterprises rely on relational databases to store their structured operational business data including:

Customer information (CRM)
Product catalogs
Financial data
HR data
Manufacturing data
Inventory
Supply chain data

Why Relational For Business Data

Structured Schemas – Business data fits neatly into tables with predefined columns.
Validation – Constraints and data types ensure data accuracy.
Flexibility – Standard SQL makes evolving schemas and queries easy.
Transactions – ACID properties protect integrity of business data.
Ecosystem – Reporting, visualization, ETL and BI tools expect SQL data sources.

The tabular structure, validation and query power make relational databases ideal for ERP, CRM and business systems.

Legacy Applications

Organizations have decades of legacy applications built on top of relational databases like Oracle, DB2, SQL Server. Major reasons why migrating away can be difficult:

SQL Dependence – Massive amounts of SQL code and procedures to rewrite.
Referential Integrity – Foreign keys protect critical data relationships.
Data Integrity – Complex integrity logic baked into the RDBMS schema.
BI Integration – Existing reports and analytics depend on the database.
Proven Reliability – Legacy RDBMSs power critical systems reliably for years.

Options for Legacy Systems

Given the ROI challenges in migrating legacy systems, better options exist:

Leverage New Features – Modern RDBMSs add JSON support, new cloud features etc.
Polyglot Persistence – Use specialized data stores for new data, while keeping old data in RDBMS.
Offload Read Traffic – Use replicas, caches and OLAP systems to handle reads.

So for many legacy applications, relational databases continue to provide the most prudent option.

Regulatory Compliance Needs

In regulated industries like healthcare, finance and public sector, relational databases help meet compliance requirements through:

Data Typing – Columns enforce strict data types.
ACID Transactions – Ensure data accuracy and consistency.
Auditing – Track DB changes and user activity.
Backups – Facilitate point in time recovery.

Many regulatory standards like SOX, HIPAA, PCI-DSS explicitly or implicitly mandate the use of relational database capabilities.

Data with Clear Structures

Relational databases work great when the inherent data characteristics match a tabular, structured format including:

Well-defined entities with specific attributes.
Clear relationships between entities.
Data integrity rules that can be encoded declaratively.
Queries focused on filtering, aggregations, and join operations.

Examples of Structured Data

Sales transactions with customer, product, sales amount details.
Product catalog with pricing, inventory, supplier information.
Student records with name, classes, grades, report card calculations.

The predefined schema and SQL provide an optimal environment for structured data.

When To Use Relational Databases

Here are the top 8 situations when we can use relational database. Some of them are explained above.

Online Transactional Processing (OLTP) Relational databases are ideal for transactional systems like order processing, banking, ecommerce, patient records due to ACID compliance and performance.
Structured Business Data The tabular schema and SQL querying of RDBMSs fit perfectly for structured data like CRM, ERP, finance, HR, manufacturing.
Legacy Applications Legacy apps often can’t migrate from relational databases due to SQL dependencies, integrity needs, and reliability of existing RDBMSs.
Regulatory Compliance Capabilities like ACID transactions, auditing and backups help relational databases meet regulatory data compliance requirements.
Clear Data Structures Relational databases suit applications with well-defined entities, clear relationships, and query patterns than can leverage SQL.
Data Integrity Declarative constraints, foreign keys, and ACID properties provide the highest guarantees of data integrity.
Data Analytics Many analytics tools and data pipelines are optimized for the SQL querying and structure of relational databases.
Vertical Scalability RDBMSs can effectively scale workloads by adding more resources like CPU, memory, storage to a single server.

When Not To Use Relational Databases

Relational databases are not the best fit in certain scenarios:

Need to store unstructured or polymorphic data.
Need low latency access to simple data.
Requires massive scaling and geo-distribution.
Seeking a flexible schema.
Need graph-style analysis of interconnected data.

For these use cases, non-relational databases like key-value stores and graph databases may be more optimal.

Conclusion

Relational databases remain the ideal choice for structured business data, especially in transactional systems. Their ACID transactions, SQL query power, and rock solid data integrity capabilities make them indispensable for enterprise applications. Legacy systems that have decades of development optimized for relational models may find it most prudent to stay put. Startups with structured data also do best to leverage the strengths of mature RDBMSs.

Where relational databases fall short is their rigidity for rapid iteration, unstructured data and massive scaling. This is where next-gen non relational datastores open new possibilities. The key for application architects is to use the right database for each workload rather than trying to force-fit a single database for everything.

More to Read

When To Use Relational Database?

Overview of Relational Databases