It's Quite sensible when it comes to selecting BEST architecture for drilling through data models.....and playing around hierarchical & sCRaMblEd data!!

For most of the decades, businesses relied on relational database management systems (RDBMSs)—that used SQL and it continues to dominate. As of 2020, 60.5% of databases were SQL-based relational database management systems.

Still, each year, NoSQL-based non-relational database management systems are becoming more popular—particularly because data scientists want to expose their machine learning business analytics tools to more unstructured data. Let's look at how these database styles differ.

Relational Database Management Systems (SQL-Based)

Relational database management systems (RDBMSs) use SQL, a database management language that offers a highly organized and structured approach to information management. Similar to the way a phone book has different categories of information (name, number, address, etc.) for each line of data, relational databases apply strict, categorical parameters that allow database users to easily organize, access, and maintain information within those parameters.

The primary reasons why SQL-based RDBMSs continue to dominate are

• they are highly stable and reliable;
• they adhere to a standard that integrates seamlessly with popular software stacks like LAMP;
• we've been using them for more than 40 years.

RDBMS advantages

ACID compliance: If a database system is "ACID compliant," it satisfies a set of priorities that measure the atomicity, consistency, isolation, and durability of database systems. The more ACID-compliant a database is, the more it serves to guarantee the validity of database transactions, reduce anomalies, safeguard data integrity, and create stable database systems. Generally, SQL-based RDBMSs achieve a high level of ACID compliance, but NoSQL databases give up this distinction to gain speed and flexibility when dealing with unstructured data.

Ideal for consistent data systems: With a SQL-based RDBMS, your information will remain in the structure you originally create. If you don't need a dynamic information system for massive amounts of data—and you're not dealing with numerous data types—an RDBMS offers great speed and stability.

Better support options: Because RDBMS databases have been around for over 40 years, it's easier to get support, add-on products, and integrate data from other systems.

RDBMS disadvantages

Scalability challenges and difficulties with sharding: RDBMSs have a more difficult time scaling up in response to massive growth compared to NoSQL databases. These databases also present challenges when it comes to sharding. Sharding is the process of dividing a large database into smaller parts for easier management. If you're dealing with a conservative database that you don't expect to change a lot in the years ahead, the sharding and scaling challenges related to RDBMS solutions may never apply to you. On the other hand, if you plan to scale up and grow in the years ahead, a non-relational database system (NoSQL-based) could be a better match for your needs.

Less efficient with NoSQL formats: Most RDBMSs are now compatible with NoSQL data formats, but they don't work with them as efficiently as non-relational databases.

Non-relational DBMS advantages

Excellent for handling "big data" analytics: The main reason why NoSQL databases are becoming more popular is that they remove the bottleneck of needing to categorize and apply strict structures to massive amounts of information. NoSQL databases like HBase, Cassandra, and CouchDB support the speed and efficiency of server operations while offering the capacity to work with large amounts of data.

No limits on types of data you can store: NoSQL databases give you unlimited freedom to store diverse types of data in the same place. This offers the flexibility to add new and different types of data to your database at any time.

Easier to scale: NoSQL databases are easier to scale. They're designed to be fragmented across multiple data centers without much difficulty.

No data preparation required: When there isn't time to design a complex model, and you need to get a database running fast, non-relational databases save a lot of time.

Non-relational DBMS disadvantages

• More difficult to find support: Because the NoSQL community doesn't have 40 years of history and development behind it, it could be more difficult to find experienced users when you need to troubleshoot.

• Lack of tools: Since the system is relatively new compared to SQL-based RDBMS solutions, there aren't as many tools to assist with performance testing and analysis.

• Compatibility and standardization challenges: Newer NoSQL database systems also lack the high degree of compatibility and standardization offered by SQL-based alternatives. You may find that the data in your non-relational database management system doesn't readily integrate with other products and services.

Relational vs. Document DB

As you probably figured out by now, there is no right answer, no ‘One DB to rule them all’. The most common DBs for ‘regular’ use are Relational and Document DBs so we’ll compare them.

Relational — advantages

• It has a simple structure that matches most kinds of data you normally have in a program.
• It uses SQL, which is commonly used and inherently supports JOIN operations.
• Allows fast data updating. All the DB is saved on one machine, and relations between records are used as pointers, this means you can update a record once and all its related records will update immediately.
• Relational DB also supports atomic transactions - What are atomic transactions: let’s say I want to transfer X dollars from Alice to Bob. I want to perform 3 actions: decrease Alice’s balance by X, increase Bob’s balance by X and document the transaction. I want to treat these actions as one atomic unit — all of the actions or none will occur.

Relational — disadvantages

Since each query is done on a table — the query execution time depends on the size of the table. This is a significant limitation that requires us to keep our tables relatively small and perform optimizations on our DB in order to scale.

In relational DBs scaling is done by adding more computing power to the machine that holds your DB, this method is called ‘Vertical Scaling’.

Why is it a disadvantage? since there is a limit for the computing power machines can provide and since adding resources to your machine can require some downtime. Relational does not support OOP based objects, even representing simple lists is very complicated.

Document DB — advantages

• It allows you to keep objects with different structures.
• You can represent almost all data structures including OOP based objects, lists, and dictionaries using good old JSON.
• Although NoSQL is un-schematized by nature, it often supports schema validation, meaning you can make a collection schematized, the schema won’t be as simple as a table, it will be a JSON schema with specific fields.
• Querying NoSQL is very fast, each record is independent and therefore the query time is independent of the DB’s size and supports parallelism.
• In NoSQL, scaling the DB is done by adding more machines and distributing your data between them, this method is called ‘Horizontal Scaling’. This allows us to automatically add resources to our DB when needed without causing any downtime.

Document DB — disadvantages

• Updating the data is a slow process in Document DB since the data can be divided between machines and can be duplicated.
• Atomic transactions are not inherently supported. you can add it yourself in code by using verification and revert mechanism, but since the records are divided between machines it cannot be an atomic process and race conditions can occur.

Oracle database — advantages

• The most advanced technology: Oracle is known for being on the leading edge of database technology. They have a long-standing reputation for bringing quality – along with the latest features and innovations – to their customer base.

• A wide range of solutions: Oracle offers a massive suite of tools and solutions that can address virtually any information challenges you encounter.

Oracle database — disadvantages

• An expensive solution: Oracle tends to be a high-cost solution that smaller, non-enterprise-level organizations might not be able to afford.
• System upgrades might be required: Your current system specifications might not be enough to implement Oracle. Many businesses have to upgrade their hardware before using Oracle solutions.

  Best use case for Oracle: If you’re a large organization that needs to manage a massive amount of data, Oracle could be the ideal choice.

MySQL — advantages

• It’s free: As an open-source RDBMS solution, MySQL is free to use in any way you want.
• Highly compatible with other systems: MySQL has a reputation for being compatible with many other database systems.

MySQL — disadvantages

• Missing features common to other RDBMSs: Because MySQL prioritizes speed and agility over features, you might find that it’s missing some of the standard features found in other solutions. For example, the ability to create incremental backups.
• Challenges getting quality support: The free version of MySQL does not come with - - on-demand support. However, MySQL does have an active volunteer community, useful forums, and a lot of documentation that you may find useful.

  Best use case for MySQL: MySQL is a particularly valuable RDBMS solution for businesses that need a solution with enterprise-level capabilities, but are operating under strict budget constraints. It is an extremely powerful and reliable modern RDBMS with a free tier.

MongoDB — advantages

• NoSQL support: This DBMS was specifically made to support JSON and NoSQL data.
• Highly flexible: Since MongoDB stores and manages any kind of information, developers face fewer restrictions when incorporating data into a MongoDB database.
• Great for applications including web-based apps: MongoDB has become a popular DBMS for web-based applications.

MongoDB — disadvantages

• No SQL querying: MongoDB will not accept SQL queries. You can use additional tools to translate your SQL queries to work with this database engine. However, the workaround can be inconvenient.
• Difficult to set up: MongoDB takes time and more experience to set up properly than other solutions.
• Lack of security: The native settings on MongoDB don't tend to be very secure.

  Best use case for MongoDB: If you're building an application on top of an operational database and you need a really fast response time, MongoDB could be the right choice for you. However, if you're building a data warehouse for analytics purposes, you might want to use a different platform.

Hope you find this post helpful!! Please feel free to leave your views/insights below in comments section. You can connect with me on LinkedIn