Everyone is always talking about developing a SAAS app. The basis for SAAS is a Multi-tenancy architecture that allows multiple tenants (organizations) access to multiple instances of the software hosted on a single server. The tenants of the software share the server resources and memory.
Designing a database structure that can accommodate this type of design depends depends on several factors among them:
● Client data isolation
● Database scalability
● Database management (backup and restoration)
● Operational complexities such as schema and tenant management
Depending on the user and system requirements of the application, the database design can be identified as:
- Multi-tenant database
- Single multi-tenant database
- Sharded multi-tenant database
As the name implies, a tenant (organization) has its own database. Each time a new tenant is added to the system, a new database is generated for the user. Every time a new tenant is added, a new schema is generated that creates a separate database for the tenant.
This structure facilitates customization on tenant level and proper data isolation. The query speed of this design is relatively okay as the search path to the tenant database is set before queries are run. Easy data backup, restoration and migration are among the benefits of this design.
While this architecture provides data isolation and speed, it does not scale so well. When the number of tenants/clients on the app is small, this design is effective but when tenants are larger, resources compromisation is bound to occur. The number of tables increases, the number of queries increase, so is the size of these tables. Thus, there is a need for continued scaling of resources as more tenants are added.
This design facilitates many tenants to access a multi-tenant database ( of any number).
Because data isolation is not meet by storing several tenants in a multi-tenant database, The database schema contains a tenant identifier column that is used to identifier each tenant in the database. This is the technique used in separating tenant-related data and data retrieval.
System resources are better managed with this design because the multi-tenant database shares compute resources and storage resources across all its tenants. A drawback of sharing resources among different tenants is that there is no way to monitor the usage of these resources and workload of each tenant and this can lead to crippling the server.
Single Multi-tenant database
This design facilitates the usage of a single database for all tenants. As more tenants are added, compute and storage resources are increased.
The major drawback of this design is that the database becomes complex to manage quickly. Management operations for each tenant become extremely challenging to perform. Also, because all tenants access the database and running queries in the same database, the speed of query operation might be affected.
Sharded multi-tenant database
This design facilitates tenant data to be distributed across multiple databases (shards), with all the data for a particular tenant is all contained in a single shard. A sharding key/tenant identifier is managed and imposed by the database schema.
This design provides high scalability and also by distributing tenants across several small databases, the databases can be easily managed. It is also cost-effective
Designing of the shard architecture can be complex due to the need to maintain a mapping between tenants and databases. Also, the application has to maintain a catalogue of the shards and respective tenants.
In addition to the database multi-tenancy design, the structure of the database is also considered in multi-tenancy applications.
Catalogue-based multitenancy — A tenant has its own database catalogue with the tenant identifier to facilitate data isolation. Operation and maintenance could be expensive.
Schema-based multitenancy — A tenant has its own database schema with the tenant identifier to facilitate data isolation. This can be computation expensive if the database resides on a single physical server and Neighbor noise can be a challenge.
Table-based multitenancy — Multiple tenants are clustered on the same database with tenant identifier specified on an identifier column which is added to all tables to facilitate data isolation. The database can be managed like any other single tenancy database but the query traffic to the database is intensive and management operations are difficult.