We are designing a new multi tenant .NET Core 2.1 application progra, where we are using one database per tenant.

We can give each tenant a different subdomain so that we can base the tenant lookup on the subdomain, but that wouldn't let one user be able to exist in several tenants.

How would you solve it so that a user can belong to several different tenants?

I've thought about creating three tables, namely, Users, Tenants and UsersTenants in a separate database, so that we can lookup all the tenants one can access from there, and then have a default one it chooses automatically. And then in turn let the user pick from a dropdown list in the application to change the tenant (and always send a x-tenantId in a header with every request).

A very big problem with this is that the userlist recide outside of the tenants database. So when for instance a user wants to save CreatedBy or ModifiedBy and any other action based on users, it would have to store an ID located in a different database, that's not good at all (securitywise a nightmare also).

I've thought about solutions to this but haven't came up with any good ones, maybe a sync so that the users that are created in the main catalog is also added to the specific user databases, but what happens if a customer admin wants to create a new user, we would have to create it in two different places.

Any thoughts on how this can be solved?

  • The application should know what tenant the user is currently connected to, so wouldn't any CreatedBy/ModifiedBy data be written to the current tenant database? – Aaron Bertrand Oct 18 '18 at 21:59
  • Hi, yes it would, so you're saying we shouldn't have a FK-relationship for the createdby/modifiedby to the users table in the Catalog db then? Doesn't feel right :) – timeshift Oct 19 '18 at 11:58
  • Doesn't feel right <> "very big problem" - you can enforce this at write time using triggers, but what happens in two separate databases can be independent, making a constraint impossible. What happens if your catalog db becomes suspect and you need to restore an older version, prior to the time some user was created? An active foreign key would make that impossible. – Aaron Bertrand Oct 19 '18 at 12:13
  • So in essence this architecture i'm proposing sounds good? You would rather have the catalog users table than have a duplicate synched users table in each tenant db? I'm also thinking of making TenantId a column i all tables in case we want to move tenants to same database in the future. But then a new problem pops up with the keys? should eg (TenantId,OrderId) be the PK? if not it would be a collision when trying to merge two databases. – timeshift Oct 19 '18 at 12:30
  • Maintaining a copy of the users in every tenant DB does not sound practical or useful to me, especially if the entire purpose is just so you can have that foreign key. It is still going to be vulnerable to all of the things that can make it out of sync from the central database anyway. I don't know why you would ever need to move multiple tenants into the same DB in the future. If that is part of the plan then just do it right now - maybe make 5 tenant databases and stick 10 in each. Personally I prefer the model where each database is and always will be a single tenant. – Aaron Bertrand Oct 19 '18 at 12:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.