A database management system, also called DBMS, is a program that allows admins to access, alter, and analyze a database. In this article, I’ll describe the functions of a database management system and review different types of databases. While this isn’t a comprehensive list of all DBMS types, it’s a start if you’re looking for a quick guide to common database management solutions.
What’s the Purpose of a Database Management System?
There are a number of administrative tasks that need to happen with the database, like tracking changes, or backing up and recovering data, and the right software allows you to make system-level changes more easily, while pinpointing any issues. Through the DBMS, admins can manage data, the database engine, and the database schema that sets the logical structure.
However, a critical piece to making sure you’re getting the most out of whatever DBMS you choose is your ability to monitor databases and tune database performance to ensure it stays efficient. I recommend using a tool like SolarWinds Database Performance Analyzer (DPA) to monitor and optimize many types of DBMS. I find this is the most robust of these relational DBMS monitoring tools, offering comprehensive yet easy-to-use functionality. Monitor databases like MySQL, Oracle, SAP ASE, DB2, and more from a single installation. Get the tools you need to actually optimize performance based on clear diagnostics. If you need a straightforward, streamlined way to monitor your databases, tune performance, and do it all from a well-designed interface, start with DPA.
Types of DBMS Software
There are a number of different database schemas out there, each with their own advantages and disadvantages. Choosing a database type can be a matter of balancing factors like organization, security, storage space, and efficiency. To start, your choice of DBMS will depend on which of the major database types you use.
Relational Database Management System
This is a kind of DBMS based on the relational model of data. Relational databases are sometimes referred to as SQL databases, after the coding language (Structured Query Language) used to program most relational database management software. A relational database essentially draws connections between tables or spreadsheets, within a database. As data is added to a database, simply adding columns to the same table would create a static and inflexible data set; over time, your database would become confusing and unhelpful. But in a relational database, tables must have a primary key column which uniquely identifies all data in that row. Tables can then use a foreign key to link to the primary key of another table.
One of the big advantages of relational databases is Atomicity, Consistency, Isolation, and Durability (ACID) compliance. The term ACID was coined by computer scientists in the 80s to describe how database transactions must proceed to preserve data integrity. ACID compliance is still considered the standard for insuring databases are valid even in the event of disruptions like errors and power failures and is a must for databases containing sensitive or regulated information.
Popular for decades, relational databases are particularly good for major organizations that need ACID compliance and have consistently structured data. This type of database is so widespread and versatile, it’s hard to say what specific kind of task or organization it’s best for, but relational database management systems are common options for financial systems, personnel data, and logistical information. Only more recently have NoSQL databases become a feasible and even common alternative.
NoSQL Database Management System
Non-relational databases are called NoSQL. They’re much less structured than relatable databases, and are document-centered, rather than table-centered. Data in NoSQL databases doesn’t have to fit into well-defined rows and columns. Many NoSQL databases are similar to file folders. This makes it much better for large and irregular collections of data, but also demanding of much more time and processing power.
NoSQL databases management systems are generally classified as one of four types:
- Key-Value: As the name suggests, a key-value database stores combinations of keys and values. It might look like a spreadsheet with only two columns. Riak and Redis are two common examples.
- Wide-Column Stores: Wide-column store databases are column-oriented, as opposed to most databases (like relational databases) which are row-oriented. This has meaningful implications for the organization of data. While wide-column stores might organize data by a key like other databases, the information attached to that key does not have to be as structured or regular. Examples include Cassandra and HBase.
- Graph: A graph database treats data as “nodes” and connections as “edges” between those nodes. These databases are (or can be) represented visually as graphs. Graph databases are used by most social networks and pretty much any website that “recommends” anything based on your behavior. For example, Netflix’s recommendations come from a graph database. HyperGraphDB is one example of this type.
- Document Store: Document stores are similar to key-value in that they consist of keys with each corresponding to a document. However, those documents can contain immense complexity and do not have to correspond with the structure of data contained within any of the other documents. MongoDB is an example of this.
A type of database that has become increasingly popular over the last decade is in-memory, abbreviated as IMDB or MMDB (for Main Memory Database System). These databases store data on RAM, providing extreme speed for compressed, unstructured data, but also somewhat less security in the event of disruptions (although advances have addressed this). Due to their speed, MMDBs are popular for telecommunications and mobile advertising.
Other DBMS Types
Besides these systems, options like columnar or cloud-based DBMSs could be relevant for your organization. Columnar is designed to be fast, with high levels of compression, so this type of DBMS is well-suited for data storage situations where there are a large number of similar data items. But in-memory analytics have made this factor less crucial. Meanwhile, for a cloud-based DBMS, a cloud service provider takes over the task of providing data storage and maintaining that data. Incorporating some amount of cloud storage is obviously becoming an increasingly popular option. But actually keeping the management system in the cloud is typically relevant just for companies that utilize managed service providers.
Best Database Management Systems
This is just a short list of some popular DBMS types:
- Oracle 12c: One of the biggest and oldest names in the game, Oracle is a popular DBMS. Their latest generation is optimized for cloud computing to help clients managing many databases simultaneously. As a relational database, it has robust functionality and top-flight security (including ACID compliance). On the downside, Oracle is hugely expensive, especially at the enterprise level. They do have a tier for smaller businesses, but it may not be enough. Oracle is a good choice for huge operations with sensitive, clearly structured data.
- Microsoft SQL Server: Another famous name that offers quality, if potentially expensive, service. However, Microsoft isn’t just a name here, it’s an ecosystem. So, if your company runs on Microsoft already, SQL Server will integrate well with your relational database system and vice versa. Other standout features include dynamic data masking for extra security, temporal data support, support for Linux, and visualizations for mobile. Disadvantages include the aforementioned cost.
- MongoDB: The most recognizable name in NoSQL DBMS, MongoDB is open-source, but the commercial version has better functionality. The commercial edition includes advanced security (which is especially important in non-relational databases that are inherently less secure), administration, support, and training. These are features you might not miss until you need them when it could be critical. As mentioned previously about document-based databases, MongoDB is well-suited for complex but not standardized data.
- Neo4j: Neo4j is a graph database tool and a relatively rare NoSQL platform that supports ACID compliance. As mentioned, graph databases are perfect for connections and visual representations, making them the preferred database for recommendation engines. I would recommend getting the Enterprise Edition, which is best for management and scalability.
How to Choose a DBMS
In summary, your choice of DBMS depends in part on the type of database you need, which depends largely on what kind of data you have and how you want to use it. A good DBMS is useful because it provides a centralized, unified view of data, giving you easy insight into where data is stored, and its current status. At the same time, it allows the use of data across applications without having to create multiple versions of the data. It’s true that a DBMS will utilize more memory and CPU power than file storage alone. However, most businesses find a DBMS is worth it, especially for large or sensitive data collections. When creating a data management software list of potential solutions for your organization, you’ll want to consider how you will be organizing your database infrastructure and how databases will be used and compare that with each type of database management system software to find the best fit.
Must-know Tips and Tools for Oracle Virtualization – Oracle is one of the biggest names in databases. We were lucky to have a chat with several Oracle directors about virtualization.
Maximizing Database Performance – MySQL Tuning Best Practices – MySQL is one of the most popular open-source DBMS out there, so any administrator should educate themselves on best practices.
SQL and Database Performance Tuning Guide and Checklist: Expert Tips – Mastering SQL, the most common language for relational databases, is essential for any administrator.