Relational Database – How to handle many-to-many-to-many relation

Posted on

Question :

I am creating an ERD where i have the following strucutre:

  • A tenant can have multiple users.
  • A user can be in multiple tenants.
  • A user has multiple permissions.

What is the best way to also have the permissions of a user scoped per tenant?
I want a user to have permission x,y,z in tenant A but have permission 1,2,3 in tenant B.

Any help on how to model this would be greatly appriciated!


Current part of the ERD

enter image description here

Answer :

You can model it like this:

  • There is a Tenant, a User and a Permission which exist independently.

  • A Tenant has many Users, and a User is in many Tenants, so there exists a many:many TenantUser relationship.

  • That TenantUser relationship has many Permissions, and a Permission can be associated with many TenantUser relationships, so there exists a TenantUserPermission.

    TenantId int PRIMARY KEY,
    Name varchar(100) not null

    UserId int PRIMARY KEY,
    Name varchar(100) not null

CREATE TABLE [Permission] (
    PermissionId int PRIMARY KEY,
    Name varchar(100) not null

    TenantId int REFERENCES Tenant (TenantId),
    UserId int REFERENCES [User] (UserId),
    PRIMARY KEY (TenantId, UserId)

CREATE TABLE TenantUserPermission (
    TenantId int,
    UserId int,
    PermissionId int REFERENCES Permission (PermissionId),
    PRIMARY KEY (TenantId, UserId, PermissionId),
    FOREIGN KEY (TenantId, UserId) REFERENCES TenantUser (TenantId, UserId)


Note how TenantUserPermission does not reference the original Tenant and User tables, it references the TenantUser table, so there must exist a relationship for there to be permissions on it. You cannot assign permissions to a user before establishing that a user is part of a tenant.

Obviously you don’t have to use surrogate keys, and you would normally name your constraints explicitly, but this gives a general overview.

Leave a Reply

Your email address will not be published. Required fields are marked *