Evaluating the Strengths and Limitations of Database Options

Evaluating the Strengths and Limitations of Database Operations

Databases are anything but “one size fits all.” From privacy and compliance to supporting specific data types, databases only get more advanced as an application scales Even if you aren’t a database administrator (DBA), you need to factor in database capabilities and limitations when adding new features or optimizing existing features to your applications.

Current Database Landscape

The process of scaling applications and improving services can introduce new data types The need for more flexible data types has launched many new databases in the market in the past decade. As a result, database skills have become increasingly more valued by developers.

Most Wanted Database Skills Among Developers Worldwide as of 2021

According to the survey, just under 18% of respondents identified PostgreSQL as one of their most-wanted database skills. MongoDB ranked almost the same with software developers stating they are not developing with it, but want to. Source: Statista

Databases can be split into two types: relational and non-relational

• Relational Databases (also referred to as SQL Databases) Data is organized in tables, where the columns of a table correspond to the data’s attributes, including the type of an attribute For example, an employee table for an HR software suite could contain a column that stores an integer value representing an employee’s salary, and another column could store a text value representing their name The rows of the table represent instances of the data; for example, each row in an employee table would correspond to a different employee. Information between multiple tables is linked via keys. Relational databases use the Structured Query Language (SQL)
• Non-Relational Databases (also referred to as NoSQL (“Not Only SQL”) Databases) Non-relational databases have more flexible query methods. These query methods vary significantly by DBMS type. There are five types of non-relational databases:
  • Columnar Data Stores: Data is organized in a similar row-and-column table structure like
    in a relational database. However, the data is stored by column, and data from specific columns can be fetched In comparison, data in a relational database is stored by row, which allows you to fetch specific rows from the table.
  • Key-Value Stores: Data is stored as a collection of key-value pairs, where data can be retrieved by specifying a key that is associated with the data
  • Document Stores: Data is stored in documents, like those on your computer’s filesystem.
  • Document Data Stores: A document store where the files’ type is JSON, XML, or some other data-encoding format. The structure of the encoded data is flexible and can allow for complicated queries
  • Graph Databases: Data is stored using a graph structure, where entities correspond to nodes in the graph and the edges between nodes represent relationships between entities. An example: of a graph structure would be the connections between users on a social network Graph databases offer efficient querying for highly-interconnected data.

What is ACID Compliance?

ACID compliance for databases is a set of principles that ensures data integrity while processing a transaction These principles ensure that the data will not end up in an inconsistent state or be altered as a result of being added to the database, even if the transaction fails ACID compliance is particularly important for applications that are essential to the financial industry, with millions of transactions processed every second.

ACID Compliance:

• Atomic: Transactions are made up of components, and all components in a transaction must succeed Otherwise, the transaction will fail and the database will remain unchanged A transaction must have defined success or fail actions, or unit of work.
• Consistent: Successful transactions follow the database’s pre-defined parameters and restrictions.
• Isolated: Concurrent transactions are isolated from one another The result of executing several concurrent transactions will be the same as if they were executed in a sequence
• Durable: Once a transaction is written to the database, it will persist, even in the event of a system failure for the server that the database is running on Out of the box, NoSQL databases are not ACID compliant, because they are designed to prioritize flexibility and scalability instead of pure consistency

What is the BASE Model?

With the increasing use of NoSQL databases, a different transaction model was created to better align with their functionality Sticking with the chemistry theme, we have the BASE Model

BASE Model:

• Basically Available: Data is replicated and dispersed across clusters so that failures of part of a cluster should still leave the data available in other locations.
• Soft State: Consistency is not immediately enforced across the database cluster after an item in the database is updated During this time, fetching an updated record from the database may result
  in different values for different read operations.
• Eventually Consistent: Data will eventually reach consistency as updated records are replicated across the nodes in the database cluster.

The clear difference between the ACID and BASE transaction models is seen in the practical use of SQL vs NoSQL databases. NoSQL databases adhering to the BASE model are built for the flexibility and scalability required by massive high-availability deployments SQL databases adhering to the ACID model value consistency and data integrity above all else.