Relational, Document, Key Value Databases: summary, use cases

There are a wide variety of databases. With cloud and hybrid architectures often see the following:

1. Relational: Structured tables with SQL relationships, data integrity.
2. Document: JSON-like documents, semi-structured data, scalability.
3. Key-Value: Simple key-value pairs for fast data retrieval.

Relational DB

A structured format with rows and columns, relying on SQL to manage data and enforce relationships between tables.

Popular: Oracle, MySQL, Microsoft SQL Server, PostgreSQL, Snowflake, IBM Db2, SQLite, Microsoft Access, Databricks, MariaDB, Microsoft Azure SQL Database, Apache Hive, Google BigQuery

Prioritize high data integrity.

• For distributed systems, Consistency and Partition tolerance (CP).
• For single node CA (Consistency + Availability).
• ACID principles are applied strictly in most, and not BASE. Data integrity is highest priority over availability.

SQL syntax is often used to analytics, this is an example for an ecommerce site:

SELECT 
    customer_id,
    DATE_TRUNC('month', order_date) AS month,
    SUM(total_amount) AS monthly_spending
FROM 
    orders
GROUP BY 
    customer_id, DATE_TRUNC('month', order_date)
ORDER BY 
    customer_id, month;

Primary use cases:

• Transactional systems needing ACID compliance and strong data consistency, such as in banking, finance and ecommerce analytics.
• Applications with complex querying and reporting requirements such as for audits.
• Systems requiring structured, normalized data across multiple tables
• Enterprise Resource Planning (ERP) systems for business process management.
• Software with well-defined schemas and relationships.
• Data warehousing for reporting and analytics across large datasets.
• Customer Relationship Management (CRM) systems.
• Supply Chain Management (SCM) and Inventory Systems.
• Human Resources Information Systems (HRIS)

Document

Designed for semi-structured data, these may store data as JSON or BSON documents, ideal for flexible schemas.

Popular: MongoDB, Databricks, Amazon DynamoDB, Microsoft Azure Cosmos DB, Couchbase, Firebase Realtime Database, CouchDB, Realm, Google Cloud Firestore

• Availability and Partition tolerance (AP) is prioritized.
• Partial ACID compliance, but can vary by DB.
• More BASE-oriented for scalability and availability with eventual consistency.

Primary use cases:

• Content management systems with semi-structured data storage.
• Apps needing hierarchical or nested data storage in JSON format.
• Real-time analytics for applications with varying data structures.
• Catalog systems storing diverse, evolving product or service details.
• E-commerce handling multiple item attributes and versions.
• Mobile apps with offline data sync and quick access requirements
• Gaming applications managing dynamic player profiles and inventory.
• Apps requiring quick iteration on data models without migrations.

Key Value

Known for simplicity, they store data as a collection of key-value pairs, useful for caching and session management.

Popular: Redis, Amazon DynamoDB, Microsoft Azure Cosmos DB, Memcached, etcd, Hazelcast, Aerospike, Google Cloud Bigtable, Apache Ignite, Amazon SimpleDB

• Availability and Partition tolerance (AP) for high speed scalability.
• Key-value stores are typically designed around BASE principles.

Primary use cases:

• High-speed caching for frequently accessed, simple data.
• User session storage with minimal data structure.
• Authentication tokens for managing secure user sessions
• Shopping cart or user preference storage for web applications.
• IoT applications needing fast, lightweight data lookups.
• Real-time bidding systems needing millisecond response times.
• Social media platforms caching user profiles for fast access.
• Apps where each record is accessed by a unique identifier.
• Real-time messaging applications