In short
Most products should use a shared database with a tenant identifier on every row, enforced at the database rather than in application code. Separate databases per tenant suit strict isolation requirements at significant operational cost. The decision that matters more than either is modelling the organisation as a first-class entity from the start, because retrofitting it means rewriting every query and migrating live data.
The three patterns
Shared database, shared schema. Every table has a tenant identifier column, and every query filters on it. Simplest to operate, cheapest to scale, and what most products should use.
Shared database, separate schemas. Each tenant gets its own schema within one database. Better isolation, but migrations must run across every schema, which becomes slow and error-prone past a few hundred tenants.
Separate databases. Each tenant gets their own. Strongest isolation and the only option that satisfies some regulatory requirements. Also the most operationally expensive: migrations, backups, monitoring and connection management all multiply.
A hybrid exists and is common in practice: shared for most customers, dedicated for the few who require or pay for it.
Choosing between them
Start with shared schema unless something specific rules it out.
Regulatory requirements may mandate physical separation. Check before deciding rather than after.
Very different data volumes. One tenant with a hundred million rows alongside hundreds with thousands creates query planning and performance problems that separation resolves cleanly.
Per-tenant customisation of the schema itself, which is a strong sign the product is heading somewhere difficult, but does happen.
Contractual isolation commitments made during a sale.
Absent those, shared schema wins on operational cost by a wide margin, and the isolation concern is addressed by enforcement rather than by separation.
Enforce isolation in the database
With a shared schema, every query must filter by tenant. Doing that in application code means every query — including background jobs, admin tools, and analytics scripts — remembering to.
One forgotten filter is a cross-tenant leak. Use row level security so the database enforces it regardless of how a query was written, and set the tenant context in one place your application cannot bypass.
If RLS is unavailable, enforce tenancy in a single data-access layer rather than in individual queries, and test cross-tenant access explicitly.
The decision that actually hurts later
Not the isolation pattern. Whether an organisation is a real entity in your model.
Many products start with users only. Someone signs up, owns their data, and tenancy is implied by user identity. It works until the first customer says can my colleague have access too — which is usually within months.
Retrofitting organisations at that point means adding an organisation table, changing ownership on every relevant table, updating every query, migrating live data, and reworking permissions. It is one of the more painful migrations in a young product.
Model it from the start: an organisation entity, users belonging to organisations through a membership record with a role, and data owned by the organisation rather than by the user. Even with one user per organisation initially, the structure costs almost nothing now and saves a quarter later.
Membership is not a column
A user's role should live on the membership joining them to an organisation, not on the user record.
This matters because people belong to more than one organisation — a consultant with several clients, an agency managing accounts, someone who changed jobs. A role on the user record cannot express "admin here, viewer there", and discovering that later means the same migration problem in miniature.
The membership record is also the right place for invitation state, join date, and per-organisation settings.
Practical details worth deciding early
How organisations are identified in URLs. A slug or identifier in the path makes the current context explicit and shareable, and avoids the confusion of a hidden "current organisation" that changes underneath a user with several.
What happens when the last admin leaves. An organisation with no administrator is a support ticket. Prevent removing the final admin, or provide a documented recovery route.
Whether data can move between organisations. Usually asked eventually, and much easier if ownership was modelled explicitly.
How deletion works. Deleting an organisation is a large cascading operation touching every table, and it needs a deliberate design rather than a foreign key cascade discovered in production.
Per-tenant limits and plans. Where the plan lives, and how limits are enforced.
Noisy neighbours
With a shared database, one tenant can degrade performance for everyone — a heavy report, a bulk import, an integration in a loop.
Mitigate with per-tenant rate limits, background jobs on separate queues so one tenant's batch does not delay another's urgent work, and query timeouts. Monitor resource use per tenant so you can identify which one caused an incident, which is otherwise surprisingly hard.
The summary
Shared schema with a tenant identifier, enforced in the database rather than by convention. Organisations as first-class entities from day one, with membership records carrying roles. Explicit decisions about deletion, limits, and URL structure.
Get the entity model right and the isolation strategy can change later. Get the entity model wrong and everything downstream is harder — which is the general shape of most schema decisions.
If you are building something that will have organisations and want the model reviewed before it holds real data, book a call.
Common questions
What is the best multi-tenancy model?
A shared database with a tenant identifier on every row, for most products — it is simplest to operate and cheapest to scale. Separate schemas or separate databases suit strict isolation requirements or very uneven data volumes, at significantly higher operational cost.
How do you stop data leaking between tenants?
Enforce isolation in the database with row level security rather than relying on every query to filter correctly, and set the tenant context in one place the application cannot bypass. Application-level filtering depends on background jobs, admin tools and ad-hoc queries all remembering, and one omission is a leak.
Should I model organisations from the start?
Yes, even with one user each initially. Many products start with users only and tenancy implied by identity, which breaks the first time a customer asks for a colleague to have access. Retrofitting means changing ownership on every table, updating every query, and migrating live data.
Where should a user's role be stored?
On the membership record joining them to an organisation, not on the user record. People belong to several organisations — consultants, agencies, job changes — and a role on the user cannot express being an admin in one place and a viewer in another.
How do you handle one tenant slowing down others?
Per-tenant rate limits, separate background job queues so one tenant's bulk import does not delay another's urgent work, and query timeouts. Monitor resource use per tenant, since identifying which tenant caused an incident is otherwise surprisingly difficult.
