While Oracle and MySQL remain top picks for database systems, there are many others available, from big guns like Microsoft SQL Server to the increasingly popular MongoDB. Each has its own strengths and weaknesses, so your latest IT project may find you scratching your head as you try to decide on database software.
If you’re looking for a database platform, you probably already know the basics, but a database is a collection of data, information of almost any type, organized in a manner that can be accessed, managed, and updated either by other programs or by users directly. They are required to recall specific data on demand, like when a social media user looks back on their profile one year ago.
Databases can be installed on individual workstations or on central servers or mainframes. Applications are as varied as an industry might require; they are used to store and sort transactions, inventory, customer behavior, pictures, video, and more. Most business IT applications will require some form of database.
The first decision you’ll need to make is between desktop and server database. Desktop database management systems are licensed for single users, while server database management systems often include failsafe designs to guarantee they will be always accessible by multiple users and applications.
Some desktop database options include Microsoft Access (included with Office or Office 365 licenses), Lotus Approach, or Paradox. They are pretty inexpensive and use GUIs that make interacting with SQL simple for non-power users.
Chances are you need a server database management solution, if you’re reading this blog. They offer greater flexibility, performance, and scalability than a desktop database. Oracle, IBM DB2, Microsoft SQL, MySQL, PostgreSQL, and MongoDB are all popular options. MySQL, MongoDB, and PostgreSQL are all open source while they others are closed. Another open source database gaining popularity is Cassandra, released by Facebook.
The large vendors like Oracle and IBM have the advantage of longstanding popularity, meaning they now work with a variety of programming languages and operating systems. Microsoft SQL is conveniently integrated into the Windows Server stack and is relatively inexpensive.
Before choosing a vendor, you’ll need to ask the following questions:
One quick way to narrow down your options is to decide whether you need an SQL (Structured Query Language) based database or NoSQL. SQL databases are relational, which means they are sorted into a table and organized by each entry (the row) and its qualities (the columns). It is important to note that you have to predefine these qualities. NoSQL databases can have varying storage types, including document, graph, key-value, and columnar.
Document databases store each record in a document and documents are grouped in collections. The structure of each document does not have to be the same. Graph databases are best suited for data types that graph well, like trends. The structures have entries and information about the entries connected via line. Key-value databases use pairs of key-values to associate data. The key is an attribute which is then linked to a value. The resulting associative array is also called a dictionary, made up of many record entries, each of which contains fields. The key is used to retrieve the entry from the database. Columnar databases have column families, each of which contains rows. The columns do not have to be predefined and the rows do not need to have the same amount of columns.
Another important distinction between SQL and NoSQL is ACID compatibility. All SQL databases retain ACID functionality, while many NoSQL options do not. ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity means if a transaction has two or more pieces of information, they either all make it into the database or none do. Consistency means if a new entry fails, the data is returned to its previous state before the entry was transacted. Isolation means a new transaction remains separate from other transactions. Durability means data remains in its state even after a system restart or failure.
Note: a transaction refers to any retrieval or update of information.
SQL servers are generally not scalable across other servers, while NoSQL servers are often used in cloud environments as they can scale across servers, with many platforms including automation. If your data needs will not change in structure (meaning you know the categories of each entry are stable, like a contact database of First Name, Last Name, Phone, Address, E-mail, etc) and you don't expect massive growth, SQL might fit the bill.