CODEWITHSUNDEEP
mysqlperformancedatabase-design
Mehran
Mehran

Reputation: 16831

Database design's performance: One database for n customers vs a database per customer

Consider a web application project with MySQL database which supposed to serve multiple companies. These companies are completely unrelated and thus should not have access to each other's data. There are two designs I can think of to implement such an application:

1. Using a company_id for tables to indicate the owner of each record. This way one database will be shared among multiple companies.

2. Dedicating a database to each company. This way the company_id field will be omitted and data isolation provides better security assurance. Yet multiple companies will share a MySQL instance (it's not possible to dedicate a MySQL instance to each company due to profit aspects of the project).

If we assume that both approaches are feasible (security-wise) which one is more performant?

I believe it all comes down to overhead of having a database in MySQL but I'm not sure how much is that!

Upvotes: 0

Views: 55

Answers (1)

Peter Bowers
Peter Bowers

Reputation: 3093

From a performance perspective the differences are absolutely negligible.

Having company_id in every table adds a bit of a programming load to every form, etc., but is THE standard way to do it. This would be considered "best practice".

Having a separate database for each company allows an administrator to be appointed from the company and they can do, for instance, backups or truncates without fear of seeing or deleting someone else's data. This, IMO, would be the only reason to go with multiple databases.

Upvotes: 1

Related Questions