Multi tenant databases with single shared database

Question

we are using .net mvc and sqlserver db.

EDIT

We are also using NHibernate for data access. I mention this because we will not be writing our own sql or do stored procs. triggers in the db might work but I don't know if you can do that between databases.

END EDIT

we want to have a multi tenant set up so each client has there own instance of the db. However, we need to have each tenant connect to an other database which has a great deal of user information. there will be some small amount of shared data between them. Basically the tenants will be referencing the data of the users in the shared database.

The idea is that some people will use just the shared database ( independent clients ) they then may well be hired by one of the tenant clients. the tenant will then want access to the new employees data in the shared database. Further the employee may leave one tenant and join another or leave one and remain independent and want access to thier data. We could of course have the shared database schema in each tenant and just do a big export import each time some one left or joined but this seems like a lot of trouble too.

I am asking for any advice on how to manage the fact that the tenants will have references to the shared database but no referential integrity. Or if there is an alternate approach or whatever. Thank you, Raif

Answer 1

Across databases you have to give up declarative referential integrity (foreign keys). However you can still enforce this (if you think you need to) using after or instead of triggers, or if you control all data manipulation via stored procedures, you can do it there (on insert or update, for example, you can check first, or as part of the modification join to or use EXISTS against the table(s) in other databases to be sure that a valid value is being used).

I've worked with multi-tenant models and there can be huge benefits that are worth the costs (e.g. giving up DRI in some cases). For things that are mostly reference data and that aren't free-text entry, there shouldn't be a whole lot of extra effort required.