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
...which helps prevent situations where a
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
Usersentity-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.
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 )