Multi-Tenancy Models: Database-per-Tenant, Schema-per-Tenant, and Shared Tables

Compare the three dominant multi-tenancy data-isolation models — database-per-tenant, schema-per-tenant, and shared-tables-with-tenant-id — along isolation strength, operational complexity, and cost, and implement automatic tenant scoping for the shared-tables model in PHP.

A multi-tenant application serves multiple independent customers ('tenants' — companies, organizations, or accounts) from one codebase, and one of the earliest, hardest-to-reverse architectural decisions is how their data is isolated from each other at the storage layer. Get this wrong early and either you pay an enormous migration cost later, or worse, you ship a data-isolation bug that leaks one customer's data to another — the single most reputation-destroying category of bug a SaaS product can have. There are three well-established models, and each is a real, defensible choice depending on your isolation requirements, team size, and expected tenant count — there is no universally 'correct' answer, only trade-offs.

Database-per-Tenant

In this model, every tenant gets their own, entirely separate database — a distinct MySQL/PostgreSQL database (potentially even on a distinct database server) with the full application schema replicated into it. The application, on each request, determines which tenant is making the request (from a subdomain, an API key, or an authenticated session) and connects to that tenant's specific database for the duration of the request.

This is the strongest possible isolation model: there is no shared table, no shared connection, and — with proper credential separation — no possible way for one tenant's query to accidentally return another tenant's row, because the data doesn't exist in the same database at all. It also directly supports per-tenant backup/restore, per-tenant data residency requirements (a specific tenant's data can live in a specific geographic region for compliance reasons), and per-tenant performance isolation (one tenant running an expensive report doesn't slow down another tenant's queries, since they hit entirely separate database instances or at least separate logical databases).

The cost is substantial operational overhead that scales with tenant count. Every schema migration must run once per tenant database — a hundred tenants means running (and verifying) the same migration a hundred times, and a failed migration on tenant #63 needs its own remediation, not a single rollback. Connection management becomes more complex, since the application needs a way to route to the correct database credentials per request, often via a connection-pooling layer or a per-tenant credential lookup. And infrastructure cost tends to scale with tenant count in a way that's hard to amortize — even a mostly-idle tenant typically still needs its own provisioned (if small) database resources.

• Strongest isolation: physically separate databases, no shared table can ever leak across tenants. • Enables per-tenant data residency, backup/restore, and performance isolation naturally. • Migrations must run N times (once per tenant) — a failure on one tenant is an isolated incident, not a global rollback. • Operational/infrastructure overhead scales roughly linearly with tenant count.

Schema-per-Tenant

This middle-ground model, most naturally supported on PostgreSQL (which has first-class schema namespacing within one database), gives each tenant their own schema — a named namespace holding a full copy of the application's tables — inside a single shared database instance. The application switches the active schema (via SET search_path TO tenant_schema in Postgres) per request, so the same table names (users, orders) resolve to that tenant's own copy of the data without any tenant_id column needed on any table.

This lands between the two other models on almost every axis. Isolation is meaningfully stronger than shared tables — a query without a tenant filter simply can't see another tenant's schema at all, since it isn't querying it — though it isn't as absolute as fully separate databases, since all tenants still share the same database instance, the same connection pool capacity, and the same underlying hardware, so one tenant's runaway query can still degrade performance for others sharing that instance. Operationally, migrations still need to run once per tenant schema (the same N-times problem as database-per-tenant), but connection management is simpler, since it's one database connection with a schema switch rather than routing to entirely separate credentials.

Schema-per-tenant tends to work best for a moderate tenant count (dozens to low hundreds) where the migration-fan-out cost is tolerable but the team wants isolation stronger than a shared tenant_id column provides; it becomes unwieldy well before database-per-tenant does at very high tenant counts, because PostgreSQL's own catalog and connection-pooling behavior start to strain when a single database has to track schema metadata for thousands of schemas.

• Each tenant gets a named schema (namespace) with a full table set, inside ONE shared database — most natural on PostgreSQL. • Isolation stronger than shared tables (no tenant_id needed, wrong-schema queries simply can't see other data) but weaker than database-per-tenant (shared instance/hardware/connection pool). • Migrations still run once per tenant schema — same N-times operational cost as database-per-tenant. • Sweet spot: dozens to low hundreds of tenants; strains at very high tenant counts due to Postgres catalog/connection overhead.

Shared Tables with a tenant_id Column

The simplest model to build and operate: every tenant's data lives in the same tables as everyone else's, distinguished only by a tenant_id (or organization_id) column present on every tenant-scoped table. A single schema, a single migration run, one connection pool serving all tenants — from a pure infrastructure and DevOps perspective, this is by far the cheapest and easiest model to run, especially at high tenant counts (thousands to millions), because adding a new tenant is just inserting a row, not provisioning a database or schema.

The isolation trade-off is the sharpest of the three models: because every tenant's rows sit in the very same table, the ENTIRE burden of isolation shifts onto application code getting every single query right. There is no structural barrier — no separate database, no separate schema — preventing a query that forgets its WHERE tenant_id = ? clause from returning or modifying another tenant's rows; it is purely a matter of code discipline enforced (or not) by whatever access layer sits between application code and the database.

This is why the shared-tables model, despite being the simplest to operate, demands the most rigorous application-level tenant-scoping discipline of the three — every query touching a tenant-scoped table must be scoped, with no exceptions, and the safest way to guarantee that in practice is to make the scoping automatic and structural in the application's data-access layer rather than something each individual query author has to remember.

• One schema, one set of tables, all tenants share them, distinguished by a tenant_id column — cheapest to run at scale. • Adding a tenant is inserting a row, not provisioning a database/schema; one migration run covers everyone. • Weakest isolation: nothing but application code stands between a missing WHERE clause and a cross-tenant data leak. • Demands automatic, structural query scoping (not developer memory) to be safe in practice — covered further in the next lesson.

Comparing the Three Models

Ranking isolation strength from strongest to weakest: database-per-tenant, then schema-per-tenant, then shared-tables. Ranking operational complexity from lowest to highest at scale: shared-tables is the simplest to operate as tenant count grows into the thousands (one migration, one connection pool), schema-per-tenant is a step up in complexity (N schema migrations, Postgres-specific), and database-per-tenant is the most operationally demanding (N databases, N migration runs, per-tenant credential routing) but scales that complexity fairly linearly and predictably rather than degrading unpredictably.

Cost tends to follow the same ordering but flips at different tenant-count regimes: at low tenant counts with high per-tenant revenue (an enterprise B2B SaaS with a few dozen large customers, some requiring contractual data residency guarantees), the operational overhead of database-per-tenant is easily justified and often demanded by customers directly. At high tenant counts with low per-tenant revenue (a consumer or prosumer SaaS with tens of thousands of small accounts), the linear-scaling infrastructure cost of database-per-tenant becomes prohibitive, and shared-tables (or a hybrid, where most tenants share tables but a few large enterprise customers get dedicated databases) is the pragmatic choice.

Many real systems land on a hybrid: shared tables for the majority of tenants, with an escape hatch to a dedicated database for a handful of large customers whose contracts, compliance requirements, or performance needs justify the extra operational cost — the architecture doesn't have to be a single, uniform choice applied identically to every tenant.

• Isolation strength: database-per-tenant > schema-per-tenant > shared-tables. • Operational simplicity at high tenant count: shared-tables > schema-per-tenant > database-per-tenant. • Low tenant count / high revenue per tenant / compliance needs → database-per-tenant often wins despite the overhead. • High tenant count / low revenue per tenant → shared-tables (or a hybrid) is usually the pragmatic choice. • Hybrid models (shared tables for most, dedicated databases for a few large accounts) are common and legitimate in practice.

Code Examples

By routing every tenant-scoped read and write through this single class, the tenant_id filter is applied automatically and cannot be bypassed by an individual call site forgetting it — the scoping lives in one place instead of being repeated (and potentially forgotten) at every query in the codebase.

"SELECT * FROM {$table} WHERE tenant_id = :tenant_id AND (...)" — tenant scoping is baked into the access layer itself.

$pdo->query("SELECT * FROM invoices WHERE status = 'open'");

$query->select('invoices', 'status = :status', ['status' => 'open']); // routes through TenantScopedQuery

A raw query against a shared table with no tenant_id filter returns every tenant's open invoices, not just the current tenant's — exactly the cross-tenant leak the shared-tables model is most vulnerable to when scoping isn't enforced structurally.

Which multi-tenancy model provides the strongest data isolation between tenants? A. Shared tables with a tenant_id column B. Schema-per-tenant C. Database-per-tenant D. All three provide identical isolation

C. Database-per-tenant — Database-per-tenant physically separates each tenant's data into its own database, meaning there is no shared table or schema through which a query could ever accidentally cross tenant boundaries — the strongest isolation of the three models.

What is the main operational cost that database-per-tenant and schema-per-tenant share, which shared-tables avoids? A. Neither requires database migrations at all B. Every schema migration must be run and verified once per tenant (database or schema), rather than once for the whole system C. They cannot use PDO or prepared statements D. They require a separate PHP codebase per tenant

B. Every schema migration must be run and verified once per tenant (database or schema), rather than once for the whole system — Both database-per-tenant and schema-per-tenant require the same migration to be applied N times (once per tenant), while shared-tables applies one migration that covers every tenant at once — a major operational cost difference that grows with tenant count.

Why does the shared-tables-with-tenant_id model require more application-level discipline than the other two models? A. Because PHP cannot connect to shared tables reliably B. Because there is no structural barrier (separate database or schema) preventing a query that omits the tenant_id filter from returning another tenant's data C. Because shared tables cannot use indexes D. Because it requires a NoSQL database instead of a relational one

B. Because there is no structural barrier (separate database or schema) preventing a query that omits the tenant_id filter from returning another tenant's data — In the shared-tables model, every tenant's rows coexist in the same table, so isolation depends entirely on every query correctly filtering by tenant_id — there is no database- or schema-level wall to fall back on if application code forgets, which is why automatic/structural scoping (as in the example) matters so much.

<?php
declare(strict_types=1);

final class TenantScopedQuery
{
    public function __construct(
        private PDO $pdo,
        private readonly int $tenantId, // set once per request from the authenticated session
    ) {}

    /**
     * Every call through this method automatically appends tenant_id = ?,
     * so a caller cannot forget it — the scoping is structural, not optional.
     */
    public function select(string $table, string $where = '1=1', array $params = []): array
    {
        $sql = "SELECT * FROM {$table} WHERE tenant_id = :tenant_id AND ({$where})";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['tenant_id' => $this->tenantId, ...$params]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function insert(string $table, array $data): string
    {
        $data['tenant_id'] = $this->tenantId; // forced onto every insert, cannot be overridden by caller input
        $columns = implode(', ', array_keys($data));
        $placeholders = implode(', ', array_map(fn($k) => ":{$k}", array_keys($data)));
        $stmt = $this->pdo->prepare("INSERT INTO {$table} ({$columns}) VALUES ({$placeholders})");
        $stmt->execute($data
…