Multi-Tenant Database

📌 Goal:
Host multiple tenants (clients/organizations) efficiently while balancing isolation, scalability, and cost.

---

1. 🔗 Shared Table (Single Schema)

How it works:
One database, one schema. All tenants share the same tables. Each row is tagged with tenant_id.

erDiagram
    USERS {
        string tenant_id
        string user_id
        string name
    }
    ORDERS {
        string tenant_id
        string order_id
        string user_id
    }
    USERS ||--o{ ORDERS : has

• ✅ Very scalable, cheapest to run
• ⚠️ Risk of data leakage, strict filtering logic needed
• 🎯 Use when: many small tenants, typical SaaS app

---

2. 🧩 Schema per Tenant

How it works:
One database, but each tenant has its own set of tables (schema).

graph TB
    subgraph TenantA_Schema
        A1[Users] --> A2[Orders]
    end
    subgraph TenantB_Schema
        B1[Users] --> B2[Orders]
    end
    subgraph TenantC_Schema
        C1[Users] --> C2[Orders]
    end

• ✅ Better isolation than shared table
• ⚠️ Harder to manage as tenants grow
• 🎯 Use when: moderate customization needed, mid-size clients

---

3. 🏢 Database per Tenant

How it works:
Each tenant gets their own full database.

graph LR
    subgraph TenantA_DB
        A1[Users] --> A2[Orders]
    end
    subgraph TenantB_DB
        B1[Users] --> B2[Orders]
    end
    subgraph TenantC_DB
        C1[Users] --> C2[Orders]
    end

• ✅ Full data and resource isolation
• ⚠️ Expensive, complex automation required
• 🎯 Use when: enterprise clients, high compliance/security needs

---

🔄 Quick Comparison

Approach	Isolation	Scalability	Cost	Complexity	Best Use Case
Shared Table	❌ Low	✅ High	✅ Low	⚠️ Medium	Mass-market SaaS
Schema per Tenant	⚠️ Medium	⚠️ Medium	⚠️ Medium	⚠️ Medium	Mid-sized SaaS, customization
Database per Tenant	✅ High	❌ Low	❌ High	✅ High	Enterprise, compliance-heavy

---

• Isolation: How well tenant data and resources are separated from each other. Higher isolation means less risk of data leakage or resource contention between tenants.
• Scalability: How easily the system can handle more tenants or increased workload. High scalability means you can add more tenants with minimal changes or cost.
• Cost: The financial expense of running and maintaining the architecture. Lower cost means less infrastructure and operational overhead.
• Complexity: How difficult the architecture is to set up, manage, and automate. Higher complexity means more effort is needed for deployment, monitoring, and maintenance.

---

🌀 Hybrid Approach: Case Study

In real-world SaaS, combine strategies for the best balance of cost, scalability, and isolation is possible.

Example:
Suppose have 100 tenants:

• 90 small tenants use the Shared Table (single schema) approach for maximum efficiency and low cost.
• 10 large, enterprise tenants each get their own dedicated database for maximum isolation and compliance.

Benefits:

• Keeps costs low for most tenants.
• Provides strong isolation and customizability for high-value clients.
• Lets scale and adapt as tenant needs change.

When to use:

• have a mix of small and large clients.
• Some tenants require strict data isolation or custom features.
• You want to optimize infrastructure costs while supporting enterprise requirements.

---

graph TD
    App["Application"]
    SharedDB["Shared DB (Single Schema)"]
    subgraph SmallTenants["90 Small Tenants"]
        T1["Tenant 1"]
        T2["Tenant 2"]
        T3["..."]
        T90["Tenant 90"]
    end
    SharedDB --> T1
    SharedDB --> T2
    SharedDB --> T3
    SharedDB --> T90

    subgraph BigTenants["10 Big Tenants"]
        BT1["Big Tenant 1"] --> DB1["Dedicated DB 1"]
        BT2["Big Tenant 2"] --> DB2["Dedicated DB 2"]
        BT3["..."]
        BT10["Big Tenant 10"] --> DB10["Dedicated DB 10"]
    end

    App --> SharedDB
    App --> DB1
    App --> DB2
    App --> DB10