I'm starting to design a web application, and I'm evaluating some decisions regarding the general structure of it. My use case is as follows:I plan to have multiple customers on a single server. Initially there won't be many customers, probably two to start (but the system should be ready for more, of course). As I'm working as a single developer, I want to save cost in servers, SSL certificates and so onEach customer can have different users for their own data, with different permissionsA customer should not be able to access data from other customer, unless the other explicitly allows him (i.e. creating a user for him)I was evaluating how to handle this multiple customer scenario, and right now I'm planning to separate each customer in its own DB (and deciding which DB to connect with the customer URL, for example). Managing everything in the same DB would be, I think, error-prone (an error in the app would let a customer view data it shouldn't) and complicate my design (I would have to store the customer with all data).So I would like to know what advantages and disadvantages come with this kind of design. Is there a performance issue? Maintenance problems? Security problems?If that matters, my platform of choice is Python + Flask + SQLAlchemy (though I'm evaluating Django too)
Advantages and disadvantages of making one DB per customer
design;database;web applications
There are potential problems with storing customers together in the same database--you hit on two of them. The third that comes to mind deals with data statistics. If one customer uses the service lightly, and another heavily, it can throw off the statistics for everyone, making all the queries perform badly. I've seen that happen before (but only when the disparity is massive; something like five orders of magnitude difference).One the other hand, separating everyone into different databases is certainly an exercise in code duplication. Everything, including stuff that isn't necessarily customer-specific, will have to reside in each database. Trying to pick the correct database could be very tricky, especially where users have access to multiple customers.My experience is that adding a customer field to every table is no big deal, and the wrong data is almost never returned if it becomes a standard to always filter on the customer. I would stick with same DB--you'll likely have less headaches than if you're dealing with multiple out-of-sync databases.