Overview
Multi-tenant applications share infrastructure while serving multiple organizations. The central risk is not merely incorrect display; it is one tenant reading or changing another tenant's records. Tenant isolation must be a consistent data-access rule, not a convention remembered by individual developers.
Start with the workload and the invariant
For a shared-schema design, include tenant_id on every tenant-owned row. Build indexes around tenant-scoped access patterns, often beginning with tenant_id. Add composite unique constraints where identifiers are only unique inside a tenant, such as (tenant_id, external_code). Ensure child relationships cannot accidentally connect records from different tenants.
Practical example
CREATE UNIQUE INDEX uq_customer_tenant_code
ON customers (tenant_id, external_code);
CREATE INDEX idx_orders_tenant_created
ON orders (tenant_id, created_at);
Centralize tenant resolution from an authenticated session or trusted token claim. Never accept a tenant ID from a form field as proof of access. Every read, update, delete, export, and background job must apply the same tenant scope. A missing scope in an administrative report can be as dangerous as a missing scope in a detail page.
Trade-offs and failure modes
For stronger relational enforcement, use composite foreign keys that include tenant_id where the schema and engine support the required key structure. This can prevent a child row from referencing a parent belonging to another tenant. Test the exact constraint design and migration behavior before rollout.
Verification and rollout
Create automated tests with at least two tenants and deliberately attempt cross-tenant reads, updates, deletes, search results, file downloads, and exports. Include cache keys: a cache key that omits tenant identity can leak data even when SQL queries are correctly scoped.
Engineering checklist
Log tenant identifiers in operational events without exposing sensitive payloads. Review support tooling and super-admin actions separately, with explicit authorization and audit trails. Tenant isolation is an end-to-end property across SQL, caches, queues, files, and APIs.
Before shipping, review the database version, storage engine, SQL mode, character set, collation, and deployment process used by the target environment. Features and DDL behavior can differ between MySQL and MariaDB releases. Validate the exact statements on a representative staging database, capture a baseline, and keep a tested recovery path for changes that affect production data.