Types of Databases

Not all databases are built the same. The type of database we pick depends entirely on the shape of our data and the access patterns we expect. Picking the wrong type is like using a screwdriver to hammer a nail — it technically works, but it’s painful.

Let’s walk through every major type.

The Seven Main Types

1. Relational Databases (RDBMS)

The most widely used type. Data is organized into tables (relations) with a fixed schema. Every row follows the same structure. We query using SQL.

The big strength? Relationships. We can link tables together with foreign keys and use JOINs to pull related data in a single query.

-- Classic relational structure
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),  -- foreign key relationship
    total DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW()
);

Best for: Banking, e-commerce, any app with structured data and complex relationships.

2. Document Databases

Instead of tables, data lives as JSON-like documents. Each document can have a completely different structure — no rigid schema required.

Think of it like a filing cabinet. Each folder (document) can contain whatever we want. One folder might have 5 pages, another might have 20 with different fields.

-- MongoDB-style document (not SQL, but shown for clarity)
-- A user document might look like:
-- {
--   "_id": "abc123",
--   "name": "Manish",
--   "email": "manish@example.com",
--   "addresses": [
--     { "city": "Mumbai", "zip": "400001" },
--     { "city": "Delhi", "zip": "110001" }
--   ]
-- }
-- No need to create a separate "addresses" table!

Best for: Content management, user profiles, catalogs — anything with semi-structured or frequently changing schemas.

3. Key-Value Stores

The simplest model. Every piece of data is stored as a key-value pair. Give it a key, get back a value. That’s it. No complex queries, no joins, no relationships.

Because it’s so simple, it’s incredibly fast. Most key-value stores live entirely in memory.

Best for: Caching, session storage, feature flags, rate limiting, leaderboards.

4. Wide-Column Stores

Think of it like a relational table, but each row can have different columns. Data is grouped into column families, and we query by row key.

These are designed for massive scale — billions of rows, thousands of columns, distributed across many nodes.

Best for: IoT data, event logging, recommendation engines, anything with huge write throughput.

5. Graph Databases

Data is stored as nodes (entities) and edges (relationships between them). Each node and edge can have properties.

When our queries are all about “how is X connected to Y?”, graph databases destroy relational databases in performance. A query that needs 10 JOINs in SQL might be a simple traversal in a graph.

Best for: Social networks, fraud detection, recommendation engines, knowledge graphs.

6. Columnar Databases

In a normal row-based database, data for a single row is stored together. In a columnar database, data for a single column is stored together.

Why does this matter? When we run analytics queries like “what’s the average order total?”, we only need the total column. A columnar store reads just that column — not every field of every row.

Best for: Analytics, data warehousing, business intelligence, reporting.

7. Time-Series Databases

Optimized for data that comes in as timestamped data points — metrics, sensor readings, stock prices. They handle massive write throughput and have built-in functions for time-based aggregation (averages per hour, rolling windows, etc.).

Best for: Monitoring/observability, IoT sensor data, financial data, log analytics.

How to Pick the Right Type

There’s no “best” database. It depends on what we’re building:

The Real World: Polyglot Persistence

Most production systems don’t use just one database. They use multiple types together:

• PostgreSQL as the primary data store
• Redis for caching and sessions
• Elasticsearch for full-text search
• ClickHouse for analytics

This is called polyglot persistence — picking the right tool for each job instead of forcing everything into one database.

Interview Tip

If an interviewer asks “which database would you pick?”, never answer with just a name. Always explain the why — what access patterns, what scale, what consistency requirements. That’s what separates a good answer from a great one.