In the previous article, we have seen different databases such as relational, non-relational, hierarchical, etc. Few might doubt what this database is exactly, why we require it, how it is useful, and how we are utilizing it in our application like this. Most of your doubts regarding these databases are going to be resolved in this article. Also, I will provide a detailed explanation for each database. In which requirement do we have to use which database? Which database is most widely used in industries am going to all these prospects.

Databases In this article we are going to cover different types of databases, the use cases and examples, and the pros and cons of the same so let’s get started.

Think of rooms in a hospital – they look different from rooms in a hotel. In a movie theater, chairs are lined up in front of the screen for people to sit and watch a movie. In malls, there are large open spaces so that people can move around freely.

Similarly, different databases are designed based on the properties of data, the volume of data, and querying requirements. Each type provides unique features and ways to store data efficiently.

In this article, we are going to focus on relational and non-relational databases.

Some popular types of databases include:
1. Relational Databases
2 . Non-Relational Databases
2.1- Key-Value Stores
2.2- Column-Based Databases
2.3- Document-Based Databases
2.4- Search Databases

Relational Database(SQL)

So we will look into those examples as well relational databases are the most popular ones and here are the two factors that help you decide whether you have to select a relational database for your use case or not schema and asset properties let’s talk about schema.

Schema

Schema in relational databases refers to how your data is going to be structured, in relational databases, you have tables and rows that store the data so if your data can be represented in the form of tables and rows while satisfying the property of relational DB’s like if your data is complex and it could be represented using relational tables easily then you select a relational label.

Let’s discuss a classic example of employee data. You have an employee’s table, a department table, and an account table. The employee’s table is going to store the data of employees like name, age, phone number, EmpId, city, department ID, and account ID. Now, this ID refers to the primary key or the unique ID that identifies every employee.

Department ID and account ID are foreign keys that identify which department this employee belongs to and what is the account ID of this employee. These are called foreign keys. The department table will have details like the name of the department, when it was started, and other details. An account ID will have other details like balance and so on.

Now, how schema constraints come into the picture. Employee data will have a requirement that one employee has to belong to a department and that employee has to have an account as well. So, the department and account cannot be null. That is a schema constraint.

If your data can satisfy that and you know that this is going to be the structure of your data, we decide to select relational DBs.

Employees Table

Department Table

Accounts Table

Advantages of Relational Databases

what are the benefits of these relational DBs or schema you can represent complex data easily using relational tables. Second with the schema constraints you can ensure that some garbage data or null data doesn’t get into your database because the schema constraints like department ID cannot be null and account ID cannot be null will ensure that you don’t have inconsistent data or bad data in your database i.e Data Integrity.

Exceptions of Relational Databases

1. Schema Rigidity

• If your data has a fixed schema that won’t change much in the future, then that time relational databases work well.
• However, if your data schema is dynamic and you’re unsure about the future structure (e.g., adding new fields over time), relational databases can be difficult to manage. Then it becomes complex.
• Updating Schema (Adding Columns):
  • It is possible to alter tables and add columns using SQL commands.
  • However, when the table size is huge, these operations become slow and complex.

2. Performance Issues with Large Data Sets

• As data grows, relational databases can face performance challenges:
  • Joins across multiple tables to fetch data can become slow and expensive.
  • If queries require combining properties from different tables, performance may degrade as data volume increases.

3. Scaling Limitations

• Vertical Scaling:
  • Increasing the capacity of a single machine (e.g., increasing RAM, CPU, or storage).
  • Relational DBs support vertical scaling easily.
• Horizontal Scaling:
  • Splitting the data across multiple machines.
  • Relational databases struggle with horizontal scaling:
    • Dividing large tables across multiple machines is difficult.
    • It requires custom logic in the application code or sharding, which is complex to implement in relational databases.

Non-Relational Databases(No-SQL)

Now let’s talk about non-relational dbs sometimes known as nosql dbs.In such databases the schema is not fixed and different types of non-relational databases cater to different requirements.

1. Key-Value Stores

Let’s first talk about key-value stores have just like a hash map they will just have a key and a value so suppose you have requirements like you have a feature flag or you have a certain discount or promotion or you want to enable a certain feature in a certain city for your application so this kind of values could be stored in key-value stores there are multiple other cases for key-value stores like caching solutions are implemented using key-value stores. Some examples are Redis, DynamoDB, Memcache, etc.

Advantages of Key-Value

The benefit of key-value stores is they are quite fast and provide quick access because most of the data stores are in memory apart from uh such kinds of data like application-related data or configuration-related data you can also store request responses into key-value stores again key-value stores could be used in multiple caching solutions which we will discuss in detail in the caching Article.

2. Document-Based Databases

Let’s talk about document-based databases are usually used when you are not sure of the schema or how the data and the fields different fields of data are going to evolve in such cases document DBS is used so there is no fixed schema and one more important point for documentdbs are they can support heavy reads and writes.

So let’s see what a document-based DB looks like. Just like in relational databases, we have tables and rows, a document database has collections and documents. You can think of documents as rows and collections like tables.

The use cases, for example, storing product details for an e-commerce website. If you have to store product details for an item, you will have the item name, item ID, price, availability, tax, etc. Some details like that. You know that these details, although are known, they can change over time.

Also, when querying such kind of data, you would need all these properties at once in one query. You don’t want to have this different data in different tables and then make joins and fetch the data. Document DBs help you in decreasing that complexity, where you can just simply fetch documents from the database.

So when you have use cases like that, the schema is not fixed, you don’t know how it is going to evolve, and you want that flexibility of keeping dynamic data. Also, when you have use cases for heavy reads and writes, in such cases, document DBs are a choice.

Let’s take one more example to make it clear. Suppose you have a relational DB where you store user-related data. You would have a user table where you have the user ID, name, city, country, the company he works for, etc.

If you have to fetch all the user details, you would have to make a query to the user table, then the city table, country table, and company table to fetch the details related to the city, country, and company. Also, there is a requirement to save a large amount of user data, so this kind of case becomes complicated while using relational DBs.

On the other hand, if you put all this information in a document DB, where you have user, city, country, and ID—all these details which are fetched from different tables in the case of a relational DB—if these are stored in the document DB itself, it just has to fetch one document.

Example How Data is Stored in a Document-based database.

[
     {
          name: "ABC",
          age: "23",
          sex: "female
     },
    {
          name: "XYZ",
          age: "25",
          sex: "male
     },
     {........},
     {........}
]

Representation of the above tables taken in a Relational Database, in a document-based database.

[
  {
    id:"1",
    name:"jack",
    city:{
           id:"2",
           name:"LA",
           state:"California"
         },
   country:{
             id:"4",
             name:"United States of America",
             continent:"North America",
             code:"USA"
         },
  company:{
            id:"14",
            name:"Tesla",
            ceo:"Elon Mask",
            headquaters:"Palo Alto"
         }
   }
]

Downsides of a Document-Based DB:

• First, you don’t have a schema, so you might have null values or empty values in your DB.
• Second, these types of DBs do not provide ACID transactions, so sometimes the updates could become complex, and you cannot ensure if the transaction is completed or not completed.
• Although you can handle that using the application code, the DB itself does not provide that facility.

Summary of Benefits / Reasons to Choose Document Databases:

• They are highly scalable.
• They provide sharding capabilities.
• If you have dynamic data and you want that flexibility, you need a schema-less organization of your data.
• Such DBs provide special querying operations and aggregation queries that can help you fetch data as per the requirement.
• When you have all these factors, NoSQL DBs or document DBs are one of the choices.

Note: If you don’t understand sharding or horizontal or vertical scaling, as I said, there are dedicated articles on this topic, and we’ll cover more about DB sharding in those articles.

3. Column-Based Databases

• Column databases are sort of a midway between relational DBs and document DBs in a way that there is sort of a fixed schema with tables and columns, but these DBs do not support ACID transactions.
• Such databases are used when you require heavy reads.
• Some examples are event data or streaming data.

Use Case Examples:

• If you use a music app, you are continuously either liking the song, skipping over the song, or favoriting a song. All those interactions that you are doing have to be written and stored in DBs as event data so that analytics can be run over them. Such kind of data is stored in column DBs.
• Some other examples could be storing health tracking data or storing data for IoT devices where different sensors are deployed, and sensors are sending data continuously every 10 seconds or 30 seconds.
• In all such use cases, column DBs are used because they support a large number of heavy writes.

Reads in Column DBs:

• Coming to the reads, such DBs do not support a huge number of reads, but they do support special kinds of reads.
• The table structure is defined by the kind of queries you have to make.
• For example, in the music app, the queries will be:
  • You have to fetch user details and song details.
  • You have to fetch users who have liked a particular song.
  • You have to fetch the songs that a particular user likes.

Table Design in Column DBs:

• Some tables in column DBs could be:
  • Users
  • Songs
  • Users by liked songs
  • Songs by users
• This design in column DBs is done concerning what kind of reads are required.

Distributed Database Support:

• Column DBs are a good supporter of distributed databases.
• If you don’t know what distributed databases are, we will have a dedicated video on distributed DBs where we will dig into more of this in detail.

Popular Examples of Column DBs:

• Cassandra
• HBase
• Scylla

4. Search Databases

Whenever you interact with any application where you search for something like booking a flight, booking a movie, or purchasing an item on Amazon, all those full-text search queries are supported by data stored in search databases.

For example, if you’re reading a book, you usually have an index at the start of the book. You can find out where every chapter can be accessed. Suppose you want to go to chapter 5; it will say, “Go to page 237,” so that’s how you can easily access this page.

Similarly, the data against those queries is stored in advanced indexes inside search databases. So, when you search for a particular item, say “Post-it,” there will be data saved in these indexes to support those search queries.

Some examples of such databases are:

• Elastic
• Solr

The important thing to note here is that the data stored in search databases is not the primary data store.

For example, if you are working with an e-commerce application, the product catalog, all the products and items will be stored in a primary database (maybe a relational or a non-relational DB). The results of search queries, or the data on which frequent queries are executed, will be stored on a search DB and will be refreshed as per the frequency of the queries.

Other Use Cases for Data Storage:

• Images and videos: Such kinds of data are usually stored on cloud platforms like Amazon S3 or buckets in GCP.
• Large datasets or time series data: When a lot of data has to be stored and analytics is run over that.

When you are trying to build a large-scale system, however, these cannot be used as strict rules.

• In some cases, it will be very easy for you to identify that a certain requirement could be fulfilled with a key-value data store.
• In other cases, when requirements are fuzzy and when you don’t know how the data is going to evolve, it might be a difficult decision to choose between a relational or a non-relational DB, a document, or a column DB.

In such cases, usually, people sit with the team, weigh all the pros and cons, and then decide what is the choice that they have to go ahead with.

It is also possible that you might have chosen a relational DB at a certain point in your product life cycle. But five years down the line, or ten years down the line, when the scale is huge and the data is growing so fast, you might have to migrate to another kind of database.

Big companies, in some cases, have to develop their in-house database solutions to fulfill their requirements. So, there is no right or wrong answer here.

These were just some rules that you could use to select databases.

That was a short introduction to different types of databases and their use cases.

FAQ’s

1. Before Relational Databases did we have any other DataBases?

2. What are ACID Properties utilized by Databases?

3. Do we have only SQL and No-SQL Databases?

4. How large data Recovery will be Provided?

5. What are the SQL Languagues?

Conclusion

Different kinds of databases cater to such needs. All the details are included in the description, so the types of databases and different use cases that we have just discussed are the most popular ones. There are many more types of databases, and there are many more use cases.

Selecting the appropriate database depends on several factors:

– Structured Data & Fixed Schema → Use Relational Databases.
– Scalability & Flexibility → Use NoSQL Databases.
– High-Speed Key-Based Access → Use Key-Value Stores.
– Heavy Reads/Writes with Evolving Schema → Use Document Databases.
– Analytical & Streaming Data → Use Column Databases.
– Fast Search & Query Optimization → Use Search Databases.