Skip to content
Tutorials Center
My learning

Topics

Lesson 70 of 75 ยท Projects

Project: Multi-Tenant SaaS Schema

Design a multi-tenant MySQL schema: tenant_id on every table, composite keys and foreign keys, isolation through views, per-tenant reports and a sharding plan.

A SaaS product serves many customers (tenants) from one application. Each tenant must see only its own data, must never be able to touch another tenant's rows, and should not slow the others down. The way you lay out the database on day one decides how safe, fast and scalable the product will be, and it is very hard to change after you have real customers.

In this project you design a shared-schema multi-tenant database in MySQL: a tenant_id column on every table, composite keys and foreign keys that make cross-tenant mistakes impossible, views that limit what a session can see, reports per tenant, the indexes that make tenant queries fast, and a plan for when one database is no longer enough. It builds on keys and constraints, foreign keys, views and composite indexes.

What runs live here. All live examples run in the SQLite stand-in, which supports composite keys, composite foreign keys, views and EXPLAIN QUERY PLAN. MySQL-only parts (session variables, stored functions, WITH CHECK OPTION, per-tenant accounts, sharding tools) are read-only code.

Three ways to isolate tenants

Database per tenant Strong isolation, heavy to run.
Shared tables tenant_id on every row. Cheapest.
Hybrid Shared by default, big tenants alone.

This project builds the shared-table model.

1 / 2

Choosing the model

With database per tenant, each customer gets its own MySQL schema (in MySQL, "schema" and "database" are the same thing). Isolation is excellent, restoring one tenant from backup is easy, and a noisy tenant is easy to move. The price is operational: thousands of schemas mean thousands of migrations, many open connection pools and slow tooling.

With shared tables, all tenants live in the same tables and each row carries a tenant_id. There is one migration, one connection pool, and cross-tenant analytics are simple. The price is discipline: every query must filter by tenant, or data leaks. This is the model most startups use, and the one we build, plus a plan to graduate large tenants later.

Step 1: tenant_id in every key

The central design rule: every table has tenant_id, and it is the first column of the primary key and of every index that serves tenant queries. Two benefits follow. InnoDB stores rows in primary-key order (InnoDB architecture), so a tenant's rows sit next to each other on disk and a tenant query touches few pages. And a tenant filter can always use the left side of an index.

Child tables reference parents with a composite foreign key (tenant_id, parent_id). That makes it structurally impossible for a task of tenant 1 to point at a project of tenant 2: the pair must exist in the parent table.

Here is a small SaaS: tenants have users, projects and tasks.

SQL Tenant-aware schema and a tenant-safe join

Press Run (or Ctrl + Enter) to see the result here.

Five rows come back, each task with the right project name and assignee. Notice three things in this design:

  • The two tenants both have a user with id 1 and the same e-mail address [email protected]. That is fine: uniqueness is per tenant, thanks to UNIQUE (tenant_id, email). Different customers must be allowed to have the same user names.
  • Every join condition includes tenant_id. If you joined projects on p.id = k.project_id alone, task 1 of Globex would match the projects with id 1 of both tenants. Forgetting the tenant in a join is the most common multi-tenant bug.
  • Ids repeat across tenants here to keep the demo readable. In real MySQL you usually keep a global AUTO_INCREMENT id. InnoDB then needs an index that starts with the auto-increment column, so you write id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (tenant_id, id), KEY (id). The primary key keeps the tenant clustering, and the extra key satisfies the auto-increment rule.

The foreign key as a guard rail

Now try to attach a task of tenant 1 to a project that only exists for tenant 2. This example fails on purpose at the last statement with FOREIGN KEY constraint failed; MySQL answers the same mistake with error 1452:

SQL Cross-tenant references are rejected

Press Run (or Ctrl + Enter) to see the result here.

The first insert succeeds, the second stops with the error. With a plain foreign key on project_id alone, the second insert would have been accepted, because project 5 exists somewhere. The composite key moves a security rule from application code into the schema.

Step 2: keep every query inside one tenant

Constraints protect writes between tables. Reads still depend on the WHERE tenant_id = ? of every query, and somebody will eventually forget it. Two layers help:

  1. A data-access layer in the application that always adds the tenant condition (global scopes in an ORM, a repository base class). This is where most teams enforce it.
  2. A database view that filters by the current tenant, so even hand-written SQL on the view is scoped.

MySQL has no built-in row-level security like PostgreSQL, so views are the closest tool. A view cannot read a user variable directly (@tenant_id in a view definition is error 1351), so the usual MySQL trick is a small function that returns the variable. This block is read-only:

SQL
CREATE FUNCTION current_tenant() RETURNS BIGINT UNSIGNED
  NO SQL DETERMINISTIC
  RETURN @tenant_id;

CREATE SQL SECURITY INVOKER VIEW my_tasks AS
  SELECT id, project_id, assignee_id, title, status
  FROM tasks
  WHERE tenant_id = current_tenant()
  WITH CASCADED CHECK OPTION;

-- application, at the start of every request:
SET @tenant_id = 42;
SELECT * FROM my_tasks WHERE status = 'open';

WITH CHECK OPTION makes inserts and updates through the view fail if the new row would not be visible, so a session scoped to tenant 42 cannot write rows for tenant 43. The application account gets privileges on the views only, not on the base tables. Note that the optimizer does not treat a stored function like a constant, so check the plan with EXPLAIN on a big table (EXPLAIN); for hot paths many teams filter in the data-access layer and keep views for reporting and support tools.

The mechanism is easy to see in SQLite, using a one-row context table in place of the session variable. Notice what happens when no tenant is set:

SQL A tenant-scoped view

Press Run (or Ctrl + Enter) to see the result here.

Tenant 1 sees three tasks, tenant 2 sees two, and a session with no tenant sees zero rows, because tenant_id = NULL is never true. That "fail closed" behaviour is what you want: forgetting to set the tenant returns nothing, instead of everything.

Step 3: per-tenant reporting and limits

SaaS products meter usage: seats, projects, open items. The same shape of query serves billing, plan limits and an admin dashboard. Aggregate each child table separately, then join the results to tenants, so a tenant with no rows still appears:

SQL Usage per tenant and plan limits

Press Run (or Ctrl + Enter) to see the result here.

Acme has 12 tasks, 9 open, well under its limit of 100. Globex has 8 open tasks against a free-plan limit of 5, so it is flagged. Initech has 3 tasks with 2 open, and Hooli, which has no tasks at all, still shows a row with zeros thanks to the LEFT JOIN. In MySQL, SUM(status = 'open') works the same way (a comparison is 1 or 0).

Step 4: indexes with the tenant first

Tenant-scoped queries have the shape "this tenant, this status, newest first". An index that starts with tenant_id lets MySQL jump straight to one tenant's slice. The test below uses 3,000 tasks over three tenants:

SQL The tenant-first index

Press Run (or Ctrl + Enter) to see the result here.

Before the index, the primary key helps find the tenant's rows but the engine still has to sort the open ones by due date with a temporary B-tree. After it, the plan searches the index with tenant_id and status fixed and reads the rows already ordered by due_date, so the 20-row LIMIT stops early. An index like (status, due_date) without the tenant would make the engine walk through other tenants' rows, which is exactly the slowdown a large neighbour causes (designing composite indexes).

Also keep the noisy neighbour in mind. One tenant running a huge report competes for the same buffer pool and CPU as everyone else. Typical defences: run reports on a replica, add rate limits and pagination per tenant, set MAX_EXECUTION_TIME for report queries, and watch per-tenant query cost from your application logs.

Step 5: growth and sharding plan

A single MySQL primary handles a lot, often millions of rows per tenant and thousands of tenants, when the indexes are right. When it no longer does, the tenant_id design gives you a clean way out: split by tenant. All of one tenant's data goes to one shard, so every query still stays inside one database.

flowchart LR
  A["App request<br/>(tenant 42)"] --> D["Tenant directory<br/>tenant -> shard"]
  D --> S1["Shard 1<br/>tenants 1-500"]
  D --> S2["Shard 2<br/>tenants 501-1000"]
  D --> S3["Dedicated DB<br/>very large tenant"]

A small directory table maps each tenant to its shard. Big tenants can get a database of their own.

A workable plan:

  1. Stage 1: one database. Shared tables, tenant_id first in every key, composite foreign keys. Add read replicas for reports.
  2. Stage 2: a tenant directory. A table tenant_shards (tenant_id, shard_id) that the application consults when it opens a connection. Initially every tenant points to shard 1. Putting this in early costs almost nothing.
  3. Stage 3: move tenants. To move a tenant, copy its rows (WHERE tenant_id = ? on every table), switch the directory entry in a short maintenance window, and delete the old rows. With tenant_id leading each key, those copy queries are range scans.
  4. Stage 4: dedicated databases for the biggest or most regulated tenants (data residency, special backups).

Things to design for from the start so that Stage 3 is possible: avoid joins across tenants (reporting for the whole platform belongs in a warehouse), do not depend on globally ordered auto-increment ids (use per-shard ranges, or time-ordered UUIDs), and keep reference data that every tenant shares (countries, currencies) in a table that is copied to all shards.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why use a composite foreign key (tenant_id, project_id) instead of project_id alone?

    The pair must exist in the parent table, so a task of one tenant cannot point to another tenant's project even if the application has a bug.
  2. 2. What is the most common multi-tenant join bug?

    If ids repeat across tenants, a join on id alone matches rows from several tenants and leaks or mixes data.
  3. 3. In a tenant-scoped view where no tenant has been set, why do you see zero rows?

    Comparing with NULL gives unknown, never true. A forgotten tenant therefore fails closed instead of exposing all data.
  4. 4. Why put tenant_id first in indexes and primary keys?

    InnoDB clusters rows by primary key, and an index is only usable from its left side, so tenant queries touch few pages.
  5. 5. What makes moving a big tenant to another database feasible later?

    Splitting by tenant keeps every query inside one shard. A directory lets you repoint a tenant after copying its rows.

Summary

  • Shared tables with a tenant_id column are the cheapest multi-tenant model. Isolation then depends on discipline and on the schema.
  • Put tenant_id first in primary keys and indexes, and make uniqueness per tenant.
  • Use composite foreign keys (tenant_id, parent_id) so cross-tenant references are impossible.
  • Always include tenant_id in join conditions. Scope reads in a data-access layer or views, and make a missing tenant return nothing.
  • Report usage per tenant with separate aggregates and a LEFT JOIN from tenants.
  • Plan growth: a tenant directory, replicas for reports, and sharding by tenant.

Next: Project: Analytics and Reporting Queries.