I created a web app tailored for my business, which is a ticket and call-intake system to help log customer calls and manage callbacks. After seeing its effectiveness, other businesses have expressed interest in using it. I'm now looking to adapt the app to support multiple businesses, but I'm uncertain about how to structure the database. I'm working with SQL and have been presented with a few options: 1) One shared database with a store_id, 2) Separate schema for each store, or 3) Separate database for each store. I understand the basics of these options, but I'm seeking advice on what would be best in the long run for security, scalability, and ease of maintenance. For anyone who has built multi-tenant applications, which approach would you recommend, and are there any common pitfalls I should avoid?
5 Answers
Honestly, it's better to reassess from a different angle: 1) How many customers are you expecting? 2) What’s the volume of data? 3) How much variation exists in the data structure? Keep it simple until there's a real need to complicate things. It's easier that way.
Going for a dedicated database for each tenant is generally the best practice, especially if you're not dealing with too many small tenants. It greatly enhances security and performance, not to mention that it avoids possible data pollution. If necessary, you can still configure a multi-tenant schema for smaller clients later on.
The trend nowadays is toward containerization and microservices. Having separate databases and front ends for each client ensures that nothing is shared beyond the reverse proxy, making scaling straightforward and enhancing security.
Definitely opt for separate databases for each business. It’s all about not risking data leaks or security issues by putting everything into one database. Plus, legal considerations often come into play, reinforcing this choice. The added security and performance gains are more than worth it.
There's definitely a trade-off between security and complexity here. Using shared tables with a tenant ID keeps things simple, but you risk data leaks if you forget to filter by that ID. On the other hand, separate databases offer strict isolation, but managing multiple instances can get messy. A middle ground is using one database with separate schemas—simpler than multiple DBs but still more secure than just a tenant ID. Consider: 1) Are there legal requirements to separate this data? 2) How catastrophic would data leaks be? 3) How complex are your views? 4) Do you have tools to enforce tenant ID checks? Think carefully about these factors as you decide the best approach for your project.
You can also look into Row-Level Security. It can work well to mitigate potential leaks without overcomplicating your database.