Composite-keys in multi-tenancy application for administrative actions

Question

I've designed a single-instance database for a multi-tenant application which uses composite-keys to enforce tenant-segregation at the database-layer (thus preventing incorrect 3rd-degree relations).

So the schema looks like this:

TABLE Tenants (
    TenantId int           NOT NULL IDENTITY(1,1)
    Name     nvarchar(100) NOT NULL
    IsAdmin  bit           NOT NULL

    PRIMARY KEY( TenantId )
)

TABLE Users (
    TenantId int NOT NULL
    UserId   int NOT NULL IDENTITY(1,1)
    UserName nvarchar(100) NOT NULL

    PRIMARY KEY( TenantId, UserId )

    CONSTRAINT FK_Tenants_Users FOREIGN KEY ( TenantId ) REFERENCES Tenants ( TenantId )
)

TABLE Documents (
    TenantId   int NOT NULL
    DocumentId int NOT NULL IDENTITY(1,1)

    CreatedByUserId  int NOT NULL
    ModifiedByUserId int NOT NULL

    PRIMARY KEY ( TenantId, DocumentId )

    CONSTRAINT FK_Tenants_Documents   FOREIGN KEY ( TenantId )             REFERENCES Tenants ( TenantId )
    CONSTRAINT FK_Documents_Creators  FOREIGN KEY ( TenantId, CreatedBy )  REFERENCES Users   ( TenantId, CreatedBy )
    CONSTRAINT FK_Documents_Modifiers FOREIGN KEY ( TenantId, ModifiedBy ) REFERENCES Users   ( TenantId, ModifiedBy )
)

There are many other tables in the system, but they all share the same concept where if an entity "belongs" to a tenant, then that entity's primary key is composite and includes the TenantId.

...which helps prevent situations where a Document's ModifiedByUserId could refer to a UserId in another Tenant. As Tenants are meant to be completely segregated this enforcement is ideal.

Except... how should Administrative actions happen? Note that in this system a Tenant can be marked as IsAdmin which is intended to give them the ability to not only access every Tenant's resources, but also edit and create resources - which is a problem because an Admin's TenantId would be different - so an Admin user cannot create a resource in another tenancy marked as coming from that user.

...at least for the scenario where an Admin modifies an existing resource, the ModifiedByUserId value could remain unchanged.

So to enable this scenario (creating new resources in another tenant) I have a few options:

• Give every Tenant a "Ghost user" entry which represents administrative actions. The downside is that it muddies the Users entity-set by having an entity that doesn't actually represent a human user (so what to set for things like name, email, etc?). A slight advantage to this approach compared to option 2 (below) is that it maintains complete tenant segregation, even with administrative tenants, so that a tenancy can be sharded-off to another DB instance without any identity conflicts or broken references.
• Remove TenantId from User's composite primary keys - the downside is that it weakens tenant segregation.
• Steal the UserId of an existing user in the tenant, perhaps the user-account associated with the tenancy owner - but this would result in a bad audit trail as it wouldn't be "them" who made the change.

Are there any other options for dealing with this situation?

I'm leaning towards option 1 (Ghost users) but it doesn't feel right - and also adds complexity as special-case logic would have to set the foreign-key value not to the current user, but to the ghost-user, if the current user belongs to an admin tenant:

resource.CreatedByUserId = currentUser.TenantId == resource.TenantId ? currentUser.UserId ? getGhostUserId( resource.TenantId )

Answer 1

In our multi-tenant application, we separated the users out from the other tenant-based stuff, and instead added a separate permission management which is tenant-based. This then allows you to grant access to multiple tenants to one user (or user group), similar to here where you have one account which works on all SE sites.

With that setup, you would not have an "administrative" tenant but rather an administrative role for each tenant and you get to choose who is member of this role. By adding groups support and an administrators group, which is implicitly member of the administrative role of every of your tenants, you can also implement global admins.

The other thing we did very differently is that we did not pick composite keys, but GUIDs as primary keys. There is always a debate on pros and cons, but to us it was important that there is no collision (think of merging/syncing/transferring tenant data) and that the key does not get cumbersome. This, together with the permissions I mentioned, allows even implementing data which is shared across tenants and the like if you want (or need) that.

For tenant segregation we actually have a separate database for each tenant, so that we can even back up, restore or transfer just one tenant at a time.

Answer 2

Assuming you want to keep the composite keys and keep the tenants in the same database, there are other options within the scope your question seems to imply, such as...

1. Eliminate the admin tenant concept, and give each admin user a real user identity in the Users Table.

This means your admin users would have multiple user rows in the Users table. Their identity as admin users would likely be in a separate table, say AdminUser, like...

create table AdminUser(AdminUserID int, etc...)

Users table could then have a nullable foreign key referring to AdminUser, if your database and policies allow that.

2. Or Create another table like...

create table TenantUser (TenantID int not null, UserID int not null);

Make your foreign keys refer to the TenantUser table, where only valid users should be listed.

So then each User belongs to a Tenant via the Users->Tenants relationship. The Admin Tenant still exists and owns the Admin users.

But each user can be granted rights to multiple tenants, via TenantUser.

With either option you can record the real user ID for audit trail purposes etc, and keep code fairly standardized with no Ghost Users.

You asked for alternatives, and I interpret this as meaning alternatives that are not too radically different. I'm not trying to recommend these alternatives.

There are, of course, also some radically different alternatives, like giving each customer a separate database.

The right answer really depends on your business.