Multiple companies in same database

Question

I'm working on a system which for every "company" has their own "users" and their own "bills". That scenario is better in performance and management? Handle all companies in the same database and link everything to an idempresa, or database for each client?

Answer 1

This is called multi tenancy architecture and each customer is a tenant. There are various strategies to deal with it and each one might bring potential problems.

Having a separate database for each tenant is an option that provides data separation and do not require you to add a column to identify each tenant in your tables and queries, but also has the downside to keep multiple databases up to date.

Having a column in each table of a single database to identify your tenants is also a good strategy, but then it brings problems when scaling and managing different features for different customer for example.

You need to study all available strategies and decides which one is best based on your requirements and pain points.

Answer 2

Putting a tenant data in a separate Database is a straight forward approach and less painful option but then in a long run, when your product gets wildly successful, maintaining this database will become a nightmare.

On the Other hand keeping all the Tenants data in a single database could also make your application non scalable and less performable. The better approach would be the combination of both, the decision of making the choice between these two is completely based on the type, usage and size of the customer.

In certain cases, you may need to provision a separate database for a particular module or feature of your application may be for security or to isolate the specific data alone. I have written an article on these lines; kindly have a look at http://blog.techcello.com/2012/07/database-sharding-scaling-data-in-a-multi-tenant-environment/