Relational Database Management Systems
So you may ask yourself, what are RDBMS? As it’s name establishes, is a Relational Database that stores data in a row-based table structure which connects related data elements. They include functions that maintain the security, accuracy, integrity and consistency of data.
After 12 weeks of bootcamp, i only have experience with SQLite and PostgreSQL. Since i didn't get enough time to deeply explore other RDBMS, i'm going to take some time to talk briefly of other Relational Databases that weren't covered.
In this blog i will briefly go over three different RDBMS. SQLite, PostgreSQL and MySQL. These three are of the most popular relational database management systems, that interact with a database. They allow you to read, write, and perform other tasks, as well as controlled access to a database.
“MySQL is the world’s most popular open source database. With its proven performance, reliability and ease-of-use, MySQL has become the leading database choice for web-based applications, used by high profile web properties including Facebook, Twitter, YouTube, Yahoo! and many more.
MySQL is an open-sourced software backed by Oracle Corporation. It is a scalable, fast and reliable database management system which can run on any operating system or server. Unlike SQLite, it follows a client/server architecture, meaning there is an additional server to communicate between applications and the database. MySQL databases work well for dynamic websites or as an embedded database for software, hardware, and appliances. It was designed to be easy to use and fast, rather than SQL compliant and this causes some limitations. MySQL also performs well with read operations, but if you need heavy read-write operations you many want to use PostgreSQL instead. Despite this, MySQL is still the most popular RDBMS with companies like Shopify, Uber, and Amazon using it.
Security: Unlike SQLite, MySQL allow multi user management and allows you to specify users access. It comes with its own security script which grants you more control of access to your database and allows you to set password protection. Like SQLite and PostgreSQL, until every change operation of a transaction is successfully completed, that transaction isn’t cleared and the changes aren’t saved. This combined with the other layers of security MySQL provides, makes it a great choice for eCommerce businesses. This is because the money will not be deposited unless the entire process successfully completes. Upon failure all changes are reverted until the entire transaction is successful.
Easy to Use: Not only is it easy to install, but due to its popularity there is no shortage documentation and database administrators with experience using MySQL. There are also many third-party tools. And because MySQL started as an open-source software, there is a large community of developers to turn to for help.
“PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.”
— PostgreSQL Global Development Group
PostgreSQL has a strong reputation for its reliability, data integrity, extensive features. This gives developers the ability to better protect data and build secure environments and gives them the ability to manage data regardless of how large the database is. PostgreSQL allow you to define your own data types, build custom functions, and even write code in another programming language without reconfiguring the database. Being such a strong database system, companies like Apple, Instagram, Twitch, and Reddit all use PostgreSQL.
SQL Standard: PostgreSQL follows more closely to the SQL standards and the other two relational database management systems mentioned in this blog. In fact, according to the official site, it meets 170 of the 179 standards of SQL
Integration: Because PostgreSQL is compatible with many programming languages, it makes it easier to migrate your database to another OS or integrate it with different tools. This also allows defining our own customized functions.
Big Community: PostgreSQL is a mature database management system. Because of that, it has had years to grow its community. This community has been able to deliver strong documentation and other online resources. And that makes troubleshooting easy.
“ SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.”
Most database management systems implement a separate server to communicate with the database server, through an interprocess communication. This is known as a client/server architecture. SQLite is described as “serverless”, meaning it does not require an additional server. Unfortunately this feature prevents SQLite to support multi-user management, meaning you can’t grant and control users access to the database. Applications communicate directly with the SQLite database server disk file. It being self contained, requires little support from an operating system and makes it easy to embed in devices like phones or game consoles.
Lightweight and Portable: In fact, the -lite in SQLite refers to it’s lightweight structure. It takes up little space and is notably -lite in it setup, administration, and it’s required resources. This can mostly be attributed the SQLite not requiring installation of additional dependencies. An SQLite database is stored as one single disk file. This single file is also “cross-platform”. This means you can create a SQL database on one machine and it can be copied and used on a different machine, even if that machine has a different environment or architecture.
Speed and Efficiency: Read and write requests are really fast with SQLite. This is because it only loads the data it needs instead of reading the whole file and storing it. When editing, it only overwrites the parts of the file you made changes too.
User-friendly: SQLite doesn’t require configuration and doesn’t need to be stopped, started, or restarted because it doesn’t have a server process. This makes installing and integrating SQLite pain-free.
It's a matter of preference on which RDBMS to use. They are all very good tools.