Schema design for multi-tenant apps

One product, many customers, each expecting their data isolated. MySQL does not give you tenants for free. You choose how to partition data: shared tables with a tenant key, schema per tenant, or database per tenant. Each pattern has different ops and query cost.

Shared table (row-level tenancy)

One set of tables; every row carries tenant_id. Simplest to deploy and migrate. Every query must filter by tenant – miss it once and you leak data across customers.

CREATE TABLE projects (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_tenant_name (tenant_id, name),
  KEY idx_tenant_created (tenant_id, created_at)
);

SELECT id, name FROM projects
WHERE tenant_id = 42
ORDER BY created_at DESC;

Put tenant_id first in composite indexes when queries always scope by tenant. Foreign keys from child tables include tenant_id where practical so orphans cannot cross tenants.

Schema per tenant

Each tenant gets tenant_42.projects, same table layout. Stronger isolation at the namespace level. Migrations must run across all schemas – scripted and tested. Connection pools either switch schema per request or use separate users (heavy).

CREATE DATABASE tenant_42;
USE tenant_42;

CREATE TABLE projects (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Suits enterprise customers who demand dedicated schema or custom extensions per tenant. Operational cost scales with tenant count.

Database per tenant

Full separation – own database instance or at least own database on shared server. Best isolation, highest cost. Backup, monitoring, and schema changes multiply. Common for regulated industries or very large tenants.

Enforcing tenant isolation in shared tables

• Middleware sets tenant from auth – never from unchecked request params alone
• ORM global scope or repository layer appends tenant_id to every query
• Database views per tenant for read-only tools (advanced but clear boundary)
• Row-level security is not native in MySQL – app discipline or views with SECURITY DEFINER routines
• Integration tests that assert cross-tenant reads return nothing

-- Dangerous: tenant_id from query string without auth check
-- Safe pattern: tenant_id from session, bound parameter
SELECT * FROM invoices
WHERE tenant_id = ? AND id = ?;

Tenant metadata table

CREATE TABLE tenants (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  slug VARCHAR(64) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  plan ENUM('free', 'pro', 'enterprise') NOT NULL DEFAULT 'free',
  settings JSON,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Plans, limits, and feature flags live here. Business tables reference tenants.id. Slug is for URLs; numeric id is for joins.

Choosing a pattern

• Shared table – default for SaaS with many small tenants, uniform schema
• Schema per tenant – moderate count, some customisation, still one server
• Database per tenant – few large tenants, compliance, or noisy neighbour isolation

You can hybrid: shared tables for small tenants, migrate big customers to dedicated schema when they outgrow neighbours. Document the promotion path before you need it.

The next tutorial covers pairing MySQL with PHP and Python apps at scale – pooling, N+1, and keeping the database out of the hot path.