Database Types & Selection Guide

A comprehensive overview of different database types and when to use each one.

Relational Databases (RDBMS)

Characteristics

Structured data with predefined schema
ACID transactions (Atomicity, Consistency, Isolation, Durability)
SQL query language
Relationships via foreign keys
Normalization to reduce redundancy

Popular Options

Database	Use Case	Strengths
PostgreSQL	General purpose, complex queries	Advanced features, JSONB support
SQL Server	Enterprise, .NET stack	Integration with Microsoft ecosystem
MySQL	Web applications	Fast reads, wide adoption
Oracle	Enterprise, high availability	Mature, feature-rich

When to Use

Complex queries with JOINs
Transactions requiring ACID compliance
Well-defined, structured data models
Financial/banking applications
Inventory management

NoSQL Databases

Document Stores

Examples: MongoDB, Couchbase, Amazon DocumentDB

{
  "_id": "user123",
  "name": "John Doe",
  "orders": [
    { "id": 1, "total": 99.99 },
    { "id": 2, "total": 149.99 }
  ]
}

Use Cases:

Flexible schemas
Content management
User profiles
Catalog data

Key-Value Stores

Examples: Redis, DynamoDB, etcd

user:123 -> {"name": "John", "email": "john@example.com"}
session:abc -> {"userId": 123, "expires": "2024-01-01"}

Use Cases:

Caching
Session management
Real-time leaderboards
Shopping carts

Column-Family Stores

Examples: Cassandra, HBase, ScyllaDB

Use Cases:

Time-series data
IoT sensor data
High write throughput
Geographic distribution

Graph Databases

Examples: Neo4j, Amazon Neptune, ArangoDB

(User)-[:FOLLOWS]->(User)
(User)-[:PURCHASED]->(Product)
(Product)-[:CATEGORY]->(Category)

Use Cases:

Social networks
Recommendation engines
Fraud detection
Knowledge graphs

Specialized Databases

Time-Series Databases

Examples: InfluxDB, TimescaleDB, Prometheus

Use Cases: Metrics, monitoring, IoT data

Search Engines

Examples: Elasticsearch, Solr, Meilisearch

Use Cases: Full-text search, log analysis, e-commerce search

Vector Databases

Examples: Pinecone, Milvus, Weaviate

Use Cases: AI/ML embeddings, semantic search, recommendation systems

Decision Matrix

Requirement	Best Database Type
Complex transactions	RDBMS (PostgreSQL, SQL Server)
Flexible schema	Document (MongoDB)
High-speed caching	Key-Value (Redis)
Social relationships	Graph (Neo4j)
Time-series metrics	Time-Series (InfluxDB)
Full-text search	Search (Elasticsearch)
ML embeddings	Vector (Pinecone)
High write volume	Column-Family (Cassandra)

Database Selection Criteria

1. Data Model

Structured → RDBMS
Semi-structured → Document DB
Highly connected → Graph DB

2. Consistency Requirements

Strong consistency → RDBMS
Eventual consistency acceptable → NoSQL

3. Scale Requirements

Vertical scaling → RDBMS
Horizontal scaling → NoSQL

4. Query Patterns

Complex JOINs → RDBMS
Simple key lookups → Key-Value
Pattern matching → Graph DB

5. Team Expertise

Consider what your team knows well

Multi-Model Approach

Modern applications often use multiple databases:

┌─────────────────────────────────────────────────┐
│                  Application                     │
├──────────┬──────────┬──────────┬───────────────┤
│ PostgreSQL│  Redis   │  Elastic │   MongoDB     │
│ (Primary) │ (Cache)  │ (Search) │ (Documents)   │
└──────────┴──────────┴──────────┴───────────────┘

.NET Integration Examples

Entity Framework Core (SQL Server)

services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString));

Redis with StackExchange

var redis = ConnectionMultiplexer.Connect("localhost");
var db = redis.GetDatabase();
await db.StringSetAsync("key", "value");

MongoDB Driver

var client = new MongoClient("mongodb://localhost:27017");
var database = client.GetDatabase("myapp");
var collection = database.GetCollection<User>("users");

Sources

Arhitectura/DB.gif