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