Introduction

A shared-table multi-tenant database stores many tenants in the same physical tables and separates them logically with a tenant key. It is one of the cheapest and simplest SaaS data models, but it only works well if tenant isolation is built into every table, query, index, and permission rule.

The Core Model

In the shared-table approach, tenant-specific rows live side by side:

• one customers table for all tenants
• one orders table for all tenants
• one users table for all tenants

The required design rule is simple:

• every tenant-owned row carries a tenant identifier

Without that, you do not have multi-tenancy. You just have a data leak waiting to happen.

Start With the Tenant Key

The most important column is usually something like:

• 'tenant_id'
• 'account_id'
• 'organization_id'

It should appear in every table that stores tenant-scoped data.

A practical schema might look like this:

1CREATE TABLE tenants (
2    tenant_id UUID PRIMARY KEY,
3    name TEXT NOT NULL
4);
5
6CREATE TABLE customers (
7    tenant_id UUID NOT NULL,
8    customer_id UUID NOT NULL,
9    email TEXT NOT NULL,
10    name TEXT NOT NULL,
11    PRIMARY KEY (tenant_id, customer_id),
12    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id)
13);
14
15CREATE TABLE orders (
16    tenant_id UUID NOT NULL,
17    order_id UUID NOT NULL,
18    customer_id UUID NOT NULL,
19    total_cents INTEGER NOT NULL,
20    PRIMARY KEY (tenant_id, order_id),
21    FOREIGN KEY (tenant_id, customer_id)
22        REFERENCES customers(tenant_id, customer_id)
23);

Notice the pattern: composite keys include tenant_id. That prevents accidental cross-tenant joins.

Why Composite Keys Help

A lot of shared-table designs fail because tenant_id exists but is treated as an optional filter rather than part of identity.

If customer_id is globally unique, developers may get lazy and stop including tenant_id in joins. That makes future mistakes easier. Composite keys force the tenant boundary into the schema itself.

That is not the only valid approach, but it is one of the safest.

Index for Tenant-Scoped Queries

Most application queries are tenant-scoped, so indexes should reflect that.

1CREATE INDEX idx_customers_tenant_email
2    ON customers (tenant_id, email);
3
4CREATE INDEX idx_orders_tenant_customer
5    ON orders (tenant_id, customer_id);

Putting tenant_id first helps the database prune work quickly when the application asks for one tenant's rows.

Enforce Isolation in the Application Layer

The application should never run a tenant-scoped query without a tenant filter.

Bad:

SELECT * FROM orders WHERE order_id = $1;

Better:

1SELECT *
2FROM orders
3WHERE tenant_id = $1
4  AND order_id = $2;

That sounds obvious, but accidental missing tenant filters are the biggest risk in shared-table systems.

Database-Level Protection Is Better Than Hope

If your database supports row-level security, use it. Application checks are necessary, but defense in depth is better.

PostgreSQL example:

1ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
2
3CREATE POLICY tenant_isolation_orders
4ON orders
5USING (tenant_id = current_setting('app.tenant_id')::uuid);

Then the application sets the tenant context on the session before running queries. This does not remove the need for correct query design, but it gives you another guardrail.

Uniqueness Must Usually Be Tenant-Scoped

Many business rules should be unique per tenant, not globally unique.

For example, customer email may need to be unique within one tenant but allowed in another tenant.

CREATE UNIQUE INDEX uq_customers_tenant_email
    ON customers (tenant_id, email);

That is a subtle but important part of schema design. Global uniqueness often creates the wrong constraints in SaaS systems.

When Shared Tables Are a Good Fit

This model is strongest when:

• tenants are small to medium
• workloads are similar
• cost matters
• operational simplicity matters
• cross-tenant analytics are useful

It is weaker when:

• one tenant is extremely large
• data residency rules differ by tenant
• per-tenant restore is critical
• noisy-neighbor isolation is strict

At that point, separate schemas or separate databases may be a better architecture.

Migration and Operations

A nice property of shared tables is that schema migrations happen once for everybody. That simplifies rollout, but it also means one bad migration affects every tenant.

Operationally, you should plan for:

• tenant-aware backups
• tenant-scoped export tools
• per-tenant rate limiting
• query monitoring by tenant

Those become important long before you outgrow the schema itself.

Common Pitfalls

• Forgetting tenant_id on one table or one join path.
• Using globally unique IDs and then omitting tenant filters by accident.
• Building indexes without leading tenant columns, which hurts tenant-scoped query performance.
• Relying only on application discipline instead of adding database-level guardrails.
• Choosing shared tables even when a few very large tenants will dominate the system.

Summary

• Shared-table multi-tenancy means all tenants share the same tables but each row carries a tenant key.
• Put tenant_id into every tenant-owned table and into key relationships.
• Design indexes and uniqueness constraints with tenant scope in mind.
• Use database protections such as row-level security where available.
• Shared tables are efficient, but they only stay safe if isolation is treated as a schema rule, not a coding convention.