There are dozens of relational database management systems that are available for implementation that it can be difficult for some to pick and choose what to use. Some of the common commercial databases that exist include SQLite, MS Access, Oracle, SQL Server, PostgreSQL, MySQL, and MariaDB.
SQLite is a self-contained database that is file-based and completely open-source. It is known for its portability, reliability, and performance even in low-memory environments. All the transactions are ACID compliant (atomicity/consistency/integrity/durability) even in cases where there’s a system crash or power outage. It is a unique one as it is a serverless database. Most of the other databases will require a server process that programs connect to perform database requests. SQLite however accesses the database and reads from and writes to the database disk file directly. By doing so, there is not any need to configure a server or need to configure programs to connect to the database. If they can access the disk in which SQLite is on, programs are able to access it directly. There are limitations with it as there is limited concurrency as only one process can make changes to the database at a time. Multiple processes can read and query from it. There’s also no user management with SQLite so as long as the underlying operating system gives users permission to access the disk file, the user has access to the database.
MS Access by Microsoft is not as popular as it once was but it is well recognizable. Similar to SQLite, the database is integrated into a single file that can be distributed. It has a well-developed GUI toolkit that allows smaller organizations to build forms, reports, and other functionality within the database. It does not require any special hardware although it does only run on Windows. An MS Access database is frequently thought of as a single user database, but it can support a limited number of concurrent users. One of the key benefits of MS Access is using it as a user interface front end for other databases that are more robust like SQL Server or other databases that offer ODBC connection. It is not one that should be used with any sensitive data, regulated data, if you need role-based security, need many concurrent users, or needs to be high performance.
SQL Server is a product from Microsoft that is much more robust with a variety of editions to gear towards newer users with Express to an Enterprise edition that has high-end data center capabilities. SQL Server isn’t only a database engine, but it has many different other tools including business intelligence tools that we have discussed in prior tutorials. SQL Server does use its own version of SQL with Transact SQL. It is a tool that is highly supported, but it also comes at a high price. The enterprise edition for example can cost upwards of $14,000 per-core license. Unlike MS Access, it can run on Linux as well as on the cloud environment.
Oracle has established itself as one of the top database systems used in enterprise solutions. Similar to SQL Server, there are many different variants of Oracle depending on the needs of the organization. The XE or Express edition is free for training purposes as Oracle only charges Enterprise use. The cost for Oracle is one of the highest and can be quite complex to determine the end cost. Oracle also has PL/SQL which is a procedural language that has more capabilities to group procedures and functions together into packages. There are endless tools and features available for Oracle which can be quite useful. Even with the high cost, it is extremely reliable, fast, and easy to support.
MySQL and MariaDB have been some of the most popular open-source databases. MySQL in particular is now owned by Oracle and power many of the largest databases including Twitter, YouTube, Airbnb, Facebook, and Netflix. MySQL was designed for speed and reliability. Once MySQL had been taken over by Oracle, the original MySQL developers split off to create MariaDB as a fork of the MySQL code as they had worried that Oracle had planned to kill MySQL as it was a direct competitor to MySQL as a database. MySQL does have to be installed on a server. There are a lot of third-party tools that can be used with MySQL/MariaDB including phpMyAdmin.
PostgreSQL is viewed as one of the most advanced open-source relational databases. It was created with the goal of being standard compliant and highly extensible. Standard compliance is a big element of PostgreSQL that differentiates itself from other databases. It is an object/relational database meaning that although it is mostly a relational database, it does offer functionality that is implemented in object databases like table inheritance. PostgreSQL isn’t as widely used as MySQL due to a lack of additional third-party tools. However, PostgreSQL is compatible with many programming languages and platforms making it much easier to migrate the database from one operating system to another or integrate it within a given tool.