Anurag Vishwakarma

Posted on Aug 4, 2023 • Originally published at firstfinger.in on Aug 4, 2023

What Are the Different Types of Databases?

#database #datastructure #cloud #api

A database is a collection of data that is organized so that it can be easily accessed, managed, and updated. Databases are essential for storing and retrieving information in various applications, such as websites, mobile apps, online services, and business systems.

There are different types of databases, each with its own advantages and disadvantages. In this blog post, we will explore some of the most common types of databases and how they work. We will focus on two main categories: relational/SQL databases and NoSQL databases.

What is a Database?

A database refers to a well-organized collection of structured information or data that is usually stored electronically in a computer system. The database is typically managed by a database management system (DBMS). The combination of data, DBMS and the applications associated with them is called a database system.

What is a Database? | Image Source: Bytebase

To make processing and data querying efficient, data within the most common types of databases in use today is usually modelled in rows and columns in a series of tables. This allows for easy access, management, modification, updating, control, and organization of the data. Structured query language (SQL) is commonly used for writing and querying data in most databases.

🧩

In a simple way: A database is a place where you can store and organize a lot of information. For example, if you have a collection of books, you can use a database to keep track of the titles, authors, genres, and ratings of your books. A database can help you find the information you need quickly and easily. You can also add, update, or delete information in a database as your collection changes.

There are two types of databases that are most commonly used:

Relational/SQL database: Stores data in tables, which are made up of rows and columns.
NoSQL database: This does not use the traditional table structure of relational databases.

Relational/SQL Databases

A relational database is a type of database that stores data in tables, which consist of rows and columns. Each row represents a record, and each column represents a field or attribute. For example, a table of customers might have columns for name, email, phone number, and address.

Relational databases use a structured query language (SQL) to manipulate and query data. SQL is a standard language that allows users to create, read, update, and delete data in a relational database. For example, the following SQL statement selects all the records from the customer's table where the name is John:

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  FullName VARCHAR(100),
  Position VARCHAR(50),
  Department VARCHAR(50),
  Salary DECIMAL(10, 2)
);

INSERT INTO Employees (EmployeeID, FullName, Position, Department, Salary)
VALUES
  (101, 'John Doe', 'Software Engineer', 'Engineering', 75000.00),
  (102, 'Jane Smith', 'Marketing Manager', 'Marketing', 90000.00),
  (103, 'Michael Johnson', 'Sales Representative', 'Sales', 60000.00),
  (104, 'Emily Davis', 'HR Coordinator', 'Human Resources', 55000.00);

A real-world example of a simple database for a company's employee records.

Relational databases are widely used because they are reliable, scalable, and easy to use. They can handle complex queries and transactions that involve multiple tables and operations. Some examples of relational database management systems (RDBMS) are MySQL, PostgreSQL, and Microsoft SQL Server.

Features: ACID properties, predefined schema, relational data model.
Pros: Strong consistency, mature with extensive community support, powerful query language.
Cons: Limited scalability, rigid schema.
Use-case: Structured data with complex relationships.
Query format: SQL (Structured Query Language).

NoSQL Database

There are different types of NoSQL databases.

A NoSQL database is a type of database that does not store data in tables, but rather in other formats, such as documents, key-value pairs, graphs, or columns. NoSQL databases are designed to handle large volumes of unstructured or semi-structured data that do not fit well in the rigid schema of relational databases. They also offer more flexibility and scalability than relational databases. NoSQL databases are also known as Not only SQL databases.

What Sets NoSQL Databases Apart?

NoSQL databases do not rely on a fixed schema, allowing for the storage of unstructured or semi-structured data. They can easily accommodate data that may evolve over time, making them ideal for handling dynamic applications.

There are different types of NoSQL databases, each with its own advantages and disadvantages:

Image Credit: Alex Xu

1] Time-Series Databases

A time-series database is a type of database that stores data as a series of values that are associated with timestamps. A time series is a sequence of data points that are ordered by time and represent a measurement or an event over time. For example, a time series of temperature might look like this:

timestamp: 2021-01-01 12:00:00
value: 25
timestamp: 2021-01-01 13:00:00
value: 26
timestamp: 2021-01-01 14:00:00
value: 27

Time-series databases use a query language that is designed to perform operations on time-series data, such as aggregation, filtering, transformation, and analysis. For example, the following query calculates the average temperature for each hour in the temperature time series:

SELECT MEAN(value) FROM temperature GROUP BY time(1h);

Time-series databases are suitable for storing data that has a high degree of volume and velocity and requires real-time processing and analysis. They can also handle data that is irregular and has missing values.

💡

Some examples of time-series database management systems are InfluxDB, TimescaleDB, and Prometheus.

Features: Optimized for time-stamped data.
Pros: Efficient storage and querying of time-series data.
Cons: Not suitable for general-purpose data.
Use-case: IoT applications, monitoring systems.
Query format: SQL-like syntax (InfluxQL for InfluxDB).

2] Document Databases

A document database is a NoSQL database that stores data as documents. A document is a collection of key-value pairs that can contain various types of data, such as text, numbers, arrays, objects, or binary data. For example, a document of a customer might look like this:

 {
"name": "John",
"email": "john@example.com",
"phone": "+1-234-567-8901",
"address": {
"street": "123 Main Street",
"city": "New York",
"state": "NY",
"zip": "10001"
  }
}

High-Performance Document Database Architecture

Document databases use a query language that is specific to the document format. For example, MongoDB uses JSON (JavaScript Object Notation) as its document format and supports queries in BSON (Binary JSON). The following query selects all the documents from the customer's collection where the city is New York:

db.customers.find({address.city: 'New York'});

Document databases are suitable for storing data that has a high degree of variety and does not require a fixed schema. They can also handle nested and hierarchical data more efficiently than relational databases.

💡

Some examples of Document Database Management Systems are MongoDB, CouchDB, and DynamoDB.

Features: Stores data in document-like structures.
Pros: High flexibility, easy to scale.
Cons: Lack of standardization.
Use-case: Content management systems, catalogues.
Query format: JSON-like documents.

3] Key-Value Databases

A key-value database is a NoSQL database that stores data as pairs of keys and values. A key is a unique identifier that is used to retrieve the associated value. A value can be any type of data, such as a string, number, object, or binary data. For example, a key-value pair of a customer might look like this:

key: john@example.com
value: {
"name": "John",
"phone": "+1-234-567-8901"
}

Key-Value Database Architecture

Key-value databases use a simple query language that allows users to perform basic operations on the data, such as get, set, delete, and update. For example, the following command sets the value for the key john@example.com in the customer's collection:

SET customers john@example.com '{"name": "John", "phone": "+1-234-567-8901"}';

Key-value databases are suitable for storing data that has a high degree of simplicity and does not require complex queries or relationships. They can also handle high-performance and scalability requirements by distributing the data across multiple nodes.

💡

Some examples of key-value database management systems are Redis, Memcached, and Riak.

Features: Simple data model based on key-value pairs.
Pros: High performance, easy to scale.
Cons: Limited query capabilities.
Use-case: Caching, session management.
Query format: Simple commands to get/set data by key.

4] Graph Databases

A graph database is a NoSQL database that stores data as nodes and edges. A node is an entity that has properties and labels. An edge is a relationship that connects two nodes and has a direction and a type. For example, a graph of customers and products might look like this:

Customer(name: John) -[BOUGHT]-> Product(name: Laptop)
Customer(name: John) -[BOUGHT]-> Product(name: Phone)
Customer(name: Mary) -[BOUGHT]-> Product(name: Laptop)
Customer(name: Mary) -[LIKES]-> Customer(name: John)

Efficiently store and query complex relationships, Ideal for social networks.

Graph databases use a query language that is based on graph theory and allows users to traverse and analyze the data in terms of nodes, edges, and paths. For example, the following query finds all the customers who bought the same product as John:

MATCH (c:Customer)-[:BOUGHT]->(p:Product)<-[:BOUGHT]-(j:Customer {name: 'John'})
RETURN c.name, p.name;

Graph databases are suitable for storing data that has a high degree of complexity and requires rich relationships and queries. They can also handle data that is dynamic and evolving over time.

💡

Some examples of graph database management systems are Neo4j, OrientDB,

Features : Data stored as nodes and edges.
Pros : Excellent for data with complex relationships.
Cons: Not suitable for all data types.
Use-case: Social networks, recommendation engines.
Query format: Graph query languages (e.g., Cypher for Neo4j).

5] NewSQL Databases

NewSQL databases are a class of relational databases that aim to combine the benefits of traditional SQL databases with the scalability and performance of NoSQL databases. For example, A high-traffic e-commerce platform needs to handle concurrent transactions and large amounts of data.

SELECT * FROM users WHERE age > 30;
INSERT INTO orders (customer_id, product_id, quantity) VALUES (123, 456, 2);
UPDATE products SET price = 19.99 WHERE id = 789;
DELETE FROM customers WHERE last_purchase_date < '2022-01-01';

NewSQL combines the benefits of traditional SQL and NoSQL

NewSQL databases are designed to support the SQL querying language, which means they are compatible with existing applications. In addition, they have distributed architectures that enable them to scale horizontally.

💡

Some examples of column database management systems are CockroachDB, NuoDB, Google Cloud Spanner and TiDB.

Features: NewSQL databases offer horizontal scalability, allowing them to handle large amounts of data and high traffic loads.
Pros : NewSQL databases combine the flexibility of NoSQL databases with the reliability and consistency of traditional SQL databases.
Cons : NewSQL databases are relatively new compared to traditional SQL databases, which means they may have a smaller user base and fewer mature tools and libraries.
Use-case: A use-case for NewSQL databases is in applications that require high scalability, real-time data processing, and ACID compliance.
Query format : SELECT * FROM users WHERE age > 30;

6] Object-Oriented Databases

An object-oriented database is a type of database that stores data as objects, rather than tables or documents. An object is an entity that has attributes and methods. Attributes are properties that describe the object, such as name, colour, or size. Methods are functions that define the behaviour of the object, such as moving, speaking, or calculating.

Object-oriented databases use an object-oriented query language (OOQL) to manipulate and query data. OOQL is based on the principles of object-oriented programming (OOP), such as inheritance, polymorphism, and encapsulation. For example, the following OOQL statement selects all the objects from the customers class where the name is John:

SELECT * FROM customers WHERE name = 'John';

OODBs combine OOP and database management for flexible and scalable applications.

Object-oriented databases are suitable for storing data that has a high degree of complexity and requires rich functionality and abstraction. They can also handle data that is heterogeneous and has multiple types.

💡

Some examples of object-oriented database management systems (OODBMS) are ObjectDB, db4o.

Features: Data stored as objects.
Pros: Good for complex data, supports ACID properties.
Cons: Less common, smaller community.
Use-case: Complex data models, object-oriented programming.
Query format: Object Query Language (OQL).

7] Spatial Databases

A spatial database is a type of database that stores data as spatial features, such as points, lines, polygons, or surfaces. A spatial feature is an entity that has a location and a shape in a coordinate system. For example, a spatial feature of a city might look like this:

{
"name": "New York",
"type": "polygon",
"coordinates": [
[
[-74.0059731, 40.7143528],
[-74.0063933, 40.7152191],
[-74.0078135, 40.7152191],
[-74.0078135, 40.7143528],
[-74.0059731, 40.7143528]
]
]
}

Spatial databases use a spatial query language (SQL) to manipulate and query data. SQL is an extension of SQL that supports spatial data types and functions. For example, the following SQL statement selects all the records from the cities table where the area is greater than 1000 square kilometres:

SELECT * FROM cities WHERE ST_Area(geometry) > 1000;

Spatial databases for storing, managing, and processing spatial data, including geographic information like coordinates and shapes.

Spatial databases are suitable for storing data that has a high degree of location and geometry and requires spatial analysis and operations. They can also handle data that is multidimensional and has different levels of resolution.

💡

Some examples of spatial database management systems (SDBMS) are PostGIS, Oracle Spatial, and Esri ArcSDE.

Features: Stores geometric data types.
Pros: Supports spatial data and queries.
Cons: More complex, specialized use.
Use-case: Geographic Information Systems (GIS), location-based services.
Query format: SQL with spatial extensions.

8] Columnar Databases

A columnar database is a type of database that stores data in columns, rather than rows. A column is a collection of values that belong to the same attribute or field. For example, a table of customers might have columns for name, email, phone number, and address.

Columnar databases use a query language that is similar to SQL but with some differences in syntax and functionality. For example, the following query selects all the records from the customer's table where the name is John:

SELECT * FROM customers WHERE name = 'John';

The columnar database enables efficient data storage, fast complex queries, and valuable sales insights for e-commerce.

Columnar databases are suitable for storing data that has a high degree of scalability and performance and requires analytical queries and operations. They can also handle data that is sparse and has many null values.

💡

Some examples of Columnar Database Management Systems (CDBMS) are Cassandra, HBase, and Bigtable.

Features: Stores data by columns.
Pros: Fast query performance for analytical queries.
Cons: Not suitable for transactional data.
Use-case: Data warehousing, big data analytics.
Query format: SQL.

Summary

Databases are vital components of modern data-driven applications. The selection of the appropriate type of database depends on the specific requirements of the application. Relational databases ensure data integrity, while NoSQL databases offer flexibility and scalability.