A SaaS application stores data for many customer organizations in one relational database, and every table carries a tenant_id column naming the owning organization. Explain how that discriminator column is meant to work: what must be true of every query, of primary keys and indexes, and of unique and foreign-key constraints for the design to be correct?

## What a tenant and a discriminator column are A **tenant** is one customer organization on a shared application - a company, a workspace, an account. In the *pooled* (shared-table) layout, all tenants' rows live in the same tables, and a **discriminator column**, conventionally `tenant_id`, records the owner of each row. Nothing in the storage separates tenants; the separation exists only because every access path applies the right filter. That is the central thing to understand: in this layout, isolation is a *code and schema* property, not a physical one. ## Rule 1: every access path filters on tenant_id A SELECT missing the predicate returns other customers' data. An UPDATE or DELETE missing it modifies other customers' data - much worse, because it is silent and permanent. Because a single forgotten predicate is a breach, mature systems do not rely on discipline: they route all data access through one place that injects the tenant predicate from an authenticated request context (a repository base class, an ORM global filter, a set of views), and often add database **row-level security** policies underneath as a second, independent layer. Never trust that an id is unguessable: always re-filter server-side, or an attacker who learns another tenant's row id reads it directly. ## Rule 2: tenant_id leads composite keys and indexes A B-tree index is sorted on its column list and can only restrict a scan by a **leading prefix**. An index on `(created_at)` in a table shared by 5,000 tenants means a query for "my last 50 invoices" walks index entries for all 5,000 tenants in that date range and throws most away - work that grows with total tenants, not with your tenant. An index on `(tenant_id, created_at)` seeks straight to that tenant's slice. The same argument applies to the primary key. `PRIMARY KEY (tenant_id, entity_id)` also clusters (in engines with clustered or index-organized tables) each tenant's rows together, so a small tenant's whole dataset may be a handful of pages, which is far better for buffer-cache hit rate than rows scattered across the table by a global surrogate key. ## Rule 3: uniqueness is per tenant Business uniqueness rules are almost always *within* a customer: invoice numbers, usernames, SKU codes. `UNIQUE (email)` in a pooled table means one tenant registering [email protected] prevents another tenant from doing so - a support incident and an information leak, since the failure reveals the address exists somewhere. Scope it: `UNIQUE (tenant_id, email)`. The rare exception is a genuinely global identity (a login that spans tenants), which then belongs to a separate, deliberately global table. ## Rule 4: foreign keys should carry tenant_id A plain `FOREIGN KEY (customer_id) REFERENCES customer(id)` permits an invoice in tenant A to reference a customer in tenant B if application code ever gets it wrong. Widening the child key to `(tenant_id, customer_id)` referencing `(tenant_id, customer_id)` makes crossing a tenant boundary a constraint violation - the database enforces the invariant instead of hoping the code does. ## What the pooled layout buys and costs It is the cheapest per tenant: one schema, one connection pool, one migration, and idle tenants consume almost nothing, which is what makes freemium and long-tail SaaS economically viable. The costs are the mirror image: the weakest isolation (a bug leaks data across customers), per-tenant restore and per-tenant deletion are surgery rather than a file operation, one heavy tenant's queries evict everyone's cached pages, and the optimizer's table-level statistics describe an *average* tenant, so plans chosen for a 1,000-row tenant can be badly wrong for a 50-million-row one. Those are the reasons teams reach for schema-per-tenant or database-per-tenant. ## Beyond the database The discriminator has to follow the data everywhere: cache keys, search-index documents, message payloads, file and object storage prefixes, and exported reports all need the tenant in the key. Isolation that stops at the database boundary leaks at the first derived store.