Types of Databases

Databases come in many forms, each designed for specific use cases and data patterns. While this course focuses on relational databases and SQL, understanding the broader database landscape helps you make informed technology choices.

In this tutorial, you will learn:

• The main categories of databases
• Characteristics of relational databases
• What NoSQL databases are and when to use them
• How to choose the right database type
• Emerging database trends

Databases can be broadly categorized based on how they store and organize data.

Database Categories at a Glance

Relational databases are the most widely used database type, especially for business applications.

Core Characteristics

1. Tabular Structure

Data is organized into tables (relations) with:

• Rows representing individual records
• Columns representing attributes
• Fixed schema defining the structure

2. Relationships

Tables can be connected through keys:

• Primary Key: Unique identifier for each row
• Foreign Key: Reference to another table's primary key

3. SQL Query Language

Standardized language for:

• Querying data (SELECT)
• Modifying data (INSERT, UPDATE, DELETE)
• Defining structure (CREATE, ALTER)

4. ACID Transactions

Guaranteed transaction properties:

• Atomicity: All or nothing
• Consistency: Valid state to valid state
• Isolation: Concurrent transactions don't conflict
• Durability: Committed data persists

When to Use Relational Databases

✅ Excellent for:

• Structured data with clear relationships
• Financial transactions and banking
• E-commerce and inventory systems
• Enterprise resource planning (ERP)
• Customer relationship management (CRM)
• Reporting and analytics

⚠️ May not be ideal for:

• Highly unstructured data
• Massive horizontal scaling needs
• Rapidly changing schema requirements
• Real-time streaming data

NoSQL (often interpreted as "Not Only SQL") refers to databases that don't use the traditional relational model.

Why NoSQL Emerged

The rise of web applications brought new challenges:

• Massive scale: Billions of users and records
• Flexible data: User-generated content varies widely
• High availability: 24/7 global access requirements
• Rapid development: Schema changes frequently

Relational databases struggled with some of these requirements, leading to alternative approaches.

NoSQL Categories

1. Document Databases

Store data as flexible documents (usually JSON-like).

{
  "customer_id": "C001",
  "name": "Alice Johnson",
  "orders": [
    { "order_id": "O101", "total": 150.00 },
    { "order_id": "O102", "total": 89.99 }
  ],
  "preferences": {
    "newsletter": true,
    "theme": "dark"
  }
}

Characteristics:

• No fixed schema (each document can differ)
• Nested data is natural
• Good for content management, catalogs

Examples: MongoDB, CouchDB, Amazon DocumentDB

2. Key-Value Databases

Simplest model: store values by unique keys.

Key: user:1001:session
Value: {"token": "abc123", "expires": "2024-12-31"}

Key: cart:5555
Value: [{"product": "laptop", "qty": 1}]

Characteristics:

• Extremely fast reads and writes
• No query language (just get/set by key)
• Perfect for caching and sessions

Examples: Redis, Amazon DynamoDB, Memcached

3. Column-Family Databases

Store data by columns rather than rows.

Row Key: customer_1001
  Column Family: profile
    name: "Alice"
    email: "alice@mail.com"
  Column Family: orders
    order_001: {total: 150}
    order_002: {total: 89}

Characteristics:

• Optimized for reading columns across many rows
• Great for analytics and aggregations
• Designed for massive scale

Examples: Apache Cassandra, HBase, ScyllaDB

4. Graph Databases

Store entities (nodes) and relationships (edges).

(Alice)-[:FRIENDS_WITH]->(Bob)
(Alice)-[:PURCHASED]->(Laptop)
(Bob)-[:REVIEWED]->(Laptop)

Characteristics:

• Relationships are first-class citizens
• Efficient for traversing connections
• Natural for networks and hierarchies

Examples: Neo4j, Amazon Neptune, JanusGraph

Understanding when to use each type helps you make better architectural decisions.

Feature Comparison

When to Choose SQL (Relational)

When to Choose NoSQL

The Truth: Many Systems Use Both

Modern applications often combine database types:

┌─────────────────────────────────────────────────────────┐
│                     Application                         │
├─────────────┬─────────────┬─────────────┬──────────────┤
│  PostgreSQL │   Redis     │  MongoDB    │ Elasticsearch│
│  (Orders,   │  (Session   │  (Product   │ (Search      │
│   Users)    │   Cache)    │   Catalog)  │  Index)      │
└─────────────┴─────────────┴─────────────┴──────────────┘

Beyond the main categories, specialized databases address specific use cases.

Time Series Databases

Optimized for data with timestamps.

Use Cases:

• IoT sensor data
• Application monitoring
• Financial tick data
• Server metrics

Examples: InfluxDB, TimescaleDB, Prometheus

Search Engines (as Databases)

Optimized for full-text search and filtering.

Capabilities:

• Fast text search across documents
• Faceted search and filtering
• Relevance scoring
• Near real-time indexing

Examples: Elasticsearch, Apache Solr, Meilisearch

In-Memory Databases

Store data primarily in RAM for speed.

Characteristics:

• Microsecond response times
• Often used with persistence to disk
• Perfect for caching layers

Examples: Redis, Memcached, SAP HANA

Vector Databases

Store and search high-dimensional vectors (emerging category).

Use Cases:

• AI/ML embeddings
• Similarity search
• Recommendation systems
• Image and text search

Examples: Pinecone, Milvus, Weaviate

Databases can be deployed in different ways based on your needs.

On-Premises

You manage everything:

• Hardware and servers
• Database software installation
• Backups and maintenance
• Security and updates

Pros: Full control, data stays local
Cons: High operational burden

Cloud-Managed (DBaaS)

Cloud provider manages infrastructure:

Pros: Less maintenance, easy scaling
Cons: Less control, potential lock-in

Serverless Databases

No capacity planning needed:

• Auto-scale based on usage
• Pay per query/operation
• Zero administration

Examples:

• Amazon Aurora Serverless
• Azure Cosmos DB Serverless
• PlanetScale (MySQL)
• Supabase (PostgreSQL)

Embedded Databases

Database runs within your application:

• No separate server process
• Perfect for mobile and desktop apps
• Single-user or limited concurrency

Examples: SQLite, LevelDB, RocksDB

Here's a decision framework for selecting a database type.

Key Questions to Ask

1. What is your data structure?

2. What are your scale requirements?

3. What consistency do you need?

4. What queries will you run?

Default Recommendation

When in doubt, start with PostgreSQL.

Why?

• Handles structured and JSON data
• Excellent performance at most scales
• Rich feature set
• Strong community and tooling
• Can always migrate later if needed

Let's explore our relational database to understand why it's well-suited for e-commerce data.

You now have a comprehensive understanding of the database landscape!

Key Takeaways

✅ Relational databases store data in tables with relationships, using SQL for queries

✅ NoSQL databases offer flexible schemas and horizontal scaling for specific use cases

✅ Document databases (MongoDB) are great for flexible, nested data

✅ Key-Value databases (Redis) excel at simple, fast lookups

✅ Graph databases (Neo4j) handle connected data efficiently

✅ Most applications use multiple database types together

✅ When in doubt, start with PostgreSQL - it handles most use cases well

Database Selection Quick Guide

Next Up

Continue to SQL Commands to learn about the different categories of SQL statements and what each one does!