Managing relational databases is complex—but it doesn’t have to be

Administering and managing databases with multiple tables can get complicated quickly—to say nothing of actually querying those databases. A managed relational database offers all the convenience of a neatly organized collection of data across tables and none of the managerial complexities of putting that data to use.

What is a relational database?

A relational database stores its data in one or more tables consisting of rows and columns, where each row represents a thing and each column represents something about that thing. Attributes label each column and row so users can relatively easily compare data point to data point.

In the simple example database above, there are four rows and five columns. Each column contains information about an attribute of the student and a relational key from each row. A user can query this database to answer a question about how tall Jess is, or how much they weigh. The ID column assigns a unique key to each row to make querying the database faster.

Typical databases contain many more values and variables than this one does—in fact, it’s not unusual for databases to contain millions of records—which is one of the reasons why managing a database can become a complex task.

Relational databases are also commonly called SQL databases because they’re all written in SQL (Structured Query Language), a coding language used to execute queries, edit data, and fetch data from the database.

A relational database management system (RDBMS), meanwhile, is merely the interface or software a person might use to manage, query, or otherwise interact with their relational databases.

What’s a managed relational database?

A managed relational database is a cloud computing service in which a user pays a cloud service provider to create and manage a database. Unlike an on-premises database, users don’t have to set up or maintain a database on their own and instead outsource the responsibility to oversee the database’s infrastructure to the database provider.

Typically, maintaining a database takes time and expertise that most developers don’t have. Databases need to be provisioned, configured, and continually maintained so relations aren’t broken as more data is added.

There are a host of benefits to using a managed relational database:

• Focus on building apps rather than spending time configuring databases
• Automatically backup data so there’s never a risk of loss
• Data is secured to your specifications

What is SQL?

SQL is a coding language designed specifically for managing data in relational databases. Fun fact: The International Organization for Standardization made SQL the standard database language in 1987.

What is ACID compliance?

ACID is a set of database transaction properties that ensure data validity in operations, like transferring money between two bank accounts, despite errors, accidents, and mishaps. All databases need to demonstrate the four ACID properties to be considered ACID-compliant:

• Atomicity: Every component of a database transaction needs to work as it’s supposed to in order for the transaction to be completed, such that either the entire transaction occurs or nothing occurs.
• Consistency: Data written to the database must be valid according to all defined rules to help minimize transaction errors.
• Isolation: All transactions happening at the same time need to operate such that each transaction doesn’t affect a separate one.
• Durability: Each transaction needs to survive any technology failure, like a power outage, so that all transactions are permanently reflected in the database.

What is the difference between a relational database and object-oriented database?

There are several key differences between relational databases and object-oriented databases:

• In relational databases, simple data is stored in tables with columns and rows
• An object-oriented database can handle large and complex data which is stored as objects together with functions that operate on the data
• A relational database’s goal is to handle data and keep it independent from your application
• Object-oriented databases main objective is to implement data encapsulation
• A relational database uses keys to identify data
• An object-oriented database uses an Object ID to represent an object from a group

How do you scale a relational database?

You can scale a relational database both vertically and horizontally. To scale vertically you would add more capacity to a server such as more CPU and disk power to enhance your storage process. Scaling vertically could also involve mormalisation by splitting data within columns into separate tables.

Another alternative is to scale horizontally by adding more machines. Scaling horizontally can also involve sharding or splitting a row into separate tables

When would you use a relational database?

It depends entirely on your use case, typically you would consider relational databases when you have simple, predictable, and structured data like financial information. It’s not uncommon to use both relational and object-oriented databases together for the same use case to take advantage of the best of both worlds.

When should I not use a relational database?

If your data is structured as a hierarchy or a network it may not be the right fit for relational databases, because it can be a challenge to go through several tables using keys to chase what you are looking for. Apps that are read-mostly do not benefit from relational databases as there is a lot of overhead for simple read access. Finally, if you have no expectation of unexpected queries a relational database isn’t needed.