We live in a digital age where data is king. To efficiently manage that data, you need a database management system (DBMS). A DBMS lets you store, retrieve, and manipulate your data. It functions as a mediator between the database, applications, the developer, and the user interface (Figure 1). You can use a DBMS for simple data storage and retrieval or for more complex data-driven tasks.

Figure 1: A DBMS acts as the middleman between your database and your end users.

When it comes to choosing a DBMS, you may be overwhelmed with options. A quick Google search pulls up dozens of DBMS solutions. You'll find open source and closed source solutions. Some DBMSs use SQL to structure their data, while others go the NoSQL route. Finally, some DBMSs are better suited for enterprise environments.

To narrow the field, you need to consider your project's data management needs. In this article, I will explain the advantages of an open source DBMS solution, break down the differences between SQL and NoSQL DBMSs along with some examples of each type, and provide some criteria for choosing a DBMS. Let's get started.

Why Open Source

The first thing you should consider in selecting a DBMS is whether to use closed source or open source software. With closed source (proprietary) software, access to the source code is restricted. Open source software, on the other hand, gives the user the right to freely use, modify, and share the source code.

Proprietary solutions are not without benefits. Sometimes a proprietary DBMS offers a unique solution that happens to fit your needs. A vendor might offer 24/7 support from a single source or build protection for added security. However, all of this comes at a price and can result in vendor lock-in, limiting your ability to evolve to meet changing business needs.

An open source DBMS solution, on the other hand, offers many benefits, including greater flexibility, lower cost, no vendor lock-in, faster innovation, quality control, and data portability.

An open source DBMS is also more cost effective. By its nature, it is free to download. You also avoid licensing or registration fees for reusing, modifying, or distributing the software, and you won't be surprised by potentially rising renewal costs when your proprietary software subscription comes due.

You can also sidestep vendor lock-in with an open source DBMS. You won't be forced to purchase bundled technology that doesn't meet your needs. And if those needs change, you are free to redesign your system. With an open source DBMS, you can easily scale up or scale down to respond to environmental changes. In addition, you are free to try out new open source apps without affecting your budget.

You will find open source DBMS solutions at work in a wide range of industries, including e-commerce, healthcare, government, nonprofit organizations, financial services, and the high-tech field.

Common DBMS Types

While there are several types of DBMSs, the two most common are relational DBMSs (RDBMSs) and non-relational DBMSs. An RDBMS stores data in a highly structured format. Most RDBMS systems today use Structured Query Language (SQL) to store and manage the data. A non-relational DBMS, more commonly known as a NoSQL DBMS, handles less structured data. Both have their strengths and weaknesses. Ultimately, your project's data will dictate which type will provide the best solution.

SQL DBMSs

Used as back-end data systems for decades, SQL DBMSs are the most commonly used type of DBMS. An SQL-based RDBMS implements a predefined strict schema, which defines how the data is organized (including logical constraints such as table names, fields, data types, and relations). With a focus on consistency and availability, an RDBMS works best for data that is structured and related.

Data in an RDBMS is stored in tables consisting of rows (or records) and columns (or record attributes). Each table represents a relation with the rows holding individual records that pertain to that relation. You can connect one table to another using either a primary or foreign key relationship. A primary key functions as a unique identifier for each row (aka record) in a given table to prevent records from having the same value. A foreign key lets you link tables; it is a column or set of columns in one table that references a primary key in another table. By combining rows from two or more tables based on a shared related column, you can perform complex joins.

To ensure that database transactions (defined as a series of operations) are processed reliably, an RDBMS maintains Atomicity, Consistency, Isolation, and Durability (ACID) compliance. ACID compliance is an all-or-nothing approach – either all changes within a transaction are committed or none of them are. If a transaction is ACID compliant, you are guaranteed that a database is consistent before and after the transaction. Mission critical applications in particular require ACID compliance.

Most RDBMSs scale vertically, with the data residing on a single server. To scale up, you can add more power to the server (CPU, GPU, RAM), but scaling usually requires downtime because you have to take the server offline to make any upgrades. You can scale an RDBMS horizontally, where the data is spread or shared over multiple servers, but it is a much more difficult process. The complexity of maintaining ACID compliance and managing distributed transactions and joins can require data structure changes along with other design considerations.

Optimized for speed, RDBMSs offer fast SQL queries. They perform well for intensive read/write operations on small to medium datasets, but performance can begin to suffer if the number of user requests or the amount of data grows. To improve data retrieval speed, you can add indexes to data fields to query and join tables.

If you have highly structured data that doesn't change frequently, an RDBMS is a good choice. It offers a higher degree of data integrity, is able to handle complex queries, and is a better choice for transaction-oriented systems thanks to its ACID compliance. Examples of open source RDBMSs include MySQL, MariaDB, PostgreSQL, Firebird, and CUBRID.