When building Software as a Service (SaaS) solutions one of the things that is very important to consider is the Multi-Tenancy of the Database, and the different approaches that available in order to achieve the best option.
The option is always the same in every part of any solution, and this is from a Fixed Solution to a Fully Configurable Solution, and a SaaS solutions normally starts somewhere in the middle, that is when different customers can have different functionalities and even different UX. But a very important thing to acknowledge is the fact that in order to maintain this kind of differences we need a data model that needs to be Multi-Tenant, and so the next choice to make is the Multi-Tenancy approach that will be used, and it can be:
- Separate Databases for each Customer
- Isolation, The most Isolation we can have, and the one we can be sure that no customer can access other customer’s data
- Customization, Each customer can have different data model without affecting other customers
- Performance, The fact that the data is isolated allows that the several customers have performances accessing that are dependent only on their amount of data, and not other customers data.
- Manageability, Whenever a customer has a problem with the data on the solution, or needs a migration, or backups are easier to achieve since they are isolated.
- Same Database, Separate Schemas for each Customer
- Isolation, This is in the middle way since we have from one side the data isolated since we have different schemas for each customer, but all resides on the same database, simplifying the number of databases that reside on our server.
- Customization, Each customer can have different data models without affecting others
- Performance, The fact that the data resides on the same database as other customers, the amount of data from other customers can affect the performance of accessing the data, but of course this can be simplified by using Partitioning on the database.
- Manageability, Whenever a customer has a problem with the data on the solution or even a migration it’s still not so hard since we have different schemas for each customer, but for separate backups this is not that easy.
- Same Database, Same Schema for each Customer
- Isolation, This is the less isolated solution for data since everything reside exactly on the same tables, and database, and then this will simplify the number of Databases and Database objects on the server.
- Customization, This makes that every customer have the same schema, and so customization is only possible using approaches like:
- Having several Custom columns on the tables, like, Custom1, Custom2, and so on.
- Having a table for customizable columns, like ColumnName, ColumnType, ColumnValue.
- Performance, The fact that the data is nor isolated makes that the amount of data from other customers affect the performance of accessing data, but of course this can also be achieved using Partitioning on the database based on the for instance in the TenantID.
- Manageability, This becomes harder since everyone's data is on the same tables and so the separation of the backups, as well as the manageability of the customers data becomes harder, but the possibility to run checks on customers data became easier since we always know at front the schema used for each customer.
A very interesting document regarding this subject is this one that is part of the MSDN site, and that is called “Multi-Tenant Data Architectures”. Another interesting reading about this subject is this article from Scott Mitchell in the 4GuysFromRolla community site about “Querying a Multi-Tenant Data Architecture”.
In order to know more about SaaS architecture and solutions I’ll suggest the following articles:
Hopes this can help you get started or even with your choices in terms of Multi-tenancy of Data.