# Multi-Tenant RBAC Schema Design: 7 Tables, Step by Step (With ERD)

> A complete multi-tenant RBAC schema needs seven tables: tenants, users, and members for identity, roles and permissions for access, and two junction tables for the N:N relationships between them. This part derives the identity foundation from use cases; Part 2 builds roles and permissions on top and generates the permission list from the routing table.

Source: https://schemity.com/blog/design-data-model-for-multi-tenant-rbac/

## Multi-Tenant RBAC Data Model, Part 1: Tenants, Users, and Members

A complete multi-tenant RBAC schema is seven tables: `tenants`, `users`, and `members` for identity; tenant-scoped `roles` and system-wide `pems` (permissions) for the vocabulary of access; and two junction tables - `members_roles` and `roles_pems` - carrying the N:N relationships that make role-based access control work. Here is the finished model:

![The complete multi-tenant RBAC ERD: users and tenants joined through members, members joined to tenant-scoped roles through members_roles, and roles joined to system-wide pems through roles_pems](https://schemity.com/images/blog/design-roles-and-permissions-for-multi-tenant-rbac/full-rbac-erd-1.webp)

*Part 1 of 2. This part derives the identity foundation - `tenants`, `users`, and `members`, where a member is one user's membership in one specific tenant - from use cases, so you can see why each table has to exist. If you already have identity settled and want the access-control half, go straight to [roles, permissions, and where the permission rows come from](https://schemity.com/blog/design-roles-and-permissions-for-multi-tenant-rbac/).*

## Behavior Description

Assume we are building a KPI management platform. Multiple companies can create accounts (tenants) and let their employees access the platform.

- A user can be assigned to one or several roles. Their permissions are the sum of those roles' permissions.
- A user cannot access features outside their permissions. Even if they know the API specs and bypass the UI, they will still be blocked at the API layer.
- Admins can change a user's roles or a role's permissions at any time. The only inconvenience is that the affected user needs to log out and log back in for the changes to take effect.

Sounds straightforward, right? Let's look at what happens under the hood.

## Deriving the Data Model from Use Cases

Before implementing, I take time to think about what the system needs to do and what data structures are required to support those use cases.

The first thing we hear in the spec is "multiple tenants." A system that serves multiple companies and their users. That gives us at least two objects to start with: `tenants` and `users`.

![Tenants and users entity](https://schemity.com/images/blog/design-data-model-for-multi-tenant-rbac/tenants-and-users-entity.webp)

> Note: I use the plural convention for table names throughout this post.

## Defining the Relationship Between Tenants and Users

Now we have the foundation, but the relationship between `tenants` and `users` is not yet defined. To define it, we need to find the right constraint. There are two cases:

- **Case 1:** One tenant can have many users. One user must use [a unique email per tenant](https://schemity.com/blog/why-erd-tools-draw-1-1-relationships-as-1-n/).
- **Case 2:** One tenant can have many users. One user can use the same email across different tenants.

Consider this scenario: you are a contractor working for two different companies that both use this platform. If we go with Case 1, the best you can do is maintain two separate emails for two separate tenants on the same system. That is a real inconvenience.

Notice I used the word **person** instead of **user** here. Technically, every tenant is independent. There is no good reason to force a person to use a different email just to become a user of another tenant.

**Case 2 is the preferred design.**

## Tenant Identification at Login

This raises a natural question: if a user can log in with the same email across multiple tenants, how do we know which tenant they belong to?

The answer is that when a user logs in, we need to identify the tenant. There are a few approaches:

- Ask the user to type a tenant code in the login form. Bad idea.
- Provide a typeahead field to help the user find their tenant code. Still a bad idea.
- Give each tenant a subdomain or sub-path, such as `tenant1.ourplatform.com` or `ourplatform.com/tenant1`. This is the widely accepted approach, especially the subdomain variant. The only tradeoff is that instead of a standard SSL certificate, you need a wildcard SSL certificate.

## The Members Table

Another natural question follows: if users share an email across tenants, can they also share a password?

No. Password policies can differ per tenant, so sharing passwords across tenants is not acceptable. This leads us to a third object: the `members` table. A member represents [a user's membership in a specific tenant](https://schemity.com/blog/you-cannot-judge-coupling-and-cohesion-from-the-same-view/), containing tenant's specific information.

The naming is doing real work here. `users` answers "who is this person?" and `members` answers "who are they inside this tenant?" - two different questions, so two different tables.

![Members entity](https://schemity.com/images/blog/design-data-model-for-multi-tenant-rbac/members-entity.webp)

The relationship is clear: one user can have many members, and each member belongs to exactly one tenant. With this simple three-table model - `tenants`, `users`, and `members` - we satisfy the core constraint cleanly: a person can use the same email across multiple independent tenants.

## Recognizing the Many-to-Many Relationship

Look closely at the diagram. Both the N (of 1:N) ends from `users` and `tenants` point to the `members` table. This is a sign that `users` and `tenants` have an [N:N relationship](https://schemity.com/blog/you-have-been-reading-erd-notation-wrong/) - a user can belong to multiple tenants, and a tenant can have multiple users. The `members` table is effectively the [join table](https://schemity.com/doc/auto-junction-tables/).

With that insight, we can express the data model more precisely:

![Members entity as an N:N junction between users and tenants](https://schemity.com/images/blog/design-data-model-for-multi-tenant-rbac/tenants-users-entity.webp)

That is the normalization instinct doing its job, and the reflex that comes with it is to make the two foreign keys the primary key - the pairing itself becomes the row's identity. For a pure join table that is the correct shape. For `members` it is not, and two signals say so before you write a line of SQL.

**A member has a lifecycle of its own.** A pure junction row has no state beyond the pairing it records: this role has this permission, and that is the whole story. `members` is already carrying a password, a failed-login count, and tenant-scoped settings, and it will grow more - invited, active, suspended, removed. The moment a row has its own state and its own history, it is an entity that happens to sit between two others, not a link between them.

**Other tables are going to point at it.** Roles assigned to a membership, one-time passwords, audit records, anything owned by a person inside a tenant - each one needs a foreign key to the membership. With a composite primary key, every one of those becomes a two-column foreign key, repeated in every child table, forever. And if the pairing ever has to change, everything referencing it has to change with it.

So `members` gets a surrogate `id` as its primary key, keeps `tenant_id` and `user_id` as ordinary foreign key fields, and enforces the N:N with [a composite unique constraint over the pair](https://schemity.com/doc/check-constraints-composite-unique/) - one membership per user per tenant. The guarantee is identical; what changes is the answer to "what is this row?" That is the general rule for junction tables: [keep the composite primary key for a pure pairing, and give the junction its own id when it has a lifecycle or gets referenced](https://schemity.com/blog/many-to-many-shouldnt-mean-hand-building-the-junction-table/). We will meet the other side of that rule in Part 2, where `roles` and `permissions` join through a table that really is nothing but a pairing.

*Part 2 of 2: [Roles and Permissions Table Design - Why Permissions Are Not Tenant-Scoped](https://schemity.com/blog/design-roles-and-permissions-for-multi-tenant-rbac/). It builds the four remaining tables on this foundation, and shows how your routing table becomes the source of truth for what permissions exist.*

## Frequently asked questions

### What is a multi-tenant RBAC schema?

It is a database design where multiple tenants (organizations) share one database, and access control is role-based: members get roles, roles get permissions. The core tables are tenants, users, members, roles, permissions, and two junction tables - one linking members to roles and one linking roles to permissions.

### What is the difference between a user and a member in a multi-tenant system?

A user is a person: one email, existing exactly once. A member is that person's membership inside a specific tenant, carrying tenant-scoped state like status and roles. One human is one users row and potentially many members rows - one per tenant they belong to.

### How do I model the role-permission relationship?

As an N:N relationship through a junction table: a role grants many permissions, and a permission appears in many roles. The junction table holds two foreign keys (role id and permission id), and a composite unique constraint over the pair prevents duplicate grants.