1

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 on
  • Each customer can have different users for their own data, with different permissions
  • A 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)

Daniel Castro
  • 121
  • 5
  • @DocBrown Wow, that happens when you don't know the term you're looking for. Anyway, though the question you linked provides me really useful information, I believe this question is a bit more specific. However, I believe this would be a duplicate of this one: http://programmers.stackexchange.com/questions/141261/multi-tenancy-single-database-vs-multiple-database – Daniel Castro Apr 20 '14 at 20:29

1 Answers1

2

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.

mgw854
  • 1,818
  • 10
  • 11
  • Thank you for your answer. I was thinking to have different users for each customer (so if customer A wants to allow customer B to access their data, they would create a user in their database, through my app, of course), so picking the right DB is not hard at all. About the single DB approach, I'm not really worried about the field, but the possible bugs that could let users see data they shouldn't. Considering I'm targetting few users, do you think that single DB is the right approach? – Daniel Castro Apr 20 '14 at 20:36
  • If you really aren't targeting many users at all, I would go the simple route and use one database. The bugs, as I mentioned, should be incredibly rare. Just make sure to filter on customer anywhere you pull customer data. It should become (as it did for me) second nature. – mgw854 Apr 20 '14 at 20:41