Multi Tenant Database comparison

Separate Databases Shared Database, Separate Schema Same Database, Same Schema
1. Definition

Separate Databases - Each tenant has its own set of data that remains logically isolated from data that belongs to all other tenants.
Shared Database, Separate Schema - Each tenant having its own set of tables that are grouped into a schema created specifically for the tenant.This approach accommodates more tenants per server than separate databases approach.
Same Database, Same Schema - Using the same database and the same set of tables to host multiple tenants' data & allows to serve the largest number of tenants per database server.


2.Security
Separate Databases - Database security prevents any tenant from accidentally or maliciously accessing other tenants' data. Easy to implement. Shared Database, Separate Schema - Moderate degree of logical data isolation for security-conscious tenants, but less than separate database approach.
Same Database, Same Schema - This approach may incur additional development effort in the area of security, to ensure that tenants can never access other tenants' data & require strong data safety.


3.Extension
Separate Databases - Easy to extend Data model as per tenant need
Shared Database, Separate Schema -Also Easy to extend Data model as per tenant need
Same Database, Same Schema - Customizations happen through additional columns/extra tables. So is harder to understand and maintain.

4.Data recovery
Separate Databases -Easy to implement.
Shared Database, Separate Schema - Harder to restore the data.
Same Database, Same Schema - Very hard to restore the data.


5.Costs
Separate Databases-Very costly.
Shared Database, Separate Schema-Lesser than separate databases.
Same Database, Same Schema- Lowest hardware and backup costs. Applications optimized for a shared approach tend to require a larger development effort but lower operational costs.


6.When to approach
Separate Databases-Appropriate for customers that are willing to pay extra for added security and customizability.If some or all tenants to store very large amounts of data.If there are going to be large number of users per average tenant to be supported.
When per-tenant value added services (e.g. per-tenant backup, restore etc.) are added.

Shared Database, Separate Schema -Appropriate for applications that use a relatively small number of database tables & when customers accepts having their data co-located with that of other tenants.

Shared Database, Separate Schema -This is appropriate when it is important that the application be capable of serving a large number of tenants with a small number of servers, and prospective customers are willing to surrender data isolation in exchange for the lower costs.
If there are a very large number of rows in the affected tables, this can cause performance to suffer noticeably for all the tenants that the database serves. But this can be overcome by scaling out a shared database is through horizontal (row-based) partitioning based on tenant ID.

--Amit G Piplani--