Covering multi tenancy in the database

You there, computer man! Make it a multi tenant application.

There are several ways to support multi tenancy on the database part of your application. The easiest approach would be to add a CustomerID column to each table. This is easy to maintain since any change you make in your development environment (add a column, change a type,…) needs to be done only once on the live environment. The downside however is that all data of all your customers is mixed in one table forcing you to add a where clause to all your queries to filter on the customer id, if you support different business logic per customer you might also run into issues. Customer A wants an additional field etc.

Another approach is to prefix or suffix your table names with some sort of customer identification. So you’d have CustomerA_BlogPosts, CustomerB_BlogPosts. You can have a single database for all your customers but now all data is stored in a different table. Since now you have multiple tables representing the same model you’ll have to figure out a way to migrate to new versions. It’s doable but keep it in mind, you’d also have to create some infrastructure to select the correct table when you issues your queries.

A variant on the customer specific table names is to create database schemas which are specific to your customer. So you’ll have CustomerA.BlogPosts and CustomerB.BlogPosts. Apart from the fact that all data is stored in specific tables per customer, like the approach stated above, you can also add an additional security layer by using a specific database user per customer which can only access the tables of the specific schema. All table names remain the same so you could even migrate specific customers to another database server as long as your infrastructure to look up the correct database / schema name.

The most flexible way however, but with quite an impact on your server, is to create an entire database instance per customer, this gives you the most available options. You can use all available database tooling to revert specific databases if the customer asks you to etc.

I’ve used all approaches stated in this post and even mixed and matched between them, it all depends on the scale and requirements of your project. For small projects I’d use the first approach with the additional column. For larger projects or where you need to support different data models per customer the schema specific approach or the entire database per customer are the way to go in my opinion.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.