MySQL & Database Design

Multi-Tenant Database Design

13 min Lesson 38 of 40

Multi-Tenant Database Design

Multi-tenant architecture is essential for SaaS applications where a single application instance serves multiple customers (tenants). In this lesson, we'll explore three approaches to multi-tenancy and their trade-offs: shared database with tenant ID, separate databases, and separate schemas.

Multi-Tenancy Approaches

Each approach has distinct advantages and challenges:

Approach 1: Shared Database + Tenant ID
✓ Most cost-effective
✓ Easy to maintain
✗ Risk of data leakage
✗ Difficult to customize per tenant

Approach 2: Separate Database Per Tenant
✓ Complete data isolation
✓ Easy to backup/restore per tenant
✓ Can customize schema per tenant
✗ Higher costs
✗ Complex deployment

Approach 3: Separate Schema Per Tenant
✓ Good isolation within one database
✓ Moderate costs
✗ Database-specific feature
✗ Limited scalability

Approach 1: Shared Database with Tenant ID

The most common approach for SaaS applications:

-- Tenants table
CREATE TABLE tenants (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    subdomain VARCHAR(100) UNIQUE NOT NULL,
    custom_domain VARCHAR(255) UNIQUE NULL,
    plan ENUM('free', 'basic', 'pro', 'enterprise') DEFAULT 'free',
    status ENUM('active', 'suspended', 'trial', 'cancelled') DEFAULT 'trial',
    max_users INT UNSIGNED DEFAULT 5,
    max_storage_mb INT UNSIGNED DEFAULT 1000,
    trial_ends_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_subdomain (subdomain),
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- Users (each belongs to a tenant)
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role ENUM('owner', 'admin', 'user', 'guest') DEFAULT 'user',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    UNIQUE KEY unique_email_per_tenant (tenant_id, email),
    INDEX idx_tenant (tenant_id),
    INDEX idx_email (email)
) ENGINE=InnoDB;

-- Projects (scoped to tenant)
CREATE TABLE projects (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    status ENUM('active', 'archived', 'completed') DEFAULT 'active',
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_tenant (tenant_id),
    INDEX idx_status (tenant_id, status)
) ENGINE=InnoDB;

-- Tasks (scoped to tenant)
CREATE TABLE tasks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    project_id BIGINT UNSIGNED NOT NULL,
    assigned_to BIGINT UNSIGNED NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    status ENUM('todo', 'in_progress', 'review', 'done') DEFAULT 'todo',
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    due_date DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_tenant (tenant_id),
    INDEX idx_project (project_id),
    INDEX idx_assigned (assigned_to),
    INDEX idx_status (tenant_id, status)
) ENGINE=InnoDB;

-- Tenant settings (key-value storage)
CREATE TABLE tenant_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    UNIQUE KEY unique_setting (tenant_id, setting_key),
    INDEX idx_tenant (tenant_id)
) ENGINE=InnoDB;

-- Usage tracking (for billing)
CREATE TABLE tenant_usage (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    metric_type ENUM('storage', 'users', 'api_calls', 'projects') NOT NULL,
    metric_value BIGINT UNSIGNED NOT NULL,
    recorded_at DATE NOT NULL,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    UNIQUE KEY unique_daily_metric (tenant_id, metric_type, recorded_at),
    INDEX idx_tenant_date (tenant_id, recorded_at)
) ENGINE=InnoDB;

-- Audit log (track all tenant actions)
CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id BIGINT UNSIGNED NOT NULL,
    old_values JSON NULL,
    new_values JSON NULL,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_tenant_created (tenant_id, created_at),
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_user (user_id)
) ENGINE=InnoDB;

Critical Security Rule: ALWAYS include tenant_id in every WHERE clause and JOIN condition. Missing a tenant filter can expose data to the wrong tenant!

Essential Queries with Tenant Isolation

Every query must filter by tenant_id:

-- Get tenant projects (CORRECT)
SELECT * FROM projects
WHERE tenant_id = 1
ORDER BY created_at DESC;

-- Get project tasks (CORRECT - double check)
SELECT t.*
FROM tasks t
JOIN projects p ON t.project_id = p.id
WHERE t.tenant_id = 1
    AND p.tenant_id = 1
    AND t.status = 'in_progress';

-- Get user's assigned tasks (CORRECT)
SELECT t.*
FROM tasks t
WHERE t.tenant_id = 1
    AND t.assigned_to = 5
ORDER BY t.due_date;

-- WRONG - Missing tenant_id filter (SECURITY BUG!)
SELECT * FROM tasks WHERE status = 'todo';

-- Application-level tenant context
SET @current_tenant_id = 1;

-- Use session variable in queries
SELECT * FROM projects
WHERE tenant_id = @current_tenant_id;

Best Practice: Use database views or application-level middleware to automatically inject tenant_id filters, reducing the risk of forgetting the filter.

Tenant Isolation Views

Create views to enforce tenant isolation:

-- Create session variable helper
DELIMITER $$
CREATE FUNCTION get_current_tenant_id()
RETURNS INT UNSIGNED
DETERMINISTIC
BEGIN
    RETURN @current_tenant_id;
END$$
DELIMITER ;

-- Tenant-scoped project view
CREATE VIEW tenant_projects AS
SELECT p.*
FROM projects p
WHERE p.tenant_id = get_current_tenant_id();

-- Tenant-scoped task view
CREATE VIEW tenant_tasks AS
SELECT t.*
FROM tasks t
WHERE t.tenant_id = get_current_tenant_id();

-- Usage (after setting @current_tenant_id)
SET @current_tenant_id = 1;
SELECT * FROM tenant_projects;

Approach 2: Separate Database Per Tenant

Complete isolation with separate databases:

-- Master database: tenant registry
CREATE DATABASE saas_master;
USE saas_master;

CREATE TABLE tenants (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    subdomain VARCHAR(100) UNIQUE NOT NULL,
    database_name VARCHAR(100) UNIQUE NOT NULL,
    database_host VARCHAR(255) DEFAULT 'localhost',
    status ENUM('active', 'suspended', 'trial') DEFAULT 'trial',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_subdomain (subdomain),
    INDEX idx_database (database_name)
) ENGINE=InnoDB;

-- Create tenant database dynamically
SET @tenant_db = 'tenant_1_db';
SET @sql = CONCAT('CREATE DATABASE ', @tenant_db);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Switch to tenant database
USE tenant_1_db;

-- Standard schema (same for all tenants)
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role ENUM('owner', 'admin', 'user') DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE projects (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    status ENUM('active', 'archived') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- No tenant_id needed - complete isolation!

Deployment Strategy: Use database migration scripts that run against each tenant database. Track migration versions per tenant in the master database.

Approach 3: Separate Schema Per Tenant

PostgreSQL-style schema isolation (MySQL has limited support):

-- PostgreSQL example (MySQL doesn't support schemas as namespaces)
-- In PostgreSQL:
CREATE SCHEMA tenant_1;
CREATE SCHEMA tenant_2;

-- Tables in separate schemas
CREATE TABLE tenant_1.users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL
);

CREATE TABLE tenant_2.users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL
);

-- Query specific tenant
SELECT * FROM tenant_1.users;

-- MySQL alternative: use prefixed table names
CREATE TABLE tenant_1_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL
) ENGINE=InnoDB;

CREATE TABLE tenant_2_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL
) ENGINE=InnoDB;

Cross-Tenant Queries (Admin/Reporting)

Sometimes admins need to query across all tenants:

-- Get active tenant count
SELECT COUNT(*) AS active_tenants
FROM tenants
WHERE status = 'active';

-- Get total users across all tenants
SELECT
    t.id,
    t.name,
    COUNT(u.id) AS user_count
FROM tenants t
LEFT JOIN users u ON t.id = u.tenant_id
WHERE t.status = 'active'
GROUP BY t.id, t.name
ORDER BY user_count DESC;

-- Get tenant usage summary
SELECT
    t.name AS tenant,
    SUM(CASE WHEN tu.metric_type = 'storage' THEN tu.metric_value ELSE 0 END) AS total_storage_mb,
    SUM(CASE WHEN tu.metric_type = 'api_calls' THEN tu.metric_value ELSE 0 END) AS total_api_calls
FROM tenants t
LEFT JOIN tenant_usage tu ON t.id = tu.tenant_id
    AND tu.recorded_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
WHERE t.status = 'active'
GROUP BY t.id, t.name;

-- Find tenants exceeding limits
SELECT
    t.id,
    t.name,
    t.max_users,
    COUNT(u.id) AS current_users
FROM tenants t
LEFT JOIN users u ON t.id = u.tenant_id AND u.is_active = TRUE
GROUP BY t.id, t.name, t.max_users
HAVING current_users > t.max_users;

Tenant Provisioning Workflow

Complete tenant onboarding process:

-- Step 1: Create tenant
INSERT INTO tenants (name, subdomain, plan, status, trial_ends_at)
VALUES (
    'Acme Corp',
    'acme',
    'trial',
    'active',
    DATE_ADD(NOW(), INTERVAL 14 DAY)
);
SET @tenant_id = LAST_INSERT_ID();

-- Step 2: Create owner user
INSERT INTO users (tenant_id, email, password_hash, full_name, role)
VALUES (
    @tenant_id,
    'owner@acme.com',
    '$2y$10$...',
    'John Doe',
    'owner'
);

-- Step 3: Initialize default settings
INSERT INTO tenant_settings (tenant_id, setting_key, setting_value)
VALUES
(@tenant_id, 'timezone', 'UTC'),
(@tenant_id, 'date_format', 'Y-m-d'),
(@tenant_id, 'language', 'en');

-- Step 4: Create sample project
INSERT INTO projects (tenant_id, name, description, created_by)
SELECT
    @tenant_id,
    'Welcome Project',
    'Your first project',
    id
FROM users
WHERE tenant_id = @tenant_id AND role = 'owner';

-- Step 5: Log provisioning
INSERT INTO audit_logs (tenant_id, action, entity_type, entity_id)
VALUES (@tenant_id, 'tenant_created', 'tenant', @tenant_id);

Billing and Usage Tracking

-- Record daily usage snapshot
INSERT INTO tenant_usage (tenant_id, metric_type, metric_value, recorded_at)
SELECT
    t.id,
    'users',
    COUNT(u.id),
    CURDATE()
FROM tenants t
LEFT JOIN users u ON t.id = u.tenant_id AND u.is_active = TRUE
WHERE t.status = 'active'
GROUP BY t.id
ON DUPLICATE KEY UPDATE metric_value = VALUES(metric_value);

-- Calculate monthly bill
SELECT
    t.id,
    t.name,
    t.plan,
    CASE t.plan
        WHEN 'free' THEN 0
        WHEN 'basic' THEN 29
        WHEN 'pro' THEN 99
        WHEN 'enterprise' THEN 299
    END AS base_price,
    MAX(CASE WHEN tu.metric_type = 'users' THEN tu.metric_value ELSE 0 END) AS peak_users,
    GREATEST(0, MAX(CASE WHEN tu.metric_type = 'users' THEN tu.metric_value ELSE 0 END) - t.max_users) * 5 AS overage_charge
FROM tenants t
LEFT JOIN tenant_usage tu ON t.id = tu.tenant_id
    AND tu.recorded_at >= DATE_FORMAT(NOW(), '%Y-%m-01')
WHERE t.status = 'active'
GROUP BY t.id, t.name, t.plan, t.max_users;

Migration Between Approaches

-- Migrate from shared DB to separate DB
-- 1. Export tenant data
SELECT * INTO OUTFILE '/tmp/tenant_1_users.csv'
FROM users
WHERE tenant_id = 1;

-- 2. Create new database
CREATE DATABASE tenant_1_db;
USE tenant_1_db;

-- 3. Create schema (without tenant_id)
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    -- No tenant_id column needed
    ...
);

-- 4. Import data
LOAD DATA INFILE '/tmp/tenant_1_users.csv'
INTO TABLE users;

-- 5. Update tenant registry
UPDATE tenants
SET database_name = 'tenant_1_db'
WHERE id = 1;

Practice Exercise:

Task: Add tenant feature flags system.

Requirements:

Create a feature flags table
Enable/disable features per tenant
Default features for new tenants
Query to check if tenant has feature access

Solution:

CREATE TABLE features (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE tenant_features (
    tenant_id INT UNSIGNED NOT NULL,
    feature_id INT UNSIGNED NOT NULL,
    is_enabled BOOLEAN DEFAULT TRUE,
    enabled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (tenant_id, feature_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (feature_id) REFERENCES features(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Check if tenant has feature
SELECT EXISTS(
    SELECT 1 FROM tenant_features
    WHERE tenant_id = 1
        AND feature_id = (SELECT id FROM features WHERE name = 'advanced_reporting')
        AND is_enabled = TRUE
) AS has_feature;

-- Get all enabled features for tenant
SELECT f.name, f.description
FROM features f
JOIN tenant_features tf ON f.id = tf.feature_id
WHERE tf.tenant_id = 1 AND tf.is_enabled = TRUE;

Summary

In this lesson, you learned:

Three approaches to multi-tenant database design
Implementing shared database with tenant ID isolation
Security best practices for preventing data leakage
Separate database per tenant architecture
Tenant provisioning and onboarding workflows
Usage tracking and billing calculations
Cross-tenant reporting for administrators

Next Up: In the next lesson, we'll design time-series and analytics databases for handling large-scale data!