CODEWITHSUNDEEP
c#entity-frameworkmulti-tenant
RHarris
RHarris

Reputation: 11187

How to handle shared lookup tables across tenant databases using EF Core?

We're developing a multi-tenant application. The design is such that each tenant will have their own database (on the same server) and each database will have the same schema.

The system has certain lookup tables that define values that will be constant across all systems. For example, States, Counties, Genders, etc. The design states that these will be in a "Control" database. The "Control" database is managed by our organization -- so tenants can read data from the lookup tables but cannot write data to the tables.

The idea here is that we want to avoid duplicating these lookup tables and data in every database. If we have to change a value or add a new value we can do it in one table as opposed to having to update it in each tenant database.

The problem is that with EF Core, we have two DbContexts -- one that ties to the tenant's data and one that ties to the Control database. There doesn't seem to be a way to define cross-database relationships.

example

//Table exists in each Tenant Database
public class Client{
    public int Id {get; set;}
    public string Firstname {get; set;}
    public string Lastname {get; set;}

    //Table of Counties defined in "Control" database
    public int CountyOfResidenceId { get; set;}
    public County CountyOfResidence {get; set;}

}

The only way I can think of to handle this design is to have to (in my API Controller) validate id's coming from the client (via lookup to the Control database) and manually populating the CountyOfResidence (as well as all other "lookup" properties) for every client read from the database.

How is this generally handled by those creating Multi-Tenant applications where each tenant is required to have their own database?

Upvotes: 2

Views: 482

Answers (1)

Steve Py
Steve Py

Reputation: 34773

When working with DB-per-Tenant, I leverage replication to control lookup tables across tenant DBs. The issue with trying to centralize "common" tables in a separate database is that you lose the ability to efficiently enforce FK relationships. With SQL Server you can use check constraints across databases in place of FKs, but there is a performance cost to this, which IMO is not worth the relatively low cost of putting these tables in each tenant DB. Given that these are supposed to be truly common tables, they shouldn't be expected to change very frequently, if at all. One-way replication is pretty dead-simple to set up or even manage manually as a schedule-able job.

The way I structure my multi-tenant applications is that the master DBContext/DB handles only authentication, versioning, maintenance tracking, and handing off the Tenant's DB connection string once authenticated.

Upvotes: 2

Related Questions