First a clarification :
- In a multi-tenant architecture, one instance of an application is shared between several tenants but the data is compartmented so that each tenant only see its own. This can be achieved with different patterns:
- with a database per tenant
- with a multi-tenant database
- with a shared database, the application taking care of the multitenancy by implementing a partitioning scheme
- any hybridization if the above
- In a single tenant application, each application instance is independent serves a single tenant and uses its own single-tenant database.
Your scenario 1 is somewhat in contradiction with multitenancy because you break the boundaries that the architecture is supposed to ensure:
* The safest way to handle this is by organizing the connection at application level, as if it were a remote connection with a distant system. It’s like any system interface; you could let the tenants be in control of the interfaces they accept.
* In the case of the shared database you could simplify this kind of connections, because the application could simply read the shared data, implementing the access control rules at application level. It’s in the same time the strength and the weakness of this model.
In your scenario 2: except for the the shared database scenario, you’d have the problem that you would have a lot of (slower) cross tenant queries. If you have to join data of different tenants for example, you’d have to read much more records than if you could let the database do the work.
A proven but expensive approach, if you have a lot of analytics to be done that way, is to create a data warehouse. You’d then load in a shared database the shared data of the relevant tenants, and make your cross-tenant queries there. A side effect is that the performance overhead generated by the analytical activity would not impact the transactional activity of the tenants.
So whether you use multi-tenant with a database per tenant or multi-instance with single tenant, should not impact significantly your scenarios.
