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,
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
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?