Guide To Choose A Database For Your Next Design

Why we need to choose a database?

A database is an organized collections of data that can be managed and accessed easily. Each provider has applied these modifications to data stores differently, depending on the type of database and how the database engine is configured. As a result, we are unable to store certain data structures in one type of database but in another.

As an illustration, a relational database cannot hold a graph data structure. These kinds of data structures are intended to be stored in different kinds of databases. Therefore, it is crucial to select the appropriate database for your needs at the design phase rather than selecting the incorrect database type and changing it later on in the development process.

Types Of Databases

We can choose between traditional relational databases and non-relational databases. More than these two words you may have heard SQL and NO SQL databases. Non-relational databases are referred to as NO SQL and relational databases as SQL.

Let's talk about the various factors we took into account before deciding on a database for our designs.

Relational Databases

Relational databases have been around here for more than 40 years and it is the default choice of software professionals for structured data storages. You might have heard about ACID principles. In order to maintain consistency in databases, before and after the transactions, certain properties are followed. These are called ACID properties. One of the greatest powers of the relational database is its abstractions of ACID transactions and related programming semantics.

Some of the important features of relational database are

1. ACID compliance
   The general principle is if one change fails, the whole transaction will fail, and the database will remain in the state it was in before the transaction was attempted.

2. Reduced redundancy - Normalization
   The information related to a specific entity is stored in one table and relent data is stored in another table linked to that by a foreign key. As an example, product related information are stored in a one table and customer who brought the product stored in another table and linked to the production table by a foreign key. This process is called normalization and has the additional benefit of removing anomalies.

3. Concurrency
   Database concurrency is a unique characteristic enabling two or more users to retrieve information from the database at the same time without affecting data integrity. Common issues during concurrent database transactions include dirty reads and lost data updates (dirty reads - This issue arises when a particular transaction accesses a data object written or updated by another uncommitted transaction in the database.). Concurrency in a relational database is handled through transactional access to the data.
   If your database is running this transaction as one whole atomic unit, and the system fails due to a power outage, the transaction can be undone, reverting your database to its original state.

4. Back Up and Disaster Recovery
   Relational databases guarantee the state of data is consistent at any time. The export and import operations make backup and restoration easier. Most cloud-based relational databases perform continuous mirroring to avoid loss of data and make the restoration process easier and quicker.

So If our use case required above features then we should consider going with a relational database.

So are there any drawbacks of relational databases?
Yeah of course, let’s discuss some drawbacks of relational databases

1. Lack of scalability
   While using the relational database over multiple servers, its structure changes and becomes difficult to handle, especially when the quantity of the data is large. Due to this, the data is not scalable on different physical storage servers. Ultimately, its performance is affected.

2. Complexity in Structure
   Relational databases can only store data in tabular form which makes it difficult to represent complex relationships between objects. This is an issue because many applications require more than one table to store all the necessary data required by their application logic.

Non-Relational Databases

These databases are used in applications that require a large volume of semi-structured and unstructured data, low latency, and flexible data models. This can be achieved by relaxing some of the data consistency restrictions of other databases.
Following are some of the characteristics of the NOSQL database

1. Simple Design
   Unlike SQL databases, NoSQL databases are not complicated. They store data in an unstructured or a semi-structured form that requires no relational or tabular arrangement. For example storing all the customer related information in a one document instead of multiple tables which require join operations. So less code , debug and maintain.

2. Horizontal Scaling
   As stated above scaling is one of the drawbacks in the relational databases. But scaling is one of the prominent features in non-relational databases. Because we can represent all of the customer related information in a one document instead of multiple tables over several nodes like in relational databases, we scale NoSQL databases pretty easily.

3. Availability
   Most of the non-relational databases support replication to support availability.

4. Independent Of Schema
   Non-relational databases don’t need a schema at the time of database configuration or data writes. As an example, mongo db which store the data in document (JSON, XML) allow different fields in different documents. So Number of fields in different documents can be different

Following are some of drawback of Non-relational databases.

1. No Support for ACID implicitly
   They don’t support ACID (atomicity, consistency, isolation, durability) transactions across multiple documents. But currently there are some newer versions of No-SQL databases which supports ACID properties to some extent. (MongoDB added support for multi-document ACID transactions in the 4.0 release, and extended them in 4.2 to span sharded clusters.)

2. Relatively Large Storage
   In terms of storage utilization, No SQL databases needs more storage because they focus more on query optimization than considering the storage. So there are lot of duplicates data in No-SQL databases
   NoSQL databases are divided into various categories based on the nature of the operations and features, including document store, columnar database, key-value store, and graph database.
   So we discussed about different pros and cons of relational and non-relational databases. So how do we choose the right database for our requirement.

Choose the right database

Various factors affect the choice of database to be used in an application. A comparison between the relational and non-relational databases is shown in the following table to help us choose:

By taking above table as a guidance we will go through some scenarios

Which database should we use when we have unstructured data and there’s a need for high performance?
So type of data is unstructured and needs high performance. So, it should be No SQL database. NoSQL database like MongoDB would be a perfect choice for this

Which database should we use when we have a financial application, like stock trading application, and we need our data to be consistent all times?
In a stock trading application data will be structured and we need high consistency too. So these are the features of relational database.

What kind of database should we use if we make a retail store application that requires storing the data in tabular format?
if we have tabular data that means we have structured data. Hence, a relational database is the right choice

Which kind of database should we use for making an application like Reddit/Facebook?
The data generated by the these social media applications are mostly unstructured which requires a non-relational databases.

For a web multiplayer game?
In this scenario we need to store data in the database as same as the object in the game so we can deserialize later. Hence, best choice is document oriented NoSQL database.