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



  • 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!

    EDIT:

    Current part of the ERD

    enter image description here



  • 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.

    CREATE TABLE Tenant (
        TenantId int PRIMARY KEY,
        Name varchar(100) not null
    );
    

    CREATE TABLE [User] (
    UserId int PRIMARY KEY,
    Name varchar(100) not null
    );

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

    CREATE TABLE TenantUser (
    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)
    );

    https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=fffba369e1552d4beb3080f31b5925b6

    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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2