Types of Databases

Databases are commonly grouped into two categories: relational and NoSQL (non-relational).

Relational databases

Relational databases enforce a predefined schema before storing data. Data is organized into relations (tables) composed of tuples (rows) and attributes (columns). Each tuple has a unique key. Because data is structured, a tuple in one table can link to a tuple in another using a foreign key.

Structured Query Language (SQL) is used to manage the database, handling data insertion, deletion, and retrieval.

Relational databases are dominant due to their simplicity, robustness, flexibility, and scalabilityTraditional databases are vertically scalable. Vertical scaling has limits. One might reach a point when more compute, memory, storage, or networking capability could not be added to a single node.. To maintain data integrity, relational databases provide (ACID) properties. ACID is a powerful abstraction that automatically handles complex anomalies (such as dirty reads, lost updates, and phantom reads), simplifying application logic.

However, ACID guarantees can be overkill for some use cases. If an application can tolerate specific anomalies, a custom solution might offer higher performance, though it adds implementation complexity.

The ACID offers the following properties:

• Atomicity: Transactions are atomic units. Either all statements in a transaction execute successfully, or none do. Failed transactions are aborted and rolled back.

• Consistency: The database remains in a consistent state before and after every transaction. It enforces rules to ensure data validity.

• Isolation: Concurrent transactions do not affect each other. The final state of the database is the same as if the transactions were executed sequentially.

• Durability: Once a transaction is committed, it survives permanently, even in the event of a system failure.

Popular database management systems (DBMS) for relational schemas include:

• MySQL

• Oracle Database

• Microsoft SQL Server

• IBM DB2

• Postgres

• SQLite

Why relational databases?

Relational databases are the default choice for structured data storage. Their primary strengths are the abstraction of ACID transactions and the standardization of programming semantics.

• Flexibility: SQL’s data definition language (DDL)DDL is a computer language used to create and modify the structure of database objects in a database. allows schema modifications, such as adding tables, renaming columns, or altering structures, even while the database server is running and processing queries.

• Reduced redundancy: Normalization eliminates data redundancy. Information about a specific entity is stored in one table and linked to other tables via foreign keys. This prevents inconsistent dependencyInconsistent dependencies arise when a user searches for some data in the irrelevant table or the data is missing in the relevant table. For example, to find the customer’s address it should be searched in the customer table instead of the product table. and ensures data is updated in only one place.

• Concurrency: Enterprise applications often have many users reading and writing data simultaneously. Relational databases manage concurrency through transactions, preventing inconsistencies such as double-booking a hotel room.

• Integration: A shared relational database allows multiple applications to access and aggregate data from a single source. The database handles concurrency control, enabling seamless integration between different systems.

• Backup and disaster recovery: Relational databases guarantee consistent data states, simplifying export, import, and backup operations. Most cloud-based options offer continuous mirroring for quick restoration.

Drawback

The relational databases have the following drawback:

Impedance mismatch

Impedance mismatch occurs when the relational model (tables) and in-memory data structures (objects) are not aligned. Application code often uses complex, nested structures, while databases use flat tables. This requires a translation layer to map objects to rows, adding complexity and overhead.