Discover the core of multi-client database structuring with the expertise of our experienced developer, Matt Bell.
When working on a solution that will be used by more than one client, it is important to think about how you should structure the system’s database to separate each client’s private data and prevent any breaches of confidential information.
A common approach to handle this is to use a method called multi-tenancy, whereby clients will have their data stored in a shared environment; they will all be logically separated, but physically integrated. This is contrary to a single-tenancy approach whereby a distinct instance of the application runs on infrastructure not shared by other clients (or tenants).
There are several methods of implementing multi-tenancy in a system, each providing different levels of security and flexibility in how the application is managed and maintained.
Single Database per Tenant
This is perhaps the simplest approach, as it just involves creating a separate database for each new client that uses the system.
This method allows for the strongest separation of client data, as the application will have to provide separate connection strings and credentials to access the data requested by a given client before it ever touches the data itself, all but removing the risk of a data breach entirely.
It differs from a single-tenancy approach in that servers will host multiple instances of the database, one for each tenant, instead of a whole server allocated to just one client, netting them the entire machine’s resources exclusively.
- Highest level of tenant isolation
- Easy to restore/manipulate client data
- No complexity to connections, just select the right database
- Additional servers/storage required, more expensive to patch and maintain
- More database objects require maintenance as the number of clients increases
- More work involved in adding a new tenant: new database allocation, new schema etc.
Shared Database, Shared Schema
Another approach is to use just one database with one schema and use tenant identifiers alongside technologies such as row-level security to keep data separate.
This method requires a bit of additional setup, in that security policies must be created to ensure clients are only able to access their data and not that of others in the system, but once they’re in place they can usually be left alone.
In terms of filtering the data on each request to the database, this can be done via a ‘Where’ clause on data retrieval that specifies which Tenant Id the query should limit its search to; however, this can easily be forgotten by developers and open the system up to the risk of a data breach. A better approach is to use row-level security which utilises policies (called Filter and Block Predicates) to physically restrict access to rows of data that don’t meet given criteria e.g., When logged in as Tenant 1, only access rows with a TenantId of 1 etc.
- Initial creation is simple; only one schema to create on one database
- Simpler management of single database and schema
- Less maintenance thanks to one/fewer servers
- Tenant data is stored together; risk of data breach
- Server resources are shared between multiple tenants
- Cannot easily restore/manipulate a single client’s data
We recently worked on a project for a client that involved transitioning their system from its original implementation of being mostly locally-installed services calling to a Single Database Per Tenant, to instead use cloud-hosted microservices that would allow the bulk of the system’s functionality to be performed away from the client machine. This approach however called for a database per microservice, so, we utilised the Shared Database, Shared Schema version of multi-tenancy to reduce the number of databases we otherwise would have had to create and manage (ClientA.Microservice1, ClientB.Microservice1, ClientA.Microservice2 etc) along with elastic load balancing to improve performance by allocating more resources to clients making more requests dynamically.
Shared Database, Separate Schema
This approach takes the same Shared Database structure of the previous method but increases the tenant isolation by creating separate schemas for each tenant.
Client data is split into separate tables, removing the need for row-level security, while maintaining the benefits of only having to manage one database and server (depending on how many clients the system supports).
- Increased tenant isolation over a shared schema approach
- Optimisations can be made at an individual tenant level
- Custom data can be held for some tenants and not others
- Still hard to restore a single client’s data
- More database objects are created as the number of clients increases
- Any schema updates need to be rolled out to each client individually
The use of each of these approaches can be determined based on the type of application they will support, the amount of data that will need to be stored for each client, and the number of clients themselves. If you can allocate a substantial amount of resources to the hosting of your database, you may wish to go for a Single Database per Tenant approach to prevent any issues with performance. Otherwise, a Single Database, Shared Schema approach can be less costly and useful for an application serving just a few clients.
Contact us today to strengthen data isolation, streamline management, and enhance overall security.